分类导航

Oracle 发布时间：2022-05-17 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了执行计划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,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,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 @R_801_5545@,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,'iostats last -preDicate -note'));
sql_ID	243b0tpjxj6wv,child number 1
-------------------------------------
SELEct @R_801_5545@,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,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,'allstats -rows'));

PLAN_table_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sql_ID	d2hh42yzqqjz7,child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ ename,dept
d WHERE  e.deptno = d.deptno AND    d.deptno=20 ORDER BY 1,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.

大佬总结

以上是大佬教程为你收集整理的执行计划display_cursor函数全部内容，希望文章能够帮你解决执行计划display_cursor函数所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。

标签：

上一篇: DG：windows密码文件下一篇:[INS-40996] Installer has dete...

猜你在找的Oracle相关文章

Oracle 闪回找回数据的实现方法 2022-05-17
Oracle PL/SQL中异常高级特性示例解析 2019-11-05
Oracle中触发器示例详解 2019-11-05
Oracle游标的使用实例详解 2019-11-05
oracle net manager 数据传输安全步骤详解 2019-11-05
ORACLE检查找出损坏索引(Corrupt Indexes)的方法详解 2019-11-05
ORACLE中段的HEADER_BLOCK示例详析 2019-11-05
修改oracle密码有效期限制的两种思路详解 2019-11-05
修改ORACLE数据库密码有效期的方法 2019-11-05
plsql 登录 oracle11 12514 错误的处理方法 2019-11-05