dbms_xplan之display_cursor函数的使用_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1690 | 回复: 0   主题: dbms_xplan之display_cursor函数的使用        下一篇 
小静
注册用户
等级:上等兵
经验:147
发帖:9
精华:0
注册:2012-3-6
状态:离线
发送短消息息给小静 加好友    发送短消息息给小静 发消息
发表于: IP:您无权察看 2015-7-22 15:57:55 | [全部帖] [楼主帖] 楼主

一、display_cursor函数用法
1、display_cursor函数语法

DBMS_XPLAN.DISPLAY_CURSOR(
sql_id        IN  VARCHAR2  DEFAULTNULL,
child_number  IN  NUMBER    DEFAULTNULL,
format        IN  VARCHAR2  DEFAULT'TYPICAL');


2、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新增的修饰符
                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                  

二、演示使用display_cursor函数获取执行计划    
        1、当前数据库版本以及加载执行计划到库缓存            

SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
SQL> SELECT ename,dname,loc
2  FROM   emp e, dept d
3  WHERE  e.deptno = d.deptno
4  AND    e.empno  = 7788;
ENAME      DNAME          LOC
---------- -------------- -------------
SCOTT      RESEARCH       DALLAS


  2、查看真实的执行计划   

/*

----------------不传递任何参数给display_cursor函数,显示当前会话最后一条SQL语句的执行计划-------------*/
SQL> select * fromtable(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                                             3 (100)
1    NESTED LOOPS                                1      63       3   (0)  00:00:01
2     TABLE ACCESS BYINDEX ROWID  EMP           1      33       2   (0)  00:00:01
*  3      INDEXUNIQUE SCAN           PK_EMP        1               1   (0)  00:00:01
4     TABLE ACCESS BYINDEX ROWID  DEPT        409   12270       1   (0)  00:00:01
*  5      INDEXUNIQUE SCAN           PK_DEPT       1               0   (0)
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."EMPNO"=7788)
5 - access("E"."DEPTNO"="D"."DEPTNO")
/*

------------------- 获得SQL语句的SQL_ID,可以看出此SQL_ID与上面显示的执行计划中的SQL_ID一致 ----------*/
SQL> select sql_id,address,plan_hash_value,hash_value,child_number from v$sql
2  where sql_text like'%SELECT ename%'and sql_text notlike'%from v$sql%';
SQL_ID        ADDRESS          PLAN_HASH_VALUE HASH_VALUE CHILD_NUMBER
------------- ---------------- --------------- ---------- ------------
a67wqmkfb9j65 0000000091DBFBC8      2385808155 2629092549            0
/*-------------- 传递SQL_ID以及format参数,并配合修饰符控制执行计划的输出 ------------------------*/
SQL> select * fromtable(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                                     3 (100)
1    NESTED LOOPS                               63       3   (0)  00:00:01
2     TABLE ACCESS BYINDEX ROWID  EMP          33       2   (0)  00:00:01
3      INDEXUNIQUE SCAN           PK_EMP                1   (0)  00:00:01
4     TABLE ACCESS BYINDEX ROWID  DEPT      12270       1   (0)  00:00:01
5      INDEXUNIQUE SCAN           PK_DEPT               0   (0)
--------------------------------------------------------------------------------


3、查看真实执行计划并获得统计信息
前提条件
设置参数statistics_level为all,可以基于session级别以及实例级别
或者启用gather_plan_statistics提示      

/*

-------------查看实例参数statistics_level的值,并在会话级别将其设定为all  ---------*/
SQL> show parameter statistics_le
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      ALL
SQL> alter session set statistics_level=all;
Session altered.
SQL> select e.ename,e.sal,s.grade
2  from emp e
3  join salgrade s
4  on e.sal between losal and hisal
5  and e.deptno = 20;
ENAME             SAL      GRADE
---------- ---------- ----------
SCOTT            3000          4
FORD             3000          4
JONES            2975          4
ADAMS            1100          1
SMITH             800          1
/*

------- 执行上述SQL语句后获得其真实的执行计划,使用了iostats last -predicate -note 修饰符控制显示输出 -----*/
SQL> set pagesize 0
SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'iostats last -predicate -note'));
SQL_ID  243b0tpjxj6wv, child number 0
-------------------------------------
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
-------------------------------------------------------------------------------------------
1    MERGE JOIN                            1        1        5  00:00:00.01        14
2     SORT JOIN                            1        5        5  00:00:00.01         7
3      TABLE ACCESS FULL   EMP             1        5        5  00:00:00.01         7
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
-------------------------------------------------------------------------------------------
/*---------------- 修改会话级别的参数statistics_level为typical并验证修改结果 ----------------*/
SQL> alter session set statistics_level=typical;
SQL> col name format a40
SQL> col value format a25
SQL> col display_value format a25
SQL> selectname, value, display_value, isses_modifiable
2  from v$parameter
3  where isses_modifiable = 'TRUE'
4  andnamelike'%&input_name%';
Enter value for input_name: statistics_level
old   4: andnamelike'%&input_name%'
new   4: andnamelike'%statistics_level%'
NAME                                     VALUE                     DISPLAY_VALUE             ISSES
---------------------------------------- ------------------------- ------------------------- -----
statistics_level                         TYPICAL                   TYPICAL                   TRUE
/*-------- 使用提示gather_plan_statistics,并获得其真实执行计划,使用了allstats -rows修饰符控制显示输出 ---*/
SQL> set pagesize 180
SQL> SELECT /*+ gather_plan_statistics */ ename,dname,loc
2  FROM   emp e, dept d
3  WHERE  e.deptno = d.deptno
4  AND    d.deptno=20 ORDERBY 1,2,3;
ENAME      DNAME          LOC
---------- -------------- -------------
ADAMS      RESEARCH       DALLAS
FORD       RESEARCH       DALLAS
JONES      RESEARCH       DALLAS
SCOTT      RESEARCH       DALLAS
SMITH      RESEARCH       DALLAS
SQL> select * fromtable(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 ORDERBY 1,2,3
Plan hash value: 3339094711
---------------------------------------------------------------------------------------------------------------------
Id    Operation                       Name      Starts   A-Rows     A-Time     Buffers    OMem    1Mem    O/1/M
---------------------------------------------------------------------------------------------------------------------
1    SORT ORDERBY                                 1        5  00:00:00.01         9    2048    2048       1/0/0
2     NESTED LOOPS                                 1        5  00:00:00.01         9
3      TABLE ACCESS BYINDEX ROWID  DEPT           1        1  00:00:00.01         2
*  4       INDEXUNIQUE SCAN           PK_DEPT        1        1  00:00:00.01         1
*  5      TABLE ACCESS FULL            EMP            1        5  00:00:00.01         7
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"=20)
5 - filter("E"."DEPTNO"=20)
Note
-----
- dynamic sampling used for this statement


三、总结
        1、与display函数不同,display_cursor显示的为真实的执行计划
        2、对于format参数,使用与display函数的各个值,同样适用于display_cursor函数
        3、当statistics_level为all或使用gather_plan_statistics提示可以获得执行时的统计信息
        4、根据真实与预估的统计信息可以初步判断SQL效率低下的原因,如统计信息的准确性、主要的开销位于那些步骤等       

--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论