Alberto Dell'Era

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.

Important note: you must have bought the appropriate Oracle licence (i.e. the Diagnostic Pack in 11.1) to read from that view and hence to use this feature (xplan will output a warning to remember you about that); you can disable this feature by setting ash_profile_mins=0.

Dump of dependent object definitions

If the statement references some database objects (e.g. a view, a pl/sql function) and hence depends on them, xplan will list them right below the statement text:

SELECT /*+ index(t,t_fbi) ordered use_nl(v) xplan_test_marker */
       T.RR, PLSQL_FUNC(MAX(T.X))
  FROM T, V
 WHERE UPPER(T.X) >= '0'
   AND T.X > :B1
   AND V.RR ='x'
 GROUP BY T.RR
 ORDER BY T.RR

- depends on view DELLERA.V
- depends on function DELLERA.PLSQL_FUNC

and the object definition will be reported at the bottom of the xplan output:

############################################# function DELLERA.PLSQL_FUNC ###
function plsql_func (p varchar2)
return varchar2
is
begin
  return p;
end plsql_func;
############################################# view DELLERA.V ###
view columns: #1 X(NUMBER),#2 PADDING(VARCHAR2),#3 RR(VARCHAR2)
select x, padding, rr
  from t
 where x > 0

Reading other RAC instance statements

Now you can read from another instance by specifying the option inst_id (defaults to the instance you are connected). This is handy for inspecting other instances of the RAC cluster without reconnecting.

Automatic dump of AWR most-expensive statements

The experimental script xplan_awr.sql will inspect AWR (Active Workload Repository) and dump all the statements that are still in the library cache and that have exceeded some resource consumption thresholds in any of the periods marked by two consecutive AWR snapshots. Thresholds can be the percentage of total (e.g. dump if the CPU consumption is more that 10% of total CPU) or the ranking position (e.g. dump if the statement ranks more than 5th in the CPU chart - the typical "top-N" analysis). The thresholds are configurable in the topmost "params" WITH clause.

Again, you must have bought the appropriate Oracle licence to use AWR, and hence to run xplan_awr.sql.

• Friday, August 7, 2009 performance tuning, tools, xplan
• jump to comments