Shulou(Shulou.com)06/01 Report--

The DBMS_XPLAN package is used to format the output of an explain plan. It was introduced in Oracle 9i as a replacement for the "utlxpls.sql" script or custom queries of the plan table. Subsequent database versions have increased the functionality of the package.

The DBMS_XPLAN package, which is used to format the output of the execution plan, was originally introduced in 9i as a substitute for users to execute utlxpls.sql scripts and query the plan table; the functionality of this package was enhanced in subsequent versions of ORACLE

Setup

If it is not already present create the SCOTT schema.

Conn sys/password as sysdba@$ORACLE_HOME/rdbms/admin/utlsampl.sql

Create a PLAN_TABLE if it does not already exist.

Conn sys/password as sysdba@$ORACLE_HOME/rdbms/admin/utlxplan.sqlCREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;GRANT ALL ON sys.plan_table TO public;DISPLAY Function

The DISPLAY function allows us to display the execution plan stored in the plan table. First we explain a SQL statement.

CONN scott/tigerEXPLAIN PLAN FORSELECT * FROM emp e, dept dWHERE e.deptno = d.deptnoAND e.ename = 'SMITH'

Next we use the DBMS_XPLAN.DISPLAY function to display the execution plan.

SET LINESIZE 130SET PAGESIZE 0SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY) -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 58 | 4 (0) | 00:00:01 | | 1 | NESTED LOOPS | | 2 | NESTED LOOPS | | 1 | 58 | 4 (0) | 00:00:01 | * 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 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" = 'SMITH') 4 -access ("E". "DEPTNO" = "D". "DEPTNO") 18 rows selected.SQL >

The DBMS_XPLAN.DISPLAY function can accept 3 optional parameters:

Table_name-Name of the PLAN_TABLE, default value 'PLAN_TABLE'.

Statement_id-Statement id of the plan to be displayed. The default value is NULL, which displays the most recent execution plan in the PLAN_TABLE.

Format-Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC',' ALL', 'SERIAL'. There is also an undocumented 'ADVANCED' setting.

Table_name: specifies the name of the schedule. Default is PLAN_TABLE.

Statement_id: specifies that the statement_id; of the execution plan to be displayed defaults to null, which means that the latest execution plan in the schedule is displayed

Format: format custom output execution plan. The default value is format;, basic,all,serial and Adou advancedvalue.

Note. From Oracle 10g Release 2 onwards the format of the output can be tailored by using the standard list of formats along with keywords that represent columns to including or excluding (prefixed with'-'). As a result, the format column can now be a space or comma delimited list. The list of available columns varies depending on the database version and function being called. Check the documentation for your version.

EXPLAIN PLAN SET STATEMENT_ID='TSH' FORSELECT * FROM emp e, dept dWHERE e.deptno = d.deptnoAND e.ename = 'SMITH';SET LINESIZE 130SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY (' PLAN_TABLE','TSH','BASIC')) Plan hash value: 3625962092-| Id | Operation | Name |-- | 0 | SELECT STATEMENT | | 1 | NESTED LOOPS | | 2 | NESTED LOOPS | | 3 | TABLE ACCESS FULL | EMP | 4 | INDEX UNIQUE SCAN | PK_DEPT | | 5 | TABLE ACCESS BY INDEX ROWID | DEPT |-- -12 rows selected.SQL >

DISPLAY_CURSOR Function

In Oracle 10g Release 1 Oracle introduced the DISPLAY_CURSOR function. Rather than displaying an execution plan from the PLAN_TABLE, it displays the actual execution plan used to run a query stored in the cursor cache. This information is gathered from the V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views, so the user must have access to these.

Later versions of ORACLE10.1 introduce a new display_cursor function that displays the actual execution plan stored in the library cahce pool, rather than showing an execution plan evaluated from plan_table, as the display function does.

The display_cursor function gets the information from the dynamic views v$sql_plan_statistics_all and v$sql_plan, so the user should have the permission to execute these two views.

It accepts three optional parameters:

Sql_id-The SQL_ID of the statement in the cursor cache. The SQL_ID as available from the V$SQL and V$SQLAREA views, or from the V$SESSION view using the PREV_SQL_ID column. If omitted, the last cursor executed by the session is displayed.

Child_number-The child number of the cursor specified by the SQL_ID parameter. If not specified, all cursors for the specified SQL_ID are diaplyed.

Format-In addition to the setting available for the DISPLAY function, this function also has' RUNSTATS_LAST' and 'RUNSTATS_TOT' to display the last and total runtime statistics respectively. These additional format options require "STATISTICS_LEVEL=ALL".

Sql_id: the sql_id in the cursor pool (in fact, the parent cursor number). Sql_id can be obtained from the v$sql and v$sqlarea views, or from the v$session view using the prev_sal_id column. By default, the cursor information of the most recent statements executed by the session will be fetched.

Child_number: child cursor number; if not specified, the execution plan of all child cursors under the parent cursor will be displayed

Format: allows you to use all the format parameters of the display function, and you can also set runstats-last and runstats_tot to get the latest and all runtime statistics; you need to set it to statistics_level=all state

The following example show the advanced output from a query on the SCOTT schema.

CONN / AS SYSDBAGRANT SELECT ON v_$session TO scott;GRANT SELECT ON v_$sql TO scott;GRANT SELECT ON v_$sql_plan TO scott;GRANT SELECT ON v_$sql_plan_statistics_all TO scott;CONN scott/tigerSELECT * FROM emp e, dept dWHERE e.deptno = d.deptnoAND e.ename = 'SMITH';SET LINESIZE 130SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (format = >' ADVANCED')) PLAN_TABLE_OUTPUT-SQL_ID gu62pbk51ubc3 Child number 0--SELECT * FROM emp e Dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH'Plan hash value: 3625962092-| Id | Operation | | Name | Rows | Bytes | Cost (% CPU) | Time |-| 0 | SELECT STATEMENT | | | 4 | 1 | NESTED LOOPS | 2 | NESTED LOOPS | | 1 | 58 | 4 (0) | 00:00:01 | | * 3 | TABLE ACCESS FULL | EMP | | | 1 | 38 | 3 (0) | 00:00:01 | | * 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0) | | 5 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0) | 00:00:01 |-| -Query Block Name / Object Alias (identified by operation id):- -1-SEL$1 3-SEL$1 / E@SEL$1 4-SEL$1 / D@SEL$1 5-SEL$1 / D@SEL$1Outline Data- / * + BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE ('11.2.0.2') DB_VERSION ('11.2.0.2') ALL_ROWS OUTLINE_LEAF (@ "SEL$1") FULL (@ "SEL$1"E" @ "SEL$1") INDEX (@ "SEL$1"D" @ "SEL$1" ("DEPT". "DEPTNO") LEADING (@ "SEL$1"E" @ "SEL$1"D" @ "SEL$1") USE_NL (@ "SEL$1"D" @ "SEL$1") NLJ_BATCHING (@ "SEL$1"D" @ "@" SEL$1 ") END_OUTLINE_DATA * / Predicate Information (identified by operation id):-3-filter (" E "." ENAME "= 'SMITH') 4-access (" E "." DEPTNO "=" D "." DEPTNO ") Column Projection Information (identified by operation id ):-1-"E". "EMPNO" [NUMBER 22], "E". "ENAME" [VARCHAR2,10], "E". "JOB" [VARCHAR2,9], "E". "MGR" [NUMBER,22], "E". "HIREDATE" [DATE,7], "E". "SAL" [NUMBER,22], "E". "COMM" [NUMBER,22], "E". "DEPTNO" [NUMBER,22], "D". DEPTNO [NUMBER,22], "D". "DNAME" [VARCHAR2,14] "D". "LOC" [VARCHAR2,13] 2-"E". "EMPNO" [NUMBER,22], "E". "ENAME" [VARCHAR2,10], "E". "JOB" [VARCHAR2,9], "E". "MGR" [NUMBER,22], "E". "HIREDATE" [DATE,7], "E". "SAL" [NUMBER,22], "E". COMM [NUMBER,22], "E". "DEPTNO" [NUMBER,22] "D" .ROWID [ROWID,10], "D". "DEPTNO" [NUMBER,22] 3-"E". "EMPNO" [NUMBER,22], "E". "ENAME" [VARCHAR2,10], "E". "JOB" [VARCHAR2,9], "E". "MGR" [NUMBER,22], "E". "HIREDATE" [DATE,7], "E". SAL [NUMBER,22], "E". "COMM" [NUMBER,22] "E". "DEPTNO" [NUMBER,22] 4-"D" .ROWID [ROWID,10], "D". "DEPTNO" [NUMBER,22] 5-"D". "DNAME" [VARCHAR2,14], "D". "LOC" [VARCHAR2,13] 67 rows selected.SQL > Other Functions

There are some other useful functions in the package, but I don't find myself using them very often, so they are summarized below. If you need more information, follow the links at the bottom of the article for the appropriate database version.

DISPLAY_AWR-Introduced in Oracle 10g Release 1, this function displays an execution plan stored in the Advanced Workload Repository (AWR).

DISPLAY_SQLSET-Introduced in Oracle 10g Release 2, this function displays the execution plan of a given statement stored in a SQL tuning set.

DISPLAY_SQL_PLAN_BASELINE-Introduced in Oracle 11g Release 1, this function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.

DISPLAY_PLAN-Introduced in Oracle 11g Release 2, this function displays the contents of the plan table in a variety of formats.

Original article: http://oracle-base.com/articles/9i/dbms_xplan.php

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.