Quick Analysis of an Oracle AWR report

Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach
by Sam R. Alapati, Darl Kuhn and Bill Padfield
Step 1: Check DB Time

If DB time is significantly higher than the elapsed time then things are waiting.

Elapsed: 60.10 (mins)
DB Time: 197.70 (mins)

The example in the book shows DB time 20x the elapsed.

Step 2: Instance Efficiency

Hopefully these will be above 90%

Buffer Nowait %: 100.00
Redo NoWait %: 100.00
Buffer Hit %: 99.88
In-memory Sort %: 99.95
Library Hit %: 98.84
Soft Parse %: 97.81
Execute to Parse %: 83.30
Latch Hit %: 100.00
Non-Parse CPU: 99.89
Parse CPU to Parse Elapsd %: 0.00 %

The last one shows how much time the CPU is parsing SQL statments, so the lower the better.

Step 3: Top 5 Timed Events

This tell you where the highest resources are being consumed. In the book the example is the db file sequential read would indicate that there where full table scans being performed.

For my AWR report is looks like most of the resources are used in the remote connections over DBlink. Now I need to find out if this is due to a poor network or just user load.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s