A lot of new features have been added in version 2.0 of xplan, the sqlplus script I use to investigate about SQL statements performance (I spoke about version 1.0 in this post). Here’s a brief description.
wait profile (from ASH)
For each statement, its wait profile is calculated fetching wait information from Active Session History:
[text]
—————————————–
|ash event |cnt |% |
—————————————–
|enq: HW – contention |2606|61.0|
|enq: TX – row lock contention| 875|20.5|
|db file sequential read | 344| 8.0|
|enq: TX – index contention | 158| 3.7|
|gc current grant busy | 152| 3.6|
|cpu | 56| 1.3|
|gc current block 2-way | 34| 0.8|
|gc current block busy | 13| 0.3|
|gc buffer busy | 10| 0.2|
|gc cr block 2-way | 7| 0.2|
|gc current grant 2-way | 5| 0.1|
|read by other session | 5| 0.1|
|direct path write | 3| 0.1|
|gc cr block busy | 3| 0.1|
|gc cr grant 2-way | 1| 0.0|
|SQL*Net more data from client| 1| 0.0|
|cr request retry | 1| 0.0|
—————————————–
[/text]
By default this feature is on in 10g+ and inspects a window of ash_profile_mins=15 minutes from v$active_session_history.…
