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.
成都創(chuàng)新互聯(lián)公司10多年企業(yè)網(wǎng)站設(shè)計(jì)服務(wù);為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁(yè)設(shè)計(jì)及高端網(wǎng)站定制服務(wù),企業(yè)網(wǎng)站設(shè)計(jì)及推廣,對(duì)加固等多個(gè)方面擁有豐富的網(wǎng)站制作經(jīng)驗(yàn)的網(wǎng)站建設(shè)公司。
DBMS_XPLAN包是用來格式化執(zhí)行計(jì)劃輸出的，其最初在9i的時(shí)候引入，用于替代用戶執(zhí)行utlxpls.sql腳本和查詢計(jì)劃表；在隨后的ORACLE版本中增強(qiáng)了這個(gè)包的功能
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.sql CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table; GRANT ALL ON sys.plan_table TO public;
The DISPLAY function allows us to display the execution plan stored in the plan table. First we explain a SQL statement.
CONN scott/tiger EXPLAIN PLAN FOR SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH';
Next we use the DBMS_XPLAN.DISPLAY function to display the execution plan.
重慶分公司
重慶分公司
SET LINESIZE 130
SET PAGESIZE 0
SELECT *
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：指定計(jì)劃表的名字，默認(rèn)為PLAN_TABLE
statement_id：指定要顯示的執(zhí)行計(jì)劃的statement_id；默認(rèn)為null，意味著顯示計(jì)劃表中最新的執(zhí)行計(jì)劃
format：格式化定制輸出執(zhí)行計(jì)劃，默認(rèn)值為format；還有basic，all，serial以及阿斗advanced值
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' FOR
SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';
SET LINESIZE 130
SELECT *
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>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.
ORACLE10.1之后的版本引入了一個(gè)新的display_cursor函數(shù)，這個(gè)函數(shù)用于顯示存儲(chǔ)在library cahce池中的真實(shí)的執(zhí)行計(jì)劃；而不是像display函數(shù)那樣顯示一個(gè)從plan_table評(píng)估出的執(zhí)行計(jì)劃。
display_cursor函數(shù)從動(dòng)態(tài)視圖v$sql_plan_statistics_all和v$sql_plan中獲取信息，所以用戶要具有這兩個(gè)視圖的執(zhí)行權(quán)限
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：游標(biāo)池中的sql_id(其實(shí)就是父游標(biāo)號(hào))，sql_id可以從v$sql和v$sqlarea視圖中獲取，也可以使用prev_sal_id列從v$session視圖中獲?。荒J(rèn)會(huì)取出會(huì)話最近執(zhí)行的語(yǔ)句的游標(biāo)信息
child_number：子游標(biāo)號(hào)；如果沒有指定，將會(huì)展示父游標(biāo)下的所有子游標(biāo)的執(zhí)行計(jì)劃
format：允許使用display函數(shù)的所有的format參數(shù)，還可以設(shè)置runstats-last和runstats_tot來獲取最近一次的和全部的運(yùn)行時(shí)統(tǒng)計(jì)信息；需要設(shè)置為statistics_level=all狀態(tài)
The following example show the advanced output from a query on the SCOTT schema.
CONN / AS SYSDBA
GRANT 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/tiger
SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';
SET LINESIZE 130
SELECT *
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 (100)| |
| 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$1
Outline 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>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.
文章原文：http://oracle-base.com/articles/9i/dbms_xplan.php
