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.