[Oracle 10g] SQL Plan (Explain Plan/ DBMS_XPLAN) & Autotrace Enhancement in 10g
我们知道查询一条SQL的执行计划，一般可以通过如下两种方式(当然还有其他方式)
(1) Explain plan for / DBMS_XPLAN.DISPLAY
(2) Set autotrace on
Explain plan for 和 Set autotrace 都是SQL*PLUS命令，DBMS_XPLAN是Oracle提供的查询执行计划相关的包。
这两种方法在Oracle10g都得到了加强。DBMS_XPLAN最先是在Oracle9.2中引入的，但是只能用来查看SQL的“理论”上的执行计划(Explain plan for的结果)， 在10g中，可以通过新增加的DISPLAY_CURSOR来得到SQL（CURSOR)的“实际”执行计划信息。
(Tom 在<Effective Oracle by Design> 中提到如何在10g之前如何用DBMS_XPLAN.DISPLAY和V$SQL来得到实际执行的信息，下面会提到）
Autotrace提供了很多种关于SQL执行的信息，其中之一是SQL的执行计划(同样也是“理论”上的，不是实际的），但是Autotrace返回的其他统计信息(physical/logical i/o)却是实际的。 在10.2之前， Autotrace用的是自己的format方法，因此得到的SQL执行计划表同用DBMS_XPLAN不是很一致，但是从10.2开始，autotrace 采用了DBMS_XPLAN的方法来格式化最后输出的执行计划表。
需要注意的是，DBMS_XPLAN(确切说是EXPLAIN PLAN for) 和 autotrace 都依赖于表 PLAN_TABLE, 可能需要手动创建一下。 用autotrace还需要当前用户被被授予PLUSTRACE role. 关于如何进行设置EXPLAIN PLAN 和 Autotrace，参考另外一篇blog。
1. About DBMS_XPLAN
包DBMS_XPLAN在Oracle 9i R2中引入的，用来查看Explain Plan生成的执行计划。
在10g中, DBMS_XPLAN增加了用来查看SQL的实际执行的计划相关信息（DBMS_XPLAN.DISPLAY_CURSOR）。
（可以用DESCRIBE查看包DBMS_XPLAN中的信息）
SQL> desc DBMS_XPLAN
FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------TABLE_NAME VARCHAR2 IN DEFAULT
STATEMENT_ID VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FILTER_PREDS VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ -------- SQL_ID VARCHAR2 INPLAN_HASH_VALUE NUMBER(38) IN DEFAULT
DB_ID NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ -------- SQLSET_NAME VARCHAR2 IN SQL_ID VARCHAR2 INPLAN_HASH_VALUE NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
SQLSET_OWNER VARCHAR2 IN DEFAULT
FUNCTION FORMAT_NUMBER RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ -------- NUM NUMBER INFUNCTION FORMAT_NUMBER2 RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ -------- NUM NUMBER INFUNCTION FORMAT_SIZE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ -------- NUM NUMBER INFUNCTION FORMAT_SIZE2 RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ -------- NUM NUMBER INFUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------PLAN_CUR REF CURSOR IN
I_FORMAT_FLAGS BINARY_INTEGER INFUNCTION VALIDATE_FORMAT RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------HASPLANSTATS BOOLEAN IN
FORMAT VARCHAR2 IN FORMAT_FLAGS BINARY_INTEGER OUTSQL>
2. Column Projections
所谓column projections就是指DBMS_XPLAN给出的信息中包含了SQL执行计划中每一步实际选择的列的信息，这个有助于我们理解SQL执行计划每一步的实际动作。
以Scott中的表作为例子，
SQL> EXPLAIN PLAN set statement_id = 'X1'
2 FOR 3 SELECT d.deptno4 , d.dname
5 , COUNT(*) as count_employees
6 , SUM(e.sal) as sum_salaries
7 FROM dept d8 , emp e
9 WHERE d.deptno = e.deptno10 GROUP BY
11 d.deptno
12 , d.dname;
Explained.
下面通过调用DBMS_XPLAN.DISPLAY来查看上面这条SQL语句的执行计划，
（从DBMS_XPLAN.DISPLAY的函数接口可以看出，可以直接调用DBMS_XPLAN.DISPLAY,而不用传入任何参数，这样我们会得到刚刚执行的SQL语句的计划）
SQL> SELECT plan_table_output
2 FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'X1', 'ALL') );
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2708255165 -----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 180 | 7 (29)| 00:00:01 || 1 | HASH GROUP BY | | 9 | 180 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 280 | 6 (17)| 00:00:01 || 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 98 | 4 (25)| 00:00:01 || 6 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------- 1 - SEL$1
3 - SEL$1 / D@SEL$1
4 - SEL$1 / D@SEL$1
6 - SEL$1 / E@SEL$1
Predicate Information (identified by operation id):
--------------------------------------------------- 5 - access("D"."DEPTNO"="E"."DEPTNO") filter("D"."DEPTNO"="E"."DEPTNO") Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=2) "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14],
COUNT(*)[22], SUM("E"."SAL")[22]
2 - (#keys=0) "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14],
"E"."SAL"[NUMBER,22]
3 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
5 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."SAL"[NUMBER,22]
6 - "E"."SAL"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
39 rows selected.
返回结果的最后一部分就是关于Column Projection, 从中women可以看到SQL执行每一步返回的列的信息。
3. Query Blocks
Query Block部分在2中的执行计划信息中也可以看到。
关于Query Block的详细信息，可以参见Adrian Billington的原文。
4. Viewing actual execution plans with DBMS_XPLAN
9i 中 DBMS_XPLAN只是提供了“理论”上的的SQL执行计划(通过Explain Plan). 不过，9i提供了几个动态视图来供查询实际的SQL(cursor)执行计划信息，比如v$SQL_PLAN, v$SQL_PLAN_STATISTICS). 虽然如此，DBMS_XPLAN却没有能够提供返回容易阅读的格式化好的实际执行计划信息。
在10g中这个问题得到了解决， DBMS_XPLAN中增加的Function, DISPLAY_CURSOR, 用来返回格式化好(就像DISPLAY一样）的SQL实际执行的信息。DISPLAY_CURSOR主要是依赖于Oracle的几个动态视图， 包括v$SQL, v$SQL_PLAN, v$SQL_PLAN_STATISTICS_ALL等。
下面来看看如何使用DISPLAY_CURSOR,
SQL> SELECT d.dname
2 , SUM(e.sal) AS sum_sal
3 FROM dept d
4 , emp e
5 WHERE d.deptno = e.deptno
6 GROUP BY
7 d.dname;
DNAME SUM_SAL
-------------- ----------
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
SQL> SELECT plan_table_output
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
PLAN_TABLE_OUTPUT
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN
上面说到DISPLAY_CURSOR依赖于视图V$SQL_PLAN，因此需要赋予scott这个权限，
SQL> show user
USER is "SYS"
SQL> grant select on v_$sql_plan to scott;
Grant succeeded.
下面重新来过~
SQL> set serveroutput off
SQL>SQL> SELECT d.dname
2 , SUM(e.sal) AS sum_sal
3 FROM dept d4 , emp e
5 WHERE d.deptno = e.deptno6 GROUP BY
7 d.dname;
DNAME SUM_SAL
-------------- ----------ACCOUNTING 8750
RESEARCH 10875
SALES 9400
SQL>SQL> SELECT plan_table_output
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID fb38ja6gkq5wp, child number 0-------------------------------------WARNING: User has no SELECT privileges on V$SQLPlan hash value: 2708255165
-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 7 (100)| || 1 | HASH GROUP BY | | 4 | 80 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 280 | 6 (17)| 00:00:01 || 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 98 | 4 (25)| 00:00:01 || 6 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 5 - access("D"."DEPTNO"="E"."DEPTNO") filter("D"."DEPTNO"="E"."DEPTNO")24 rows selected.SQL>
注意返回的结果里面有个WARNING: User has no SELECT priviledge on V$SQL，说明我们还需要赋予scott该权限。
注意调用DISPLAY_CURSOR的时候，我们没有传入任何参数，这样的话这个函数会返回当前Session中最后一个执行的SQL(cursor)的执行计划信息。由于sqlplus默认会调用DBMS_OUTPUT.GET_LINES，影响到DISPLAY_CURSOR返回的结果，因此我们首先 set serveroutput off。
--- Tom’s Solution in <Effective Oracle by Design> in Oracle 9.2 to view actual execution plan
首先创建一个视图（也可以创建一个实际的表） 用来充当PLAN_TABLE的角色，只不过这次“存储”（因为是视图，只是动态从v$sql_plan里面得到）的是之前运行过的SQL语句的实际的执行计划。
CREATE OR REPLACE VIEW dynamic_plan_table
AS SELECT RAWTOHEX(address) || '_' || child_number AS statement_id
,SYSDATE timestamp ,operation,options
,object_node
,object_owner
,object_name
,0 object_instance
,optimizer
,search_columns
,id
,parent_id
,position,cost
,cardinality
,bytes
,other_tag
,partition_start
,partition_stop
,partition_id
,other
,distribution
,cpu_cost
,io_cost
,temp_space
,access_predicates
,filter_predicates
FROM v$sql_plan;
然后通过DBMS_XPLAN.DISPLAY来格式化输出结果，只不过对于DISPLAY的参数要做一些特殊处理，
SELECT plan_table_outputFROM TABLE(dbms_xplan.display
('dynamic_plan_table',(select rawtohex(address) || '_' || child_number x
from v$sql
where sql_text='select * from t t1 where object_id > 32000'),
'serial'))
可以看出DBMS_XPLAN.DISPLAY的第一个参数的值是dynamic_plan_table，就是刚才创建的view,这就让DBMS_XPLAN到刚才创建的视图里“取”数据，而不是默认的PLAN_TABLE。第二个参数的SQL语句是取获得之前执行的SQL语句对应的Statemnt_ID. 第三个参数'serial’是为了向后兼容， 可以换成'ALL’
5. Identifying Specific SQL Cursors
DISPLAY_CURSOR可以用来查询某个具体的SQL CURSOR的实际执行信息， 不过它需要传入一个参数： SQL_ID. (有时候还需要CHILD_CURSOR_NO参数）
SQL_ID是Oracle10g新增加的，在很多动态视图里面都存在，用来标示一条SQL语句。
需要注意的是，SQL_ID只是跟在shared pool里面的SQL语句的字符串(TEXT)是一一对应的，但并不是于SQL语句对应的Cursor是一一对应的。因为一条SQL语句(TEXT)可能会对应于多个child cursors(不同的执行计划). 正是因为这个原因，DISPLAY_CURSOR还接受另外一个参数—child cursor no (默认值为0， 因为大多数情况下每条SQL语句对应一个cursor)
另外， 一个Cusor(SQL_ID, CHILD_CURSOR)的执行计划也可能随着时间的迁移而变化，这个会在动态视图v$SQL_PLAN中以新的一条PLAN_HASH_VALUE来体现。
动态视图V$SESSION在Oracle 10G 中也得到了加强，这个视图多出来几列来显示SQL_ID/SQL_CHILD_NUMBER, PREV_SQL_ID/PREV_CHILD_NUMBER，这可以帮助我们定位到刚刚执行的的SQL语句。
为了通过DISPLAY_CURSOR得到具体某个SQL（cursor）实际的执行计划，显然我们需要得到该SQL的SQL_ID和CHILD_NUMBER). 可以通过查询动态视图v$SQL来得到SQL_ID。
SQL> conn scott/scottConnected.
SQL> SELECT d.dname
2 , SUM(e.sal) AS sum_sal
3 FROM dept d4 , emp e
5 WHERE d.deptno = e.deptno6 GROUP BY
7 d.dname;
DNAME SUM_SAL
-------------- ----------ACCOUNTING 8750
RESEARCH 10875
SALES 9400
SQL>SQL> SELECT sql_id
2 , child_number
3 , sql_text
4 FROM v$sql
5 WHERE LOWER(sql_text) LIKE 'select d.dname%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ----------------------------------------00chu48sqmz9p 0 SELECT d.dname , SUM(e.sal) AS sum_
sal FROM dept d , emp e WHERE d
.deptno = e.deptno GROUP BY d.dn
ame
SQL>
得到了SQL_ID和CHILD_NUMBER就可以调用DBMS_XPLAN.DISPLAY_CURSOR来得到这条SQL的实际执行信息了，
SQL> SELECT plan_table_output
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('00chu48sqmz9p', 0, 'ALL'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------SQL_ID 00chu48sqmz9p, child number 0-------------------------------------SELECT d.dname , SUM(e.sal) AS sum_sal FROM dept d , emp eWHERE d.deptno = e.deptno GROUP BY d.dname
Plan hash value: 2708255165
-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 7 (100)| || 1 | HASH GROUP BY | | 4 | 80 | 7 (29)| 00:00:01 |
| 2 | MERGE JOIN | | 14 | 280 | 6 (17)| 00:00:01 || 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 14 | 98 | 4 (25)| 00:00:01 || 6 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$13 - SEL$1 / D@SEL$1
4 - SEL$1 / D@SEL$1
6 - SEL$1 / E@SEL$1
Predicate Information (identified by operation id):
--------------------------------------------------- 5 - access("D"."DEPTNO"="E"."DEPTNO") filter("D"."DEPTNO"="E"."DEPTNO")Column Projection Information (identified by operation id):
----------------------------------------------------------- 1 - "D"."DNAME"[VARCHAR2,14], SUM("E"."SAL")[22]2 - "D"."DNAME"[VARCHAR2,14], "E"."SAL"[NUMBER,22]
3 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
5 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."SAL"[NUMBER,22]
6 - "E"."SAL"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
43 rows selected.SQL>--------------------------------------
Regards,
FangwenYu
