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

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

阅读更多
个人分类: oracle db
上一篇MongoDB的使用
下一篇单点登录 sso
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭