oracle查看执行计划之DBMS_XPLAN

zpjx

　　使用DBMS_XPLAN包中的方法是在oracle数据库中得到目标SQL的执行计划的另一种方法。针对不同的应用场景吗，你可以选择如下四种方法中的一种：
　　select * from table(dbms_xplan.display)
　　select * from table(dbms_xplan.display_cursor(null,null,'advenced'));
　　select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));
　　select * from table(dbms_xplan.display_awr('sql_id'));
　　方法1是执行select * from table(dbms_xplan.display)，这需要与explain plan命令配合使用，它用于查看使用explain plan命令后得到的执行计划。
　　方法2是执行select * from table(dbms_xplan.display_cursor(null,null,'advenced'));它用于查看刚刚执行过的sql的执行计划，这里针对dbms_xplan.display_cursor所传入的第一个和第二参数的值均为null，第三参数的值是“advanced”，第三个输入参数的值也可以是“all”，只不过用“advanced”后的显示结果会比用“all”的显示结果更详细些。
　　SQL> conn scott/scott;
　　Connected.
　　SQL> set linesize 800
　　SQL> set pagesize 900
　　SQL> col plan_table_output for a200
　　SQL> select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;
　　EMPNO ENAME      DNAME
　　---------- ---------- --------------
　　7782 CLARK      ACCOUNTING
　　7839 KING       ACCOUNTING14 rows selected.
　　SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
　　PLAN_TABLE_OUTPUT
　　-------------------------------------------------------------------------------------------
　　SQL_ID7ww0fhpbqqt0n, child number 0
　　-------------------------------------
　　select empno,ename,dname from emp,dept where emp.deptno=dept.deptno
　　Plan hash value: 844388907
　　----------------------------------------------------------------------------------------

　　|>　　----------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT     |       |       |       |     6 (100)|       |
　　|   1 |  MERGE JOIN     |       |    14 |   364 |     6(17)| 00:00:01 |
　　|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2 (0)| 00:00:01 |
　　|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |
　　|*  4 |   SORT JOIN     |       |    14 |   182 |     4(25)| 00:00:01 |
　　|   5 |    TABLE ACCESS FULL     | EMP     |    14 |   182 |     3 (0)| 00:00:01 |
　　----------------------------------------------------------------------------------------

　　Query Block Name / Object Alias (identified by operation>　　-------------------------------------------------------------
　　1 - SEL$1
　　2 - SEL$1 / DEPT@SEL$1
　　3 - SEL$1 / DEPT@SEL$1
　　5 - SEL$1 / EMP@SEL$1
　　Outline Data
　　-------------
　　/*+
　　BEGIN_OUTLINE_DATA
　　IGNORE_OPTIM_EMBEDDED_HINTS
　　OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
　　DB_VERSION('11.2.0.4')
　　ALL_ROWS
　　OUTLINE_LEAF(@"SEL$1")
　　INDEX(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
　　FULL(@"SEL$1" "EMP"@"SEL$1")
　　LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")
　　USE_MERGE(@"SEL$1" "EMP"@"SEL$1")
　　END_OUTLINE_DATA
　　*/

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
　　filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

　　Column Projection Information (identified by operation>　　-----------------------------------------------------------
　　1 - "DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
　　2 - "DEPT"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]
　　3 - "DEPT".ROWID[ROWID,10], "DEPT"."DEPTNO"[NUMBER,22]
　　4 - (#keys=1) "EMP"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22],
　　"ENAME"[VARCHAR2,10]
　　5 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "EMP"."DEPTNO"[NUMBER,22]
　　58 rows selected.
　　SQL> select * from table(dbms_xplan.display_cursor(null,null,'all'));
　　PLAN_TABLE_OUTPUT
　　-------------------------------------------------------------------------------------------
　　SQL_ID7ww0fhpbqqt0n, child number 0
　　-------------------------------------
　　select empno,ename,dname from emp,dept where emp.deptno=dept.deptno
　　Plan hash value: 844388907
　　----------------------------------------------------------------------------------------

　　|>　　----------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT     |       |       |       |     6 (100)|       |
　　|   1 |  MERGE JOIN     |       |    14 |   364 |     6(17)| 00:00:01 |
　　|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2 (0)| 00:00:01 |
　　|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |
　　|*  4 |   SORT JOIN     |       |    14 |   182 |     4(25)| 00:00:01 |
　　|   5 |    TABLE ACCESS FULL     | EMP     |    14 |   182 |     3 (0)| 00:00:01 |
　　----------------------------------------------------------------------------------------

　　Query Block Name / Object Alias (identified by operation>　　-------------------------------------------------------------
　　1 - SEL$1
　　2 - SEL$1 / DEPT@SEL$1
　　3 - SEL$1 / DEPT@SEL$1
　　5 - SEL$1 / EMP@SEL$1

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
　　filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

　　Column Projection Information (identified by operation>　　-----------------------------------------------------------
　　1 - "DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
　　2 - "DEPT"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]
　　3 - "DEPT".ROWID[ROWID,10], "DEPT"."DEPTNO"[NUMBER,22]
　　4 - (#keys=1) "EMP"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22],
　　"ENAME"[VARCHAR2,10]
　　5 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "EMP"."DEPTNO"[NUMBER,22]
　　41 rows selected.
　　当参数传入all，显示结果中了“Qutline Data”部分的内容：
　　方法3是执行    select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));它用于查看指定sql的执行计划。这里针对方法dbms_xplan.display_cursor所传人的第一个参数的值是指定sql的sql_id或者sql hash value,第二参数的值是要看执行计划所在的child cursor number，第三个参数是advcanced或者all
　　SQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select empno,ename,dname%';
　　SQL_TEXT
　　select empno,ename,dname from emp,dept where emp.deptno=dept.deptno
　　SQL_ID      HASH_VALUE CHILD_NUMBER
　　------------- ---------- ------------
　　7ww0fhpbqqt0n 1466655764    0
　　只要目标sql的执行计划所在的child cursor还没有被page out出shared pool，就可以使用方法3查看该sql的执行计划：
　　SQL> select * from table(dbms_xplan.display_cursor('2qm0f3qgsqqyc',0,'advanced'));
　　PLAN_TABLE_OUTPUT
　　-------------------------------------------------------------------------------------------
　　SQL_ID2qm0f3qgsqqyc, child number 0
　　-------------------------------------
　　select empno,ename,dname from scott.emp,scott.dept where
　　scott.emp.deptno=scott.dept.deptno
　　Plan hash value: 844388907
　　----------------------------------------------------------------------------------------

　　|>　　----------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT     |       |       |       |     6 (100)|       |
　　|   1 |  MERGE JOIN     |       |    14 |   364 |     6(17)| 00:00:01 |
　　|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2 (0)| 00:00:01 |
　　|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |
　　|*  4 |   SORT JOIN     |       |    14 |   182 |     4(25)| 00:00:01 |
　　|   5 |    TABLE ACCESS FULL     | EMP     |    14 |   182 |     3 (0)| 00:00:01 |
　　----------------------------------------------------------------------------------------

　　Query Block Name / Object Alias (identified by operation>　　-------------------------------------------------------------
　　1 - SEL$1
　　2 - SEL$1 / DEPT@SEL$1
　　3 - SEL$1 / DEPT@SEL$1
　　5 - SEL$1 / EMP@SEL$1
　　Outline Data
　　-------------
　　/*+
　　BEGIN_OUTLINE_DATA
　　IGNORE_OPTIM_EMBEDDED_HINTS
　　OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
　　DB_VERSION('11.2.0.4')
　　ALL_ROWS
　　OUTLINE_LEAF(@"SEL$1")
　　INDEX(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
　　FULL(@"SEL$1" "EMP"@"SEL$1")
　　LEADING(@"SEL$1" "DEPT"@"SEL$1" "EMP"@"SEL$1")
　　USE_MERGE(@"SEL$1" "EMP"@"SEL$1")
　　END_OUTLINE_DATA
　　*/

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
　　filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

　　Column Projection Information (identified by operation>　　-----------------------------------------------------------
　　1 - "DNAME"[VARCHAR2,14], "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10]
　　2 - "DEPT"."DEPTNO"[NUMBER,22], "DNAME"[VARCHAR2,14]
　　3 - "DEPT".ROWID[ROWID,10], "DEPT"."DEPTNO"[NUMBER,22]
　　4 - (#keys=1) "EMP"."DEPTNO"[NUMBER,22], "EMPNO"[NUMBER,22],
　　"ENAME"[VARCHAR2,10]
　　5 - "EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "EMP"."DEPTNO"[NUMBER,22]
　　59 rows selected.
　　我们可以通过dbms_xplan.display_awr来得到该sql的所有历史执行计划：
　　SQL> set lines 100
　　SQL> select * from table(dbms_xplan.display_awr('cy097a90nu4fk'));
　　PLAN_TABLE_OUTPUT
　　SQL_ID cy097a90nu4fk
　　--------------------
　　Plan hash value: 1999179007
　　-------------------------------------------------------------------------------------------

　　|>　　Inst   |IN-OUT|
　　-------------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT                 |                |       |       |    11 (100)|          |
　　|      |
　　|   1 |  HASH GROUP BY                   |                |     1 |   101 |    11  (19)| 00:00:01 |
　　|      |
　　|   2 |   VIEW                           |                |     1 |   101 |    10  (10)| 00:00:01 |
　　|      |
　　|   3 |    HASH GROUP BY                 |                |     1 |   261 |    10  (10)| 00:00:01 |
　　|      |
　　|   4 |     FILTER                       |                |       |       |            |          |
　　|      |
　　|   5 |      NESTED LOOPS                |                |       |       |            |          |
　　|      |
　　|   6 |       NESTED LOOPS               |                |     1 |   261 |     9   (0)| 00:00:01 |
　　|      |
　　|   7 |        REMOTE                    |                |     1 |   176 |     5   (0)| 00:00:01 |
　　DBLK_~ | R->S |
　　|   8 |        INDEX RANGE SCAN          | PRODUCTSPEC_PK |     1 |       |     1   (0)| 00:00:01 |
　　|      |
　　|   9 |       TABLE ACCESS BY INDEX ROWID| PRODUCTSPEC    |     1 |    41 |     2   (0)| 00:00:01 |
　　|      |
　　-------------------------------------------------------------------------------------------

　　Remote SQL Information (identified by operation>　　----------------------------------------------------
　　7 - SELECT "A1"."EVENTTIMEKEY","A1"."EVENTID","A1"."EVENTTIME","A1"."FOLLOTID","A1"."PRODUCTSPECI
　　D","A1"
　　."PROCESSFLOWID","A1"."OLDOPERATIONID","A1"."QUANTITY","A1"."EQUIPMENTID","A2"."PROCESSFLOWID
　　","A2"."REWORK
　　TYPE" FROM "LOTHISTORY" "A1","PROCESSFLOW" "A2" WHERE "A2"."REWORKTYPE"='Normal' AND
　　"A1"."PROCESSFLOWID"="A2"."PROCESSFLOWID" AND "A1"."EVENTID"='TrackIn' AND "A1"."OLDOPERATION
　　ID"='BFOP001'
　　AND (SUBSTR("A1"."FOLLOTID",1,1)='V' OR SUBSTR("A1"."FOLLOTID",1,1)='P') AND
　　"A1"."EVENTTIME">=TO_DATE('20140918080001','YYYY-MM-DD HH24:MI:SS') AND
　　"A1"."EVENTTIME"