DBA Tips Archive for Oracle
Formatted Explain Plan Output
by Jeff Hunter, Sr. Database Administrator
This article provides several SQL scripts that format information stored in the PLAN_TABLE into a more readable format. With each new release, Oracle has added added many new features.
These scripts are for anyone interested in viewing the access path of SQL queries. DBAs and Developers will use the Oracle supplied script $ORACLE_HOME/rdbms/admin/utlxplan.sql to create the PLAN_TABLE. The plan table is populated with data about the execution plan using the EXPLAIN PLAN statement. Once the plan table is populated, queries are run against the stored data to retrieve the the plan in a readable format.
When using these scripts, remember that repeated use of the EXPLAIN PLAN
clause does not automatically delete 'old' explain plans from the plan_table,
which may result in confusing results.
Such confusion may be avoided by deleting 'old' plans manually or using the STATEMENT_ID column to record a plan name which can then be queried.
Creating the PLAN_TABLE
Use the utlxplan.sql script to create the table as instructed below.SQL> @?/rdbms/admin/utlxplanNote that the plan table format can change between versions so ensure that you create it using the utlxplan script from the current version.
To Populate the Plan Table
SQL> EXPLAIN PLAN FOR <your query goes here>; Explained.
The version of SQL*Plus provided with 7.3.4 and above contains a utility called autotrace which allows query plans to be displayed on the fly. To use this utility the current schema must contain a plan table built using the utlxplan.sql mentioned earlier.
To produce a plan without executing the query (in a similar manner to EXPLAIN PLAN FOR) the autotrace string isSQL> set autotrace traceonly explainSee Automatic EXPLAIN PLANS and TKPROF for more on autotrace.
With Oracle 9i (version 9.2), Oracle supplies a utility called DBMS_XPLAN. It is created by dbmsutil.sql which is called by catproc.sql. As such it should already be installed on most 9.2 databases.
To generate a formatted explain plan of the query that has just been 'explained':SQL> set lines 130 SQL> set head off SQL> spool q_plan.lst SQL> alter session set cursor_sharing=EXACT; SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL')); SQL> spool off
NOTE: The DBMS_XPLAN.DISPLAY() procedure will display the explain plan of the last explain plan command. All examples shown will assume that the PLAN_TABLE table has been created. Also,
Also, with SQL*plus, it is recomended to set linesize and pagesize before running these examples. For example:set linesize 100 set pagesize 0
The following can be used to display the explain plan of the last explain plan command. In this example, we do not want to display information related to Parallel Query. We will use the display table function from the DBMS_XPLAN package to display the last explain plan. Force serial option for backward compatibility:SQL> set markup html preformat on SQL> SELECT plan_table_output FROM table(dbms_xplan.display('plan_table',null,'serial'));Use default mode which will display only relevant information. This version of the command will also display Parallel Query information if the plan happens to run parallel:SQL> set markup html preformat on SQL> select * from table(dbms_xplan.display());
In version 8.1.5 and above, scripts were provided to produce a formatted explain plan from the plan table.
These are located in $ORACLE_HOME/rdbms/admin and are called:
utlxplp.sql - UTiLity eXPLain Parallel plans utlxpls.sql - UTiLity eXPLain Serial plans
Oracle 9i Release 9.2 also adds predicate information to the output.
Alternative 7.3 Explain plan
This example includes the Cost, Cardinality and byte estimates available using explain plan on 7.3, as well as PQO node information:set echo off column operation format a16 column options format a15 column object_name format a20 column id format 99 column query heading "Query Plan" format a80 SELECT LPAD(' ',2*(level-1))||operation||' '||options||' '||object_name||' ' ||decode(object_node,'','','['||object_node||'] ') ||decode(OPTIMIZER,'','','['||OPTIMIZER||'] ') ||decode(id,0,'Cost='||position, decode(COST,'','',' Cost='||COST||' ' ||decode(id,0,'','Card='||CARDINALITY||' ') ||decode(id,0,'','Bytes='||BYTES) ) ) query FROM plan_table START WITH id = 0 CONNECT BY PRIOR id = parent_id / set echo on
This is an enhanced version of the example select in:
Oracle7 Application Developers Guide (Release 7.0 & 7.1) page B-17 Oracle7 Server Tuning Release 7.2 page A-23
The script below adds query slave details when using the parallel query option PQO), the optimizer_goal of the statement and indicates if optimizer statistics are present for individual objects.set echo off column operation format a16 column options format a15 column object_name format a20 column id format 99 column query heading "Query Plan" format a80 SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || object_name ||' ' || decode(object_node,'','','[' || object_node || '] ') || decode(optimizer,'','','['||optimizer||'] ') ||decode(id,0,'Cost = '||position) query FROM plan_table START WITH id = 0 CONNECT BY PRIOR id = parent_id; set echo onPlan table 'other' columnThe other column in the plan_table may contain useful query details depending on the type of query performed. The other column is a long column. The main situations are as follows:Example Statement
- Referencing remote objects.
When an object on a remote database is queried, the other column gives details of the query passed to the remote site.
- Using the Parallel Query option (PQO).
When the PQO is used, the other column gives details of the select that is being performed by the query slave referenced in the query node column.set echo off set long 200 column query heading "Query Plan" format a45 column other heading 'Other' format a34 SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || object_name || ' ' || decode(object_node,'','','[' || object_node || '] ') query , other FROM plan_table START WITH id = 0 CONNECT BY PRIOR id = parent_id; set echo on
From ORACLE RDBMS Performance Tuning Guide Version 6.0 page 7-15:SELECT lpad(' ',2*level)||operation||' '||options||' '||object_name "Query Plan" FROM plan_table CONNECT BY PRIOR id = parent_id START WITH id = 1;
Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at email@example.com.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.