DBMS_XPLAN.DISPLAY_CURSOR()看执行计划

我们可以很容易的得到一个SQL的执行计划。

如果一个SQL已经执行过了，我们怎么查看他真实的执行计划呢。

如果知道已执行SQL的 SQL_ID，或HASH_VALUE.

在10g中利用DBMS_XPLAN.DISPLAY_CURSOR()可以很方便地查询到已执行SQL的执行计划。

SQL> desc dbms_xplan
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 
 虽然要求入参是SQL_ID。由于SQL_ID和HASH_VALUE本质上是一样的。

所以这里用 SQL_ID和HASH_VALUE都可以。
 
oracle官方文档中的部分说明：
DISPLAY_CURSOR Function
This table function displays the explain plan of any cursor loaded in the cursor cache.
In addition to the explain plan, various plan statistics (such as. I/O, memory and timing)
can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).

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

 

--1.执行一个SQL
SELECT /* TOTO */ ename, dname
FROM dept d join emp e USING (deptno);

 

--2.获取这个SQL的sql_id和 child_number

SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE '%TOTO%';

SQL_ID         CHILD_NUMBER
----------     -----------------------------
gwp663cqh5qbf   0

--3.根据sql_id就可以显示刚才那个SQL的执行计划
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));

 

--4.显示出的执行计划如下
Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0
--------------------------------------------------------
SELECT /* TOTO */ ename, dname
FROM dept d JOIN emp e USING (deptno);

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     7 (100)|          |
|   1 |  SORT GROUP BY      |      |     4 |    64 |     7  (43)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   224 |     6  (34)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    44 |     3  (34)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    70 |     3  (34)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."DEPTNO"="D"."DEPTNO")

--5.也可以与v$sql关联，直接查已执行sql的执行计划。个人还是喜欢第一种方法。

SELECT t.*
  FROM v$sql s,
       table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t
 WHERE sql_text LIKE '%TOTO%';