該函數(shù)的語(yǔ)法：
DBMS_XPLAN.DISPLAY_CURSOR(
?? sql_id??????? IN VARCHAR2 DEFAULT NULL,
?? child_number IN NUMBER??? DEFAULT NULL,
?? format??????? IN VARCHAR2 DEFAULT 'TYPICAL');

舉個(gè)例子 執(zhí)行一個(gè)簡(jiǎn)單查詢：
SQL> select count(0) from cat_product cp,cat_drug cd where cp.medical_id=cd.id;

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

如果我們想獲取該語(yǔ)句的實(shí)際執(zhí)行計(jì)劃，通過(guò)下列步驟：

1、查詢v$sql視圖，找到該語(yǔ)句的sql_id(注意喲，必須要確保你要查詢的sql語(yǔ)句還在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、調(diào)用dbms_xplan包，查看該語(yǔ)句執(zhí)行時(shí)的實(shí)現(xiàn)執(zhí)行計(jì)劃：
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???? |
-----------------------------------------------------------------------------------------------------------
......

事實(shí)上dbms_xplan.display_cursor也非常靈活，如果執(zhí)行的統(tǒng)計(jì)信息也被收集的話，還可以顯示出每一步實(shí)際的花費(fèi)時(shí)間等信息，例如：

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

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

--簡(jiǎn)單獲取，不需要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是優(yōu)化器評(píng)估的行數(shù),A-Rows是實(shí)際的行數(shù)，這個(gè)對(duì)于判斷優(yōu)化器的選擇正確與否比較有用！

總結(jié)

以上是生活随笔為你收集整理的dbms_xplan.display_cursor 获取执行过的sql的执行计划的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。