执行计划display_cursor函数
问题描述：关于oracle查看真实的执行计划，使用select * from table(dbms_xplan.display_cursor(null,null));的方式来获取执行计划
参考文档：https://blog.csdn.net/leshami/article/details/6866925
一、display_cursor描述
display_cursor用于显示SQL语句的真实的执行计划
DISPLAY_CURSOR语法
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
display_cursor函数参数描述
sql_id
指定位于库缓存执行计划中SQL语句的父游标。默认值为null。当使用默认值时当前会话的最后一条SQL语句的执行计划将被返回可以通过查询V$SQL 或 V$SQLAREA的SQL_ID列来获得SQL语句的SQL_ID。
child_number
指定父游标下子游标的序号。即指定被返回执行计划的SQL语句的子游标。默认值为0。如果为null，则sql_id所指父游标下所有子游标的执行计划都将被返回。
format
控制SQL语句执行计划的输出部分，即哪些可以显示哪些不显示。使用与display函数的format参数与修饰符在这里同样适用。
除此之外当在开启statistics_level=all时或使用gather_plan_statistics提示可以获得执行计划中实时的统计信息
有关详细的format格式描述请参考：dbms_xplan之display函数的使用 中format参数的描述
下面给出启用统计信息时format新增的修饰符
iostats 控制I/O统计的显示
last 默认，显示所有执行计算过的统计。如果指定该值，则只显示最后一次执行的统计信息
memstats 控制pga相关统计的显示
allstats 此为iostats memstats的快捷方式，即allstats包含了iostats和memstats
run_stats_last 等同于iostats last。只能用于oracle 10g R1
run_stats_tot 等同于iostats。只能用于oracle 10g R1
二、使用scott用户演示使用display_cursor函数获取执行计划
1、当前数据库版本以及加载执行计划到库缓存
SQL> show user USER is "SCOTT" SQL> select * from v$version where rownum<2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SQL> SQL> SELECT ename,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno AND e.empno = 7788; 2 3 4 ENAME DNAME LOC ---------- -------------- ------------- SCOTT RESEARCH DALLAS
2、查看真实的执行计划，不传递任何参数给display_cursor函数，显示当前会话最后一条SQL语句的执行计划
SQL> select * from table(dbms_xplan.display_cursor(null,null));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a67wqmkfb9j65, child number 0
-------------------------------------
SELECT ename,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno
AND e.empno = 7788
Plan hash value: 2385808155
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | NESTED LOOPS | | 1 | 54 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 24 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 30 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."EMPNO"=7788)
5 - access("E"."DEPTNO"="D"."DEPTNO")
24 rows selected.
3.获得SQL语句的SQL_ID，可以看出此SQL_ID与上面显示的执行计划中的SQL_ID一致
SQL> select sql_id,address,plan_hash_value,hash_value,child_number from v$sql where sql_text like '%SELECT ename%' and sql_text not like '%from v$sql%'; 2 SQL_ID ADDRESS PLAN_HASH_VALUE HASH_VALUE CHILD_NUMBER ------------- ---------------- --------------- ---------- ------------ a67wqmkfb9j65 0000000072C8C9D8 2385808155 2629092549 0
4.传递SQL_ID以及format参数，并配合修饰符控制执行计划的输出
/*-------------- ------------------------*/
SQL> select * from table(dbms_xplan.display_cursor('a67wqmkfb9j65',null,'typical -predicate -rows'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a67wqmkfb9j65, child number 0
-------------------------------------
SELECT ename,dname,loc FROM emp e, dept d WHERE e.deptno = d.deptno
AND e.empno = 7788
Plan hash value: 2385808155
--------------------------------------------------------------------------------
| Id | Operation | Name | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)| |
| 1 | NESTED LOOPS | | 54 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 24 | 1 (0)| 00:00:01 |
| 3 | INDEX UNIQUE SCAN | PK_EMP | | 0 (0)| |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 30 | 1 (0)| 00:00:01 |
| 5 | INDEX UNIQUE SCAN | PK_DEPT | | 0 (0)| |
--------------------------------------------------------------------------------
18 rows selected.
scott用户遇到的问题一：User has no SELECT privilege on V$SESSION
SQL> r
1* select * from table(dbms_xplan.display_cursor(null,null))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
解决：
grant select on v_$sql to scott;
grant select on v_$sql_plan to scott;
grant select on v_$session to scott;
grant select on v_$sql_plan_statistics_all to scott;
问题二：cannot fetch plan for SQL_ID: 372mc93u1j1yk
SQL> r
1* select * from table(dbms_xplan.display_cursor(null,null))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 372mc93u1j1yk, child number 0
select * from table(dbms_xplan.display_cursor(null,null))
NOTE: cannot fetch plan for SQL_ID: 372mc93u1j1yk, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)
解决：
SQL> set serveroutput off;
SQL> show serveroutput
serveroutput OFF
5.执行上述SQL语句后获得其真实的执行计划，使用了iostats last -predicate -note 修饰符控制显示输出
SQL> alter session set statistics_level=all; Session altered. SQL> select e.ename,e.sal,s.grade from emp e join salgrade s 2 3 on e.sal between losal and hisal and e.deptno = 20; 4 5 ENAME SAL GRADE ---------- ---------- ---------- SMITH 800 1 JONES 2975 4 SCOTT 3000 4 ADAMS 1100 1 FORD 3000 4 SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last -predicate -note')); SQL_ID 243b0tpjxj6wv, child number 1 ------------------------------------- select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between losal and hisal and e.deptno = 20 Plan hash value: 4204027666 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 | 13 | | 1 | MERGE JOIN | | 1 | 1 | 5 |00:00:00.01 | 13 | | 2 | SORT JOIN | | 1 | 5 | 5 |00:00:00.01 | 6 | | 3 | TABLE ACCESS FULL | EMP | 1 | 5 | 5 |00:00:00.01 | 6 | | 4 | FILTER | | 5 | | 5 |00:00:00.01 | 7 | | 5 | SORT JOIN | | 5 | 5 | 14 |00:00:00.01 | 7 | | 6 | TABLE ACCESS FULL| SALGRADE | 1 | 5 | 5 |00:00:00.01 | 7 | ------------------------------------------------------------------------------------------- 19 rows selected.
6.修改会话级别的参数statistics_level为typical并验证修改结果，scott用户无v$parameter权限可以用sys
SQL> col name format a40 col value format a25 col display_value format aSQL> SQL> 25 select name, value, display_value, isses_modifiable from v$parameter SQL> 2 where isses_modifiable = 'TRUE' and name like '%&input_name%'; 3 4 Enter value for input_name: statistics_level old 4: and name like '%&input_name%' new 4: and name like '%statistics_level%' NAME VALUE DISPLAY_VALUE ISSES ---------------------------------------- ------------------------- ------------------------- ----- statistics_level TYPICAL TYPICAL TRUE
7.使用提示gather_plan_statistics，并获得其真实执行计划，使用了allstats -rows修饰符控制显示输出
SQL> set pagesize 180
SELECT /*+ gather_plan_statistics */ ename,dname,loc
FROM emp e, dept d SQL> 2
WHERE e.deptno = d.deptno
AND d.deptno=20 ORDER BY 1,2,3; 3 4
ENAME DNAME LOC
---------- -------------- -------------
ADAMS RESEARCH DALLAS
FORD RESEARCH DALLAS
JONES RESEARCH DALLAS
SCOTT RESEARCH DALLAS
SMITH RESEARCH DALLAS
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats -rows'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d2hh42yzqqjz7, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ ename,dname,loc FROM emp e, dept
d WHERE e.deptno = d.deptno AND d.deptno=20 ORDER BY 1,2,3
Plan hash value: 3339094711
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 |00:00:00.01 | 8 | | | |
| 1 | SORT ORDER BY | | 1 | 5 |00:00:00.01 | 8 | 2048 | 2048 | 1/0/0|
| 2 | NESTED LOOPS | | 1 | 5 |00:00:00.01 | 8 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 |00:00:00.01 | 2 | | | |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 |00:00:00.01 | 1 | | | |
|* 5 | TABLE ACCESS FULL | EMP | 1 | 5 |00:00:00.01 | 6 | | | |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"=20)
5 - filter("E"."DEPTNO"=20)
24 rows selected.
