问题描述：关于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 userUSER is "SCott"sql> select * from v$version where rownum<2; BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Productionsql> 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 DALLAS2、查看真实的执行计划，不传递任何参数给display_cursor函数，显示当前会话最后一条SQL语句的执行计划
sql> select * from table(dbms_xplan.display_cursor(null,null));PLAN_table_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------sql_ID a67wqmkfb9j65,child number 0-------------------------------------SELECT ename,loc FROM emp e,dept d WHERE e.deptno = d.deptnoAND e.empno = 7788Plan 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%'; 2sql_ID ADDRESS PLAN_HASH_VALUE HASH_VALUE CHILD_NUMBER------------- ---------------- --------------- ---------- ------------a67wqmkfb9j65 0000000072C8C9D8 2385808155 2629092549 04.传递sql_ID以及format参数，并配合修饰符控制执行计划的输出
/*-------------- ------------------------*/sql> select * from table(dbms_xplan.display_cursor('a67wqmkfb9j65',null,'typical -predicate -rows'));PLAN_table_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------sql_ID a67wqmkfb9j65,loc FROM emp e,dept d WHERE e.deptno = d.deptnoAND e.empno = 7788Plan 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 1JOnes 2975 4SCott 3000 4AdamS 1100 1FORD 3000 4sql> select * from table(dbms_xplan.display_cursor(null,'iostats last -predicate -note'));sql_ID 243b0tpjxj6wv,child number 1-------------------------------------select e.ename,s.grade from emp e join salgrade s on e.salbetween losal and hisal and e.deptno = 20Plan 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_levelold 4: and name like '%&input_name%'new 4: and name like '%statistics_level%'name VALUE disPLAY_VALUE ISSES---------------------------------------- ------------------------- ------------------------- -----statistics_level TYPICAL TYPICAL TRUE7.使用提示gather_plan_statistics，并获得其真实执行计划，使用了allstats -rows修饰符控制显示输出
sql> set pagesize 180 SELECT /*+ gather_plan_statistics */ ename,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 DALLASFORD RESEARCH DALLASJOnes RESEARCH DALLASSCott RESEARCH DALLASSMITH RESEARCH DALLASsql> select * from table(dbms_xplan.display_cursor(null,'allstats -rows'));PLAN_table_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------sql_ID d2hh42yzqqjz7,child number 0-------------------------------------SELECT /*+ gather_plan_statistics */ ename,deptd WHERE e.deptno = d.deptno AND d.deptno=20 ORDER BY 1,3Plan 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.
总结
以上是内存溢出为你收集整理的执行计划display_cursor函数全部内容，希望文章能够帮你解决执行计划display_cursor函数所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）