执行计划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语句的真实的执行计划

 

?

1 2 3 4 5 6

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函数参数描述

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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、当前数据库版本以及加载执行计划到库缓存 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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语句的执行计划

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

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.<br><br><br>

3.获得SQL语句的SQL_ID，可以看出此SQL_ID与上面显示的执行计划中的SQL_ID一致

?

1 2 3 4 5 6

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参数，并配合修饰符控制执行计划的输出

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

/*--------------  ------------------------*/   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 修饰符控制显示输出

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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修饰符控制显示输出

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

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.