查看SQL语句的执行计划(explain plan for && Cursor Cache)

Posted by Harid七月 - 17 - 2014 Leave comments

执行计划的选择,对于一条SQL语句的执行效率来说极其重要。因此,我们常常需要去查看某条语句的执行计划。本文介绍两种查看执行计划的方法(众多中的两种而已,因为这两种我在这两天写的shell脚本中使用到了)。

1、使用explain plan for来预估某条语句的执行计划

如执行:

1
EXPLAIN plan FOR UPDATE test.WFE_MOVE_STATISTICS SET STATUS=:1,PROCESSCOUNT =:2, ENDTIME=:3 WHERE HISTORY_ID=:4;

sys@rcpbdb> explain plan for UPDATE test.WFE_MOVE_STATISTICS SET STATUS=:1,PROCESSCOUNT =:2, ENDTIME=:3 WHERE HISTORY_ID=:4;

Explained.

然后调用“dbms_xplan.display()”来查看刚刚的执行计划:

1
SELECT plan_table_output FROM TABLE(dbms_xplan.display());

执行结果即为刚刚那条update语句的执行计划:

explainplan

2、查询Cursor Cache中的某条语句的执行计划

dbms_xplan包有很多的方法查看各种执行计划,具体可参考Oracle官方文档:传送门>>。此处使用dbms_xplan.display_cursor这一方法。

Overview

The DBMS_XPLAN package supplies five table functions:

DISPLAY – to format and display the contents of a plan table.

DISPLAY_AWR – to format and display the contents of the execution plan of a stored SQL statement in the AWR.

DISPLAY_CURSOR – to format and display the contents of the execution plan of any loaded cursor.

DISPLAY_SQL_PLAN_BASELINE – to display one or more execution plans for the SQL statement identified by SQL handle

DISPLAY_SQLSET – to format and display the contents of the execution plan of statements stored in a SQL tuning set.

使用dbms_xplan.display_cursor,需要预先知道SQL语句的SQL_ID,执行:

1
SELECT * FROM TABLE(dbms_xplan.display_cursor('bxqjrhwmanr4u'));

explainplan2

   声明:本文采用 BY-NC-SA 协议进行授权 | 星期九
   原创文章转载请注明:转自《查看SQL语句的执行计划(explain plan for && Cursor Cache)


分享按钮