Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle dbms_xplan


Oracle Tips by Burleson Consulting

 

The Oracle dbms_xplan Utility

For the dbms_xplan utility to work, 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 can be very helpful in diagnosing Oracle performance issues.

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’);

The old PLAN_TABLE and explain plan format can be replaced by what DBMS_XPLAN  has to offer. DBMS_XPLAN is a gateway not only to SQL statements, but also into the AWR. Armed with the SELECT_CATALOG_ROLE, a user can view several dynamic performance views.

The package runs with the privileges of the calling user, so the user needs to have select privileges on v$sql_plan , v$session , and v$sql_plan_statistics_all

A simple implementation is to add EXPLAIN PLAN FOR just before a statement, and then view the plan by issuing:

 

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

 

Putting it together in an example:

 

SQL> conn scott/tiger

Connected.

SQL> EXPLAIN PLAN FOR

  2  SELECT * FROM emp e, dept d

  3     WHERE e.deptno = d.deptno

  4     AND e.ename='benoit';

 

Explained.

 

SQL> set lines 110 pages 35

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------

Plan hash value: 3625962092

 


----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |     1 |    57 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |         |       |       |            |          |

|   2 |   NESTED LOOPS               |         |     1 |    57 |     4   (0)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    37 |     3   (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - filter("E"."ENAME"='benoit')

   4 - access("E"."DEPTNO"="D"."DEPTNO")

 

The dbms_xplan package has four subprograms: DISPLAY, DISPLAY_AWR, DISPLAY_CURSOR and DISPLAY_SQLSET. The DISPLAY option was just shown in the prior example. Since the SELECT statement is also a cursor, take a look at the DISPLAY_CURSOR function.

 

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------

SQL_ID  7v1g3p9b8052u, child number 0

-------------------------------------

SELECT * FROM table(DBMS_XPLAN.DISPLAY)

 

Plan hash value: 2137789089

 

-------------------------------------------------------------

| Id  | Operation                         | Name    | Cost  |

-------------------------------------------------------------

|   0 | SELECT STATEMENT                  |         |    29 |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |

-------------------------------------------------------------

 

Note

-----

   - cpu costing is off (consider enabling it)

 

 

17 rows selected.

 

A big payoff in using DBMS_XPLAN is the quick and easy way of seeing what a statement’s SQL_ID value is. Not that the DBA will be typing that odd-looking string in all that much, but it does make some more sophisticated queries easy to code since one does not have to find the ID

The other benefit is that the ID can be used to query again from the table and be able to see past statements and their plans

The DISPLAY function gets even more granular than being able to query on older ID. One can specify an input parameter for format. The choices are ROWS, BYTES, COST, PARTITION, and PARALLEL, to name a few

To pull AWR information, use the DISPLAY_AWR function.

 

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('7v1g3p9b8052u'));

 

Going up one level would be tracing a session, either one’s own or someone else’s via a remote interface. Although some skilled people can read through an unformatted trace file, mere mortals can use TKPROF  to format “trc” trace files. In addition to basic formatting, the actual execution plan (explainy) as an input parameter will output the plans.

Becoming more sophisticated in the tracing is when wait events are also analyzed. Wait analysis is the de facto means of analyzing performance. The days of X-whatever ratios are gone, although they can be useful as trend indicators

Tracing at the TKPROF  level is good for SQL, but what about PL/SQL? That is where DBMS_PROFILERcomes into play.

Above the individual session level are STATSPACK and AWR. AWR reports, or what ADDM generates from the AWR, are based on STATSPACK reports. AWR reports, however, contain much more drill down type of information. AWR reports, available since the release of Oracle 10g, also reflect the increased amount of instrumentation found within the RDBMS

The old PLAN_TABLE and explain plan format can be replaced by what DBMS_XPLAN has to offer. DBMS_XPLAN is a gateway not only to SQL statements, but also into the AWR. Armed with the SELECT_CATALOG_ROLE, a user can view several dynamic performance views. The package runs with the privileges of the calling user, so the user needs to have select privileges on V$SQL_PLAN, V$SESSION, and V$SQL_PLAN_STATISTICS_ALL.

A simple implementation is to add EXPLAIN PLAN FOR just before a statement, and then view the plan by issuing:

 

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

 

Putting it together in an example:

 

SQL> conn scott/tiger

Connected.

SQL> EXPLAIN PLAN FOR

  2  SELECT * FROM emp e, dept d

  3     WHERE e.deptno = d.deptno

  4     AND e.ename='benoit';

 

Explained.

 

SQL> set lines 110 pages 35

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------

Plan hash value: 3625962092

 

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |     1 |    57 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |         |       |       |            |          |

|   2 |   NESTED LOOPS               |         |     1 |    57 |     4   (0)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    37 |     3   (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - filter("E"."ENAME"='benoit')

   4 - access("E"."DEPTNO"="D"."DEPTNO")

 

The package has four subprograms: DISPLAY, DISPLAY_AWR, DISPLAY_CURSOR and DISPLAY_SQLSET. The DISPLAY option was just shown in the prior example. Since the SELECT statement is also a cursor, take a look at the DISPLAY_CURSOR function.

 

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------

SQL_ID  7v1g3p9b8052u, child number 0

-------------------------------------

SELECT * FROM table(DBMS_XPLAN.DISPLAY)

 

Plan hash value: 2137789089

 

-------------------------------------------------------------

| Id  | Operation                         | Name    | Cost  |

-------------------------------------------------------------

|   0 | SELECT STATEMENT                  |         |    29 |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |

-------------------------------------------------------------

 

Note

-----

   - cpu costing is off (consider enabling it)

 

 

17 rows selected.

 

A big payoff in using DBMS_XPLAN is the quick and easy way of seeing what a statement’s SQL_ID value is. Not that the DBA will be typing that odd-looking string in all that much, but it does make some more sophisticated queries easy to code since one does not have to find the ID.

The other benefit is that the ID can be used to query again from the table and be able to see past statements and their plans.

 

The DISPLAY function gets even more granular than being able to query on older ID. One can specify an input parameter for format. The choices are ROWS, BYTES, COST, PARTITION, and PARALLEL, to name a few.

 

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


 
 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.