Currently browsing category


Xplan: now with “self” measures for row source operations

One of the most useful information that the Oracle kernel attaches to plans in the library cache are measures of various resource consumption figures, such as elapsed time, consistent and current gets, disk reads, etcetera. These can be made available for each plan line (aka “row source operation”).

These figures are always cumulative, that is, include both the resource consumed by the line itself and all of its progeny.…

xplan: dbms_metadata.get_ddl for tables referenced by the plan

As a minor but useful new feature, xplan is now able to integrate into its report the DDL of tables (and indexes) referenced by the plan, calling dbms_metadata.get_ddl transparently.

This is mostly useful to get more details about referenced tables’ constraints and partitions definition – to complement their CBO-related statistics that xplan reports about.

This feature can be activated by specifing dbms_metadata=y or dbms_metadata=all (check xplan.sql…

Xplan 2.0

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:
|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|

By default this feature is on in 10g+ and inspects a window of ash_profile_mins=15 minutes from v$active_session_history.…