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.


  1. Martin Berger

    Friday, August 7, 2009

    Alberto,
    What a nice improvemet for xplan! I used it quite regulary and I will use your new version with at least the same gratitude.
    As you now also show related objects, you might want to include assicuated statistics, if they exist. I know, they are used rarely, but if they exist, they will sum up the informations xplan will deliver.
    Martin

  2. Alberto Dell'Era

    Saturday, August 8, 2009

    @Martin

    thanks, and I will add your request to my to-do list, absolutely.
    Just to be sure – you mean the statistics added with the command ASSOCIATE STATISTICS, do you ?

  3. Martin Berger

    Saturday, August 8, 2009

    Alberto,
    yes, I mean the statiscits added with ASSOCIATE STATISTICS. To be honest, Adrian Billington brought me to them resently: http://www.oracle-developer.net/display.php?id=426#

  4. Alberto Dell'Era

    Sunday, August 9, 2009

    @Martin

    I have added the associated statistics to the report output:

    ############################################# function DELLERA.PLSQL_FUNC ###
ASSOCIATED STATISTICS:  default selectivity (.001) default cost (cpu=100 io=10 net=1)
function plsql_func (p varchar2)
return varchar2
is
begin
  return p;
end plsql_func;

    I’ve not implemented anything for domain index and indextypes.
    Hence now, the three types of associated statistics (selectivity/cost/stat type), associated to three types of objects (functions/packages/types) are reported by xplan.
    I’ve also improved the report here and there (especially improved the way dependent materialized views are reported – now there’s the defining statement and the CBO stats of the container table in a simpler output).

  5. Martin Berger

    Sunday, August 9, 2009

    Alberto,
    you are just great!
    ( I will have to read your scripts in detail, I’m sure there are tons of gems hidden for simply minds like me ).
    please keep improving the oracle-world with your knowledge!
    Martin

  6. rich

    Friday, June 18, 2010

    Hello Sir,
    this would be silly question but I am not able to get the output from your script, please help me to solve this.
    I want to use this great script for my day to day issues of PT.

    used all option but no luck ..

    @xplan.sql “%select jp.cp_job_id%” “”

    @xplan.sql “” “sql_id=ab4rjccnt8dvx”

    @xplan.sql “” “sql_id=ab4rjccnt8dvx,hash=697579389″

    getting following errors

    Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
    NCHAR | NCHAR (n) | NVARCHAR2 (n) |
    CLOB | NCLOB | REFCURSOR ] ]
    Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
    NCHAR | NCHAR (n) | NVARCHAR2 (n) |
    CLOB | NCLOB | REFCURSOR ] ]
    SP2-0552: Bind variable “CURRENT_ERROR” not declared.
    Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
    NCHAR | NCHAR (n) | NVARCHAR2 (n) |
    CLOB | NCLOB | REFCURSOR ] ]
    Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
    NCHAR | NCHAR (n) | NVARCHAR2 (n) |
    CLOB | NCLOB | REFCURSOR ] ]
    Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
    NCHAR | NCHAR (n) | NVARCHAR2 (n) |
    CLOB | NCLOB | REFCURSOR ] ]
    Usage: VAR[IABLE] [ [ NUMBER | CHAR | CHAR (n) | VARCHAR2 (n) |
    NCHAR | NCHAR (n) | NVARCHAR2 (n) |
    CLOB | NCLOB | REFCURSOR ] ]
    SP2-0552: Bind variable “INSTANCE_NAME” not
    …
    ..
    SP2-0552: Bind variable “OPT_PLAN_STATS” not declared.
    SP2-0552: Bind variable “OPT_PLAN_STATS” not declared.
    SP2-0552: Bind variable “OPT_PLAN_STATS” not declared.
    …
    ..
    Input truncated to 46 characters
    Input truncated to 2 characters
    Input truncated to 2 characters
    Input truncated to 15 characters

  7. Alberto Dell'Era

    Friday, June 18, 2010

    @rich

    a) are you using a recent version of sqlplus (e.g. 10g, 11g) ?

    b) have you extracted all the files contained in xplan.zip in the current directory ?

    regards
    Alberto

  8. Rich

    Friday, August 6, 2010

    thanks Sir,
    sorry for late reply, my problem got solved ..

    Rich ..

  9. Rich

    Friday, August 6, 2010

    hi Sir,
    I am getting following error, while running the xplan.
    I tried
    SET SERVEROUTPUT ON size UNL

    but no luck ..any suggestions ..

    @xplan “” “sql_id=akwwmt0txutp3″

    ERROR at line 1:
    ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
    ORA-06512: at “SYS.DBMS_OUTPUT”, line 32
    ORA-06512: at “SYS.DBMS_OUTPUT”, line 97
    ORA-06512: at “SYS.DBMS_OUTPUT”, line 112
    ORA-06512: at line 174
    ORA-06512: at line 238
    ORA-06512: at line 252
    ORA-06512: at line 1694
    ORA-06512: at line 2702
    ORA-06512: at line 2856

  10. Alberto Dell'Era

    Friday, August 6, 2010

    Hi Rich,

    can you tell me :

    - the version of the database you are connecting to
    - the version of sqlplus you are using

    The most recent the version of sqlplus, the better; I usually use 11.2 to connect even to older databases.

  11. Rich

    Wednesday, August 11, 2010

    hello Sir,
    sorry for delayed reply, I am using 10202 sqlplus version ..
    I will try with 11.2 version ..

    thanks for writing a such a beautiful script, I can’t say. how useful it was for me ..

    Rich ..

  12. » Best Oracle Peformance Tools?

    Thursday, January 13, 2011

    [...] xplan – extend explain plan info from Alberto Dell Era [...]

  13. Lynn Sattler

    Tuesday, April 19, 2011

    Alberto,

    I’ve chatted before with you about your xplan routine.

    I’ve been using it some more and I read your documentation again.
    I learned by trial and error that I could get it to sort the output by execution time per execution.
    Here is what I used:
    @xplan “%” “parsed_by=ONEADMIN,order_by=elapsed_time/executions desc”

    If you are so inclined you may want to add this sort option to your samples.

    Thanks for the xplan routines, I am finding them more useful. We have the sql tune / addm big dollar utilities. I find good info in your output that does not show up with addm report.

    Lynn Sattler
    Toledo, Ohio USA

  14. Alberto Dell'Era

    Sunday, May 1, 2011

    Hi Lynn,

    I’m happy to see that you find xplan useful and that you are so kind in letting me know :)

    I have added your suggestion in the header of xplan.sql and I have also expanded the example description
    a bit more – it is indeed possible to add full expressions in the order-by list, provided that you turn
    commas into semicolons:

    – order_by: (default: null)
    – Order statements by the specified columns/expressions. Ties are ordered by sql_text and child_number.
    – Use the semicolon instead of the comma in expressions.
    – For example: “order_by=elapsed_time desc;buffer_gets”
    – “order_by=elapsed_time/decode(executions;0;null;executions) desc;buffer_gets”

    This is useful also in your scenario, since it happens far too often that you get an executions=0 row, and
    all it takes to spoil the fun is just one row ;)

    ciao
    Alberto

  15. Lynn Sattler

    Monday, May 2, 2011

    Alberto,

    Have a new issue with xplan.

    Running against v11.1 linux rh5 with multibyte characterset:
    NLS_NCHAR_CHARACTERSET AL16UTF16
    NLS_LENGTH_SEMANTICS CHAR

    ( I have a hunch the issue is related to the characterset because xplan works fine against another v 11.1 db)

    Issued: @xplan “” “” (have tried different options, same results)
    get this:

    new 147: :OPT_ASH_PROFILE_MINS := 15;
    old 148: &COMM_IF_LT_10G. end if;
    new 148: end if;
    declare /* xplan_exec_marker */ — process options
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 28

    SQL>
    SQL> — print current options values
    SQL> variable CURRENT_XPLAN_OPTIONS varchar2(500 char)
    SQL> begin
    2 select /*+ xplan_exec_marker */
    3 ‘inst_id=’ || :OPT_INST_ID
    4 || ‘ plan_stats=’||:OPT_PLAN_STATS

    Any ideas?
    Lynn Sattler

  16. Kyle Hailey » Best Oracle Performance Tools

    Friday, September 13, 2013

    [...] xplan [...]

  17. Daya

    Wednesday, April 23, 2014

    @Martin, Rich, Alberto

    Where is the link to download the zip files. I couldn’t find the link to download the folder. can some body ping me the link to download the files.

    Thanks,
    Daya

  18. Alberto Dell'Era

    Friday, April 25, 2014

    Daya,

    here it is:

    http://www.adellera.it/scripts_etcetera/xplan/index.html

    the zip can be found by following the first link on the page

Leave a Comment

Please enclose SQL code inside the tag pair [sql] ... [/sql]

Subscribe without commenting

Links (alphabetical order)

Blogroll (alphabetical order)

Blog Aggregators (alphabetical order)


Switch to our mobile site