Shown below is the “SQL ordered by Elapsed Time” list from the “SQL Statistics” section of an Oracle AWR report:
After examining the list, we get the following observations:
- The list includes three SQL statements types: SELECT, INSERT, and PL/SQL (BEGIN …).
- For SELECT statements, the sum of %CPU and %IO is approximately 100%, indicating minimal non-productive waits other than CPU and I/O.
- For INSERT statements, the sum of %CPU and %IO is significantly below 100%, suggesting substantial non-productive wait, these are unhealthy.
The missing portion of DB Time can by quantified as (100% – %CPU – %IO) * %Total. Results for the top SQLs are:
| SQL ID | Calculation Process | Missing Ratio |
|---|---|---|
| 3fw75k1snsddx | (100%−5.23%−39.24%) × 17.29% | 9.60% |
| f7rxuxzt64k87 | (100%−10.95%−60.24%) × 10.15% | 2.92% |
| 9t3n2wpr7my63 | (100%−6.08%−59.00%) × 7.59% | 2.65% |
| gh2g2tynpcpv1 | (100%−4.69%−55.00%) × 3.93% | 1.58% |
| gzhkw1qu6fwxm | (100%−2.08%−0.01%) × 3.61% | 3.53% |
| budtrjayjnvw3 | (100%−11.36%−68.03%) × 2.21% | 0.46% |
The total missing portion is 9.60% + 2.92% + 2.65% + 1.58% + 3.53% + 0.46% = 20.74%. This SQL-level gap can be correlated to instance-level waits. The “Top 10 Foreground Events by Total Wait Time” section in the AWR report highlights three significant wait events other than CPU and I/O:
| Event | %DB Time | Wait Class |
|---|---|---|
| log file switch (checkpoint incomplete) | 19.9% | Configuration |
| buffer busy waits | 3.3% | Commit |
| log file sync | 1.4% | Commit |
These three waits affect INSERT operations rather than SELECT statements. The two redo-related waits are caused by the redo generated during inserts, while the buffer busy waits occur when multiple insert operations attempt to insert into the same data block simultaneously. Together, these waits account for 19.9% + 3.3% + 1.4% = 24.6% of DB Time, which closely aligns with the 20.74% gap observed at the SQL level.
The key take away here is to flag the SQL statements whose sum of the values of “%CPU” and “%IO” far below 100%, and try to find out the events other than CPU and I/O in the top event section which prevent the SQL.
Yuan Yao, An Oracle ACE's Blog 
Leave a comment