oracle查看执行计划之DBMS_XPLAN

使用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 800SQL> set pagesize 900SQL> col plan_table_output for a200SQL> 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_ID 7ww0fhpbqqt0n, child number 0————————————-select empno,ename,dname from emp,dept where emp.deptno=dept.deptnoPlan hash value: 844388907—————————————————————————————-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————————-| 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 id):————————————————————- 1 – SEL$1 2 – SEL$1 / DEPT@SEL$1 3 – SEL$1 / DEPT@SEL$1 5 – SEL$1 / EMP@SEL$1Outline 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 id):————————————————— 4 – access(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”) filter(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”)Column Projection Information (identified by operation id):———————————————————– 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_ID 7ww0fhpbqqt0n, child number 0————————————-select empno,ename,dname from emp,dept where emp.deptno=dept.deptnoPlan hash value: 844388907—————————————————————————————-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————————-| 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 id):————————————————————- 1 – SEL$1 2 – SEL$1 / DEPT@SEL$1 3 – SEL$1 / DEPT@SEL$1 5 – SEL$1 / EMP@SEL$1Predicate Information (identified by operation id):————————————————— 4 – access(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”) filter(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”)Column Projection Information (identified by operation id):———————————————————– 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或者allSQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like ‘select empno,ename,dname%’;SQL_TEXTselect empno,ename,dname from emp,dept where emp.deptno=dept.deptnoSQL_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_ID 2qm0f3qgsqqyc, child number 0————————————-select empno,ename,dname from scott.emp,scott.dept wherescott.emp.deptno=scott.dept.deptnoPlan hash value: 844388907—————————————————————————————-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————————-| 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 id):————————————————————- 1 – SEL$1 2 – SEL$1 / DEPT@SEL$1 3 – SEL$1 / DEPT@SEL$1 5 – SEL$1 / EMP@SEL$1Outline 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 id):————————————————— 4 – access(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”) filter(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”)Column Projection Information (identified b免费云主机域名y operation id):———————————————————– 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 100SQL> select * from table(dbms_xplan.display_awr(‘cy097a90nu4fk’));PLAN_TABLE_OUTPUTSQL_ID cy097a90nu4fk——————–Plan hash value: 1999179007——————————————————————————————-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |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 id):—————————————————- 7 – SELECT “A1″.”EVENTTIMEKEY”,”A1″.”EVENTID”,”A1″.”EVENTTIME”,”A1″.”FOLLOTID”,”A1″.”PRODUCTSPECID”,”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”.”OLDOPERATIONID”=’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”
PRD’ )55 rows selected.不过此方法显示执行计划中看不到执行步骤对应的谓词条件。

相关推荐: PostgreSQL 源码解读（10）- 插入数据#9（ProcessQuery）

本文简单介绍了PG插入数据部分的源码，主要内容包括ProcessQuery函数的实现逻辑，该函数位于文件pquery.c中。ProcessQuery函数使用的数据结构、宏定义以及依赖的函数等。数据结构/宏定义1、NodeTag2、MemoryContext3、…