dbms_xplan.display_cursor 获取执行过的sql的执行计划

该函数的语法：
DBMS_XPLAN.DISPLAY_CURSOR(
   sql_id        IN VARCHAR2 DEFAULT NULL,
   child_number IN NUMBER    DEFAULT NULL,
   format        IN VARCHAR2 DEFAULT 'TYPICAL');

举个例子 执行一个简单查询：
SQL> select count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id;

COUNT(0)
----------
    118908

如果我们想获取该语句的实际执行计划，通过下列步骤：

1、查询v$sql视图，找到该语句的sql_id(注意哟，必须要确保你要查询的sql语句还在shared pool)：
SQL> select sql_id from v$sql where sql_text=
2 'select count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id';

SQL_ID
-------------
c9cxqvr3q4tjd

2、调用dbms_xplan包，查看该语句执行时的实现执行计划：
SQL> select * from table(dbms_xplan.display_cursor('c9cxqvr3q4tjd'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c9cxqvr3q4tjd, child number 0
-------------------------------------
select count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id

Plan hash value: 2559475106

-----------------------------------------------------------------------------------------------------------
| Id | Operation              | Name                     | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
......

事实上dbms_xplan.display_cursor也非常灵活，如果执行的统计信息也被收集的话，还可以显示出每一步实际的花费时间等信息，例如：

SQL> select /*+gather_plan_statistics*/ count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id;

COUNT(0)
----------
    118908

--简单获取，不需要sqlid

select  *  from   table ( dbms_xplan . display_cursor ( null , null , ' ALLSTATS LAST ' )) ;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 91w1ug6vc9pxh, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id

Plan hash value: 2559475106

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation              | Name                     | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
...

E-Rows是优化器评估的行数,A-Rows是实际的行数，这个对于判断优化器的选择正确与否比较有用！