获得执行计划方法六-dbms_xplan.diskplay_cursor

当我们知道一个SQL语句的sql_id后，可以通过使用dbms_xplan.diskplay_cursor方法来获取执行计划。

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 CURSOR_CHILD_NO                NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT

SQL> set linesize 200
SQL> select sql_id
  2  from v$sql
  3  where sql_text like '%emp%'
  4  /

SQL_ID
-------------
fqxwyjvwrn1cb
dncctsm4cs86j
66zq72yq3r4pt
1gu8t96d0bdmu
1gu8t96d0bdmu

SQL>  select * from table(dbms_xplan.display_cursor('fqxwyjvwrn1cb',null,null)) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fqxwyjvwrn1cb, child number 0
-------------------------------------
select sql_id from v$sql where sql_text like '%emp%'

Plan hash value: 903671040

---------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |       |       |     1 (100)|
|*  1 |  FIXED TABLE FULL| X$KGLCURSOR_CHILD |     1 |   523 |     0   (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("KGLNAOBJ" LIKE '%emp%' AND

              "INST_ID"=USERENV('INSTANCE')))

还有与此类似的display_awr