Oracle Performance Management with Gaja Vaidyanatha
Posted by John Hallas on October 21, 2009
I attended this 2 day workshop ,held in London, last week.
I already owned both of Gaja’s book, Tales of the Oak Table and 101 Performance Tuning so I was reasonably familiar with his background.
The course started of badly as Gaja was late arriving due to travel delays and so we did not kick off until 11:30 which was either 1.5 or 2 hours depending on whom you asked. He hit the ground running with a quick introduction and then a round the room ‘what do you want to get from this’ session.
The sessions covered everything set out in the advertising blurb, namely architecture, tracing, use of the OWI interface and a planned methodology for dealing with problems. One key point , apart from the availability of RAT in 11g was that the newer release did not add much to what Oracle 10g already provided with regard to performance tuning.
The key methodology thoughts were to clearly define a goal, run a simple diagnostic routine and then repeat. With regard to SQL optimisation the 2 things to remeber were
- Reducing logical I/O is primary in any performance tuning engagement
- Hand in hand reducing elapsed time is equally important
The repeatable process is to find ‘interesting’ PIDS from glance or top, map them to a SPID (SID and SERIAL#) to find a session then run a 10046 trace and find the most expensive step (often the most indented call).
Fix the code (one change at a time) and repeat. If a 10046 trace does not help then get down to the OS level and look at how resources are being used.
That all sounds pretty simple and indeed it is. I could write more about the methodology but I am concious that it is someone’s livelyhood and not to say too much.
Without sounding too knowledgeable or clever I was aware of most of the content that was discussed in the 2 days. Where I gained real benefits was in the storage aspects that Gaja covered very well and offered real insights into CPU, hyper threading, HBA usage and loading and suchlike. Various RAID levels were well covered and we had a very good discussion on the value of placing redo logs on RAID 1+0 and suchlike.
Most of the second day was taken up with running through some real world examples, some where the problem was obvious, others where I had no clue at all. Several people had brought in 10046 traces and AWR snapshots and we all gained real insight as Gaja looked at them for the first time and showed us the key points worthy of investigation.
All in all a very worthwhile 2 days. The target audience needs to have a reasonable Oracle knowledge beforehand as a couple of students were saying that they had found it very difficult to follow. Oh and be prepared for long days as on day one Gaja was still talking strongly upto 18:00 despite being deadbeat from travelling and the second day went on up to 17:00.
Executive summary. Highly recommended