Glenn Fawcett's Weblog

Oracle analysis 101 : Begining analysis techniques

Tuesday Dec 09, 2008

Recently, I was asked to present beginning Oracle analysis techniques to an internal audience of Sun engineers. This presentation was a lot of fun to put together and was well received. After cleaning it up a bit and taking out the boring internal Sun stuff, I thought the presentation might be useful to a larger audience. This presentation focuses on problem statement, environmental, and basic AWR/Statspack analysis.

If you find this useful or have suggestions, drop me a note.

[4] Comments
Like this post? del.icio.us | furl | slashdot | technorati | digg
Posted at 02:05PM Dec 09, 2008 by glennf in Oracle
Tags:
Comments:

Hi Glenn,
my 2ct:
1) page 21, Buffer Hit rate Values below 99% are suspect for OLTP.
No, it isn't, see J. Lewis's note on topic http://jonathanlewis.wordpress.com/2007/09/26/hit-ratios-4
2) page 32, Case study on latch free contention for dual.
I think it make sense to show how to get more info from statspack report before 10046 trace. For ex., scrolling down report to latch statistics (activity, sleep break down, miss sources). With such an explicit latch contention, CBC latches will be there on top. And probably Top Logical Reads per segment would be dual.

Posted by Timur Akhmadeev on December 11, 2008 at 06:58 AM PST #

Thanks for the comments, Timur.

1) I agree that cache hit ratios can be really misleading. Looking at them in isolation is never a good thing, which is why I struggled with adding this in the first place. I did want to provide at least a starting place for investigation for OLTP style environments. This presentation was given to sysadmin types with limited or no DBA knowledge.

Maybe it is better to just have them consider the overall amount of IO. Boy, it sure would be nice to have cache-hits broken down by table and contrast that with table size. The AWR report could do this since object statistics are already broken down by both physical and logical IO. Sounds like a script in the making :)

2) In this case, indeed CBC's were top, but unfortunately segment statistics were not available due to the snap level.

Posted by Glenn Fawcett on December 11, 2008 at 11:18 AM PST #

[Trackback] I've been a bit busy recently because I've had a rolling 4 week cold (like lots of people in the office - obviously several colds in quick succession), a couple of presentations at UKOUG (where I'm sure the additional beer didn't help recovery) and t...

Posted by Doug's Oracle Blog on December 17, 2008 at 05:37 PM PST #

Hi Glenn,

Thanks for the presentation.

"Problem wait events... “log file sync”"

"2ms or less is desirable"

In my experience, as regards log file sync, the average wait depends on the work-load. Ideally the wait time would be same or close to that of the log file parallel write. If significantly exceeded, then it would mean either a bug or a cpu bottleneck.

Thanks
Krishna Manoharan

Posted by Krishna Manoharan on December 20, 2008 at 12:33 PM PST #

Post a Comment:
Comments are closed for this entry.