Oracle dbms_xplan

Oracle Tips by Burleson Consulting

The Oracle dbms_xplan Utility

The DBA or developer first needs to create the plan table. The DDL for this table is in the $ORACLE_HOME/rdbms/admin/utllxplan.sql file.

The dbms_xplan.display_awr procedure van be very helpful in diagnosing Oracle performance issues.

Using the ASH tables, I was able to go through different times of day and compare reports from times where performance was good, with those times when performance was bad.

The ASH components allowed me to compare SQL_IDs between different times of day to see which queries seemed to be going through dramatic changes. Once a SQL_ID was found that experienced such a dramatic change, I used DBMS_XPLAN.DISPLAY_AWR to find all the different iterations of the query.

In case you’ve never used it, DBMS_XPLAN.DISPLAY_AWR is very useful. It allows you to input only a SQL_ID, and Oracle will show you the explain plans for that SQL_ID as recorded in the AWR. For instance:

select * from TABLE(dbms_xplan.display_awr(’93djdy6ss3’));

The next step is to look through the results to find any inconsistencies large enough to result in huge differences in performance.

The create table statement for dbms_xplan can be submitted in any users schema. The next step in using dbms_xplan is running Explain Plan for a statement.

explain plan for
select * from employee where emp_id = 64523;

The command above will populate the plan table with the data returned from the optimizer. Next, the Oracle dbms_xplan utility can be used to view the output.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Oracle dbms_xplan Options

dbms_xplan has only one procedure – display.

dbms_xplan.display (
table_name in varchar2 default ‘PLAN_TABLE’,
statement_id in varchar2 default null,
format in varchar2 default ‘TYPICAL’);

Greg Rahn of Oracle Corporation notes a great example of using dbms_xplan.display_cursor.

As a first step of triage, I would suggest executing the query with a GATHER_PLAN_STATISTICS hint followed by a call to DBMS_XPLAN.DISPLAY_CURSOR.

select /*+ gather_plan_statistics */ ... from ... ;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

Related notes on DBMS_XPLAN:

Oracle 11g provides the following information about the DBMS_XPLAN package:

Description of the DBMS_XPLAN package:



FUNCTION DBMS_XPLAN.BUILD_PLAN_XML RETURNS XMLTYPE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 PLAN_ID                        NUMBER                  IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 FILTER_PREDS                   VARCHAR2                IN     DEFAULT
 PLAN_TAG                       VARCHAR2                IN     DEFAULT
 REPORT_REF                     VARCHAR2                IN     DEFAULT

FUNCTION DBMS_XPLAN.DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 FILTER_PREDS                   VARCHAR2                IN     DEFAULT

FUNCTION DBMS_XPLAN.DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 DB_ID                          NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION DBMS_XPLAN.DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION DBMS_XPLAN.DISPLAY_PLAN RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TABLE_NAME                     VARCHAR2                IN     DEFAULT
 STATEMENT_ID                   VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 FILTER_PREDS                   VARCHAR2                IN     DEFAULT
 TYPE                           VARCHAR2                IN     DEFAULT

FUNCTION DBMS_XPLAN.DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQLSET_NAME                    VARCHAR2                IN
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 SQLSET_OWNER                   VARCHAR2                IN     DEFAULT

FUNCTION DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_HANDLE                     VARCHAR2                IN     DEFAULT
 PLAN_NAME                      VARCHAR2                IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

FUNCTION DBMS_XPLAN.FORMAT_NUMBER RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN

FUNCTION DBMS_XPLAN.FORMAT_NUMBER2 RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN

FUNCTION DBMS_XPLAN.FORMAT_SIZE RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN

FUNCTION DBMS_XPLAN.FORMAT_SIZE2 RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN

FUNCTION DBMS_XPLAN.FORMAT_TIME_S RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NUM                            NUMBER                  IN

FUNCTION DBMS_XPLAN.PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PLAN_CUR                       REF CURSOR              IN
 I_FORMAT_FLAGS                 BINARY_INTEGER          IN

FUNCTION DBMS_XPLAN.VALIDATE_FORMAT RETURNS BOOLEAN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 HASPLANSTATS                   BOOLEAN                 IN
 FORMAT                         VARCHAR2                IN
 FORMAT_FLAGS                   BINARY_INTEGER          OUT

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books