Oracle Performance predicatable ?
Karl Reitschuster
in Tuning
|
Post a Comment
| 
Email
Hi reader, after Migration of an Oracle Database from AIX 5.3 to Sun Solaris 5.10 and from Oracle 10.2.0.2 to 10.2.0.3 I made some tests with Parallel query performance on the new platform. Following query was executed with parallel degree 4 and then with parallel degree 8; The query itself was generated with the DBMS_STATS package to gather statistics for an index of a partitioned table.
PGA_AGGREGATE_TARGET and _PGA_MAX_SIZE
Karl Reitschuster
in Configuration, Oddities
|
Post a Comment
|
2 References
| Email
Hi reader, due to strange behavior (Oracle 10.2.0.3, Sun Solaris 5.10, 64bit) of the SORT operation with manual PGA, independent of SORT_AREA_SIZE set occurrence of Waitevent DIRECT TEMP READ/WRITE with a lot of elapsed time did not disappear, we had to set up a batch friendly configuration with automatic PGA;
PL/SQL and Generics Support?
Karl Reitschuster
in PL/SQL
|
Post a Comment
|
3 References
| Email
Dear reader, looping thru data i wanted to declare a simple counter variable in a PL/SQL declaration section. Usually i use a PLS_INTEGER type. This time I wanted to make it exact. Counting rows would not need negative values. I would only need a range from 0 .. [a very high positive value]. After browsing the documentation I found the right type for it - NATURAL.
White Belt hit Black Belt
Karl Reitschuster
in Others
|
Post a Comment
| Email
Hi,
yesterday evening in the dojo (Karate-Do Training place) I talked with my sensei (The master Trainer - black belt 3rd degree) about the way to get certified for the next belt colour. Currently I am on beginner level and should be target on the yellow belt. Then he told me about a group of karate's which train only for certification to get to the next belt level as fast as possible. Surely they will get the belts faster then the other ones which are concentrating on the whole common stuff. But he told karate is much much more then the stuff tested in a belt certification.
And that was the link for me to the oracle certifications. Some specialists - not database ones - but query form experienced ones - pass one examine after the other - but oracle - working with oracle is much more then a 90-120 minutes certification. This is nothing against certification. But something against black belts outsides but white belts insides.
There is no shortcut for getting experience(d). Not with Karate and not with Oracle.
Karl Reitschuster
The database is gone!!
Karl Reitschuster
in Troubleshooting
|
Post a Comment
|
3 References
| Email
Dear reader, got a call from user of a web based application that the database was gone!! Now first I checked if the user's message really hit the root cause.
Successfully launch of a new data interface or The egg preceded the chicken
Karl Reitschuster
in Development, Tuning
|
Post a Comment
|
1 Reference
| Email
Dear reader, i created a data interface for new demand of incoming data exchange from another system. Finally before the new Interface was released to production i took some tests on a production clone database. Usually in development the interface processed 6m rows in about 15 minutes. But on the clone database the processing did not end after hours. After some analysis i found out that the interface related tables had statistics gathered before processing and resulted in 0 rows per interface table.
Explain Plan - What you see is sometimes not what you get
Karl Reitschuster
in Tuning
|
4 Comments
|
3 References
| Email
Updated on Wednesday, August 1, 2007 at 12:58PM by
Karl Reitschuster
Dear reader, today support called me because of user complained about a slow system. This time I took a look into the V$SESSION_LONGOPS view which helps to find sessions running SQL lasting longer then 6 secs. Very soon I found a candidate scanning the application customers table with 6.6m rows. With the SQL_ID I found the SQL in V$SQL and pasted the SQL into an explain plan tool.
Enterprise Management Reporting for SAP R/3 - in 72 hours - (Frankfurt/Dreieich 25th of June)
Karl Reitschuster
in Event
|
2 Comments
|
2 References
| Email
Updated on Thursday, July 5, 2007 at 10:24PM by
Karl Reitschuster
Dear reader, SAP comes with an own Datawarehouse solution for SAP R/3 called SAP BW. SAP BW due to it's complex source system and the amount of data and structure is very complex to handle. To startup a DWH Solution for a SAP R/3 customer development round trips for getting first reports out of the system lasts for weeks or even for months. And so the costs for customizing a SAP BW are high.
Thanks to Oliver ...
dear reader,
Oliver pointed me out to take a try with squarespace blogging - and i am absolutely thrilled!
Working with some other sophisticated guys in a project - you can learn a lot - even non oracle stuff ;-)
Oliver is a Senior Developer and an expert of it's own - you find his page on ...
Karl Reitschuster
Tough three days with Jonathan Lewis (Bonn 12th-14th of June)
Karl Reitschuster
in Event
|
Post a Comment
|
2 References
| Email
Dear reader,
i was able and indeed very happy to visit three sessions Jonathan Lewis did this week in Germany. It was a lot of very interesting stuff with a high input rate;-)
- Cost Based Optimisation, 12. Juni 2007
- Trouble-Shooting and Tuning, 13. Juni 2007
- Writing Optimal SQL, 14. Juni 2007
I do not want to give a summary report about every session but i do want to write three follow ups about one aspect per day i found very interesting and i did not know or did not see it that way before.
And i want to provide a (very) small picture gallery of the event.
Another interesting point was is to meet a lot of folks working with Oracle in a different way and to share experiences.
Karl Reitschuster
Karl Reitschuster
Cost Based Optimisation, 12. Juni 2007 - The Truth about sorting
For every one of the three days i wanted to post a highlight of understanding Jonathan Lewis gave to us about the oracle server :
When Oracle sorts data - hopefully in memory - it does not sort it at all! Data to sort is loaded as it is into the sort area. How then it will be sorted?
For every row loaded into the sorting area oracle creates a branch in a binary tree. This tree structure grows and so also demands place in the sorting area. The binary tree is linked with Pointers.
- This in mind helps you to understand that not all memory allocated for sorting is used by the data but also by it's tree memory structure and some little piece of metadata. So allocated for sorted data could be larger as assumed.
- On 64 bit Machines the pointer for the binary tree are also 64 bit sized - therefore you need even more memory to sort the same amount of data as on 32 bit machines.
The binary tree was replaced in Oracle 10.2 with a more CPU saving hash structure.
Karl Reitschuster
Karl Reitschuster
Trouble-Shooting and Tuning, 13. Juni 2007 - The Truth about Tuning
if you are called for Tuning very often you are called for running production systems having tremendous problems keeping service times. Is this not the broad understanding of Tuning?
Jonathan Lewis pointed out that this kind of emergency job is Trouble-Shooting and not Tuning. Tuning happens in the development process. If you are called for Trouble-Shooting on a live system you missed something very important in the development/design phase.
Karl Reitschuster
Karl Reitschuster
Jonathan Lewis repeated to proclaim : Hints are never ignored by oracle if they are written correctly and if the object exists if one is specified. Hints are rarely documented. It's not clear what strategy or pattern is behind a hint. One example:
SELECT /*+ PARALLEL(CUSTOMER, 8)*/ * FROM CUSTOMER;
Karl Reitschuster
