Oracle analysis 101 : Begining analysis techniques

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.

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 10, 2008 at 10:58 PM 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 03: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 09:37 AM 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 04:33 AM PST #

Post a Comment:
Comments are closed for this entry.
About

This blog discusses performance topics as running on Sun servers. The main focus is in database performance and architecture but other topics can and will creep in.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
News

No bookmarks in folder

Blogroll

No bookmarks in folder