Optimizing SQL statements with xplan

Xplan is a utility to simplify and automate the first part of every SQL statement tuning effort, that is, collecting the real plan of the statement, its execution statistics (number of executions, number of buffer gets performed, etc), getting the definition of all the accessed tables (and their indexes), and, last but not least, the CBO-related statistics of the accessed tables (and their indexes and columns) stored in the data dictionary by dbms_stats or ANALYZE.

The utility doesn't need to install any object inside the database since it is a read-only sqlplus script, thus needing minimal support from the customers' DBA production team. It is also so simple to run that I am normally able to ask people with minimal Oracle skills to run xplan on my behalf and then to ship me the output report - with obvious benefits for all.

The report is light and concise, designed with the needs of the SQL tuner in mind; to illustrate, the following small fragment of the report helps to get an immediate picture of the indexes layout:

-----------------------------------
|ColName     |1|2|3|4|5|P|U1|U2|R1|
--------------------U-U------------
|X           |1|1|2|1| |1|  |2 |  |
|PADDING     | | |1|2|1|2|R1|1 |  |
|RR          | | | | | | |  |  |1 |
|SYS_NC00004$|2| | | | | |  |  |  |
|SYS_NC00005$| |2| | | | |  |  |  |
-----------------------------------

You can see at a glance that column X is indexed as the first column of indexes #1, #2 and #4 (#4 being a unique index) and as the second of index #3. Constraints are reported as well, for example the PK is (X, PADDING) and there are two unique constraints (U1, U2) and a FK (R1) constraint.

To see all the information that the report provides, you can check the showcase example in the xplan main page linked above, whose report is annotated to explain the various sections meaning. The most interesting ones are surely the plan section and the table/index/column/partition information.

Using xplan is very simple; just connect to the database with sqlplus (SELECT ANY DICTIONARY and SELECT ANY TABLE are the only necessary privileges) and run the xplan.sql script. There are various ways to tell xplan which statements to report about; probably the most useful one is to ask for statements whose text matches a SQL like expression, for example

SQL>@xplan "select%from%customer%" ""

will dump all the SELECT statements that were run on the CUSTOMER table.

It is also possible to dump a statement by hash value (or by sql_id, module, action, parsing user, and even child number):

SQL>@xplan "" "hash=3280933266"

Some further customizations are possible - for example, you can order the matching statements (technically, the matching child cursors in the shared sql area) by elapsed_time, buffer_gets, etc; you can get a different output file for each hash_value, instead of a single output file; you can suppress or enable certain sections of the report; and so on. For the full list and further details, please check the xplan.sql header.

Xplan is free to download and use. If you decide to try it - for any question, comment or feature request, feel free to drop a comment on this post.


  1. coskan

    Monday, June 8, 2009

    Hi Alberto,

    First of all thank you very much for sharing this tool with community. I am currently using Tanel Poders scripts + dbms_xplan utility for seeing the execution plans but I think this tool also rocks and lets us get rid of too many steps.

    IMHO The only addition can be added to choose what not to see in case if I need only the execution plan plus filters. you might add an option s/f/a (simple full advanced )

  2. Alberto Dell'Era

    Monday, June 8, 2009

    @coskan

    There’s already something very similar to what you are asking for:

    SQL>@xplan “select%dual%” “tabinfos=N,plan_details=N”

    that suppresses the table infos (usually the longest part of the report) and the plan details.

    There’s also an option, “dbms_xplan=Y”, that adds the output of dbms_xplan.display_cursor() to the report (10g+ only of course).

    If you use Tanel’s scripts you will find the xplan interface similar – in fact, as credited in the xplan.sql header, I borrowed some ideas from one of the first scripts of Tanel’s …

    Thanks for your kind comments :)

  3. Statistique

    Monday, June 8, 2009

    Great bunch of scripts well tied up together. I’m going to use this everyday. I was just too lazy to wrap all my scripts together !

    Many Thanks.

  4. sandro

    Wednesday, June 10, 2009

    Hi Alberto,
    can you explain me why I have ColName that start from 0?

    Thanks in advance.

  5. Alberto Dell'Era

    Wednesday, June 10, 2009

    @sandro

    Might you please elaborate on your question ? ColName contains the column name as it is found in the data dictionary, if the column name starts with a number, it will be reproduced as is … even if creating a table with a leading number requires to enclose the name in double-quotes, and it is very rarely done.

  6. sandro

    Wednesday, June 10, 2009

    Sorry for my misunderstanding!
    This is extract from my report.

    ——————————————————————————————-
    |Id|IId|ColName |Type |Null |0|1|2|3|4|P |R0|R1|R2|R3|R4|R5|R6|R7|R8|
    ———————————————————–U——————————-
    | 1| 1|TEDO_TIPO |VARCHAR2 (2 byte) |NOT NULL| | | | |1|R1| | | | | | | | | |
    | 2| 2|TEDO_PROG |VARCHAR2 (10 byte)|NOT NULL| | | | |2|R2| | | | | | | | | |
    | 3| 3|TEDO_SERV_CODI|VARCHAR2 (8 byte) |NOT NULL| | | |1|

    My question is:
    why I have 0,1,2,3,ecc…and it strats from “0″?

  7. Alberto Dell'Era

    Wednesday, June 10, 2009

    @sandro

    You should read the report row-wise, for example the first row details the first column of the table:

    1) the column id (“Id”) is 1
    2) the column internal id (“IId”) is 1
    3) the column name (“ColName”) is TEDO_TIPO
    4) the column type (“Type”) is VARCHAR2 (2 byte)
    5) the column nullability (“Null”) is NOT NULL
    6) indexes 0,1,2,3 do not reference this column
    7) index 4 has this column as the first column
    … etcetera.

    If you look down on your report, you’ll find the definition of the indexes, for example you’ll find
    ### index #4
    that you’ll see is defined on (TEDO_TIPO, TEDO_PROG)

    Further details on the main page of xplan – look at the commented example there :)

  8. Blogroll Report 06/06/09 – 12/06/09 « Coskan’s Approach to Oracle

    Friday, June 12, 2009

    [...] Alberto Dell’era – Optimizing SQL statements with xplan [...]

  9. Alberto Dell’Era’s Oracle blog » Xplan 2.0

    Friday, August 7, 2009

    [...] script I use to investigate about SQL statements performance (I spoke about version 1.0 in this post). Here’s a brief [...]

  10. optimizing oracle performance

    Saturday, March 20, 2010

    [...] example, similar performance and energy cost savings can be obtained by tuning Oracle databases) …Alberto Dell’Era’s Oracle blog Optimizing SQL statements …Xplan is a utility to simplify and automate the first part of every SQL statement tuning effort, [...]

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