dbms_xplan之display_cursor函数的使用_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 1933 | 回复: 0

主题： dbms_xplan之display_cursor函数的使用

小静

注册用户

等级：上等兵
经验：147
发帖：9
精华：0
注册：2012-3-6
状态：离线
发送短消息息给小静

加好友发送短消息息给小静

发消息

发表于：

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效率低下的原因，如统计信息的准确性、主要的开销位于那些步骤等

--转自

本版精华
热门帖子

操作引用/回复

总帖数

每页帖数

1/1页

返回列表

用户登录

Weblogic中间件技术论坛

Tuxedo中间件技术论坛

数据库论坛

Java论坛

Linux/unix论坛

网站地图