dbms_xplan.display_cursor查看已执行SQL的执行计划（10g后） - 数据库编程

dbms_xplan.display_cursor 查看已执行SQL的执行计划（10g后）
参数介绍： sql_id 指定位于library cache执行计划中SQL父游标。 child_number 默认是0，如果是null,则返回sql_id所指父游标下的所有子游标的执行计划。 format 控制SQL语句执行计划的输出部分，即哪些可以显示哪些不显示。

对于执行过的sql： 1.普通的查看执行计划 select * from table(dbms_xplan.display_cursor('fb8szhn9h5r95')); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | COUNT STOPKEY | | | | | | | 2 | TABLE ACCESS FULL| T1 | 25 | 54400 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- 2.对于有实时执行信息的SQL 除此之外当在开启statistics_level=all时或使用gather_plan_statistics提示时，会用另外一个表，详细地记录运行时执行计划与信息。 alter session set statistics_level=all; --也可以alter system select /*+ gather_plan_statistics */ /*fwy1806*/* from t1 where rownum<30; 详细地查看执行计划 select * from table(dbms_xplan.display_cursor('fb8szhn9h5r95',null,'memstats')); --------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | | 50 |00:00:00.01 | |* 1 | COUNT STOPKEY | | 2 | | 50 |00:00:00.01 | | 2 | TABLE ACCESS FULL| T1 | 2 | 25 | 50 |00:00:00.01 | --------------------------------------------------------------------------- select * from table(dbms_xplan.display_cursor('fb8szhn9h5r95',null,'allstats last')); ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 25 |00:00:00.01 | 3 | 2 | |* 1 | COUNT STOPKEY | | 1 | | 25 |00:00:00.01 | 3 | 2 | | 2 | TABLE ACCESS FULL| T1 | 1 | 25 | 25 |00:00:00.01 | 3 | 2 | ---------------------------------------------------------------------------------------------- 详细查看该游标最后一次的执行计划 select * from table(dbms_xplan.display_cursor('fb8szhn9h5r95',null,'allstats')); ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | | 50 |00:00:00.01 | 6 | 2 | |* 1 | COUNT STOPKEY | | 2 | | 50 |00:00:00.01 | 6 | 2 | | 2 | TABLE ACCESS FULL| T1 | 2 | 25 | 50 |00:00:00.01 | 6 | 2 | ---------------------------------------------------------------------------------------------- 会将该游标的累积执行信息列出，例如游标执行过两次后，starts，A-Rows,buffers也是上面的两倍 select * from table(dbms_xplan.display_cursor('fb8szhn9h5r95',null,'all iostats last'));--最详细，如果没有物理读就没有READ字段 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 25 |00:00:00.01 | 3 | 2 | |* 1 | COUNT STOPKEY | | 1 | | | | | 25 |00:00:00.01 | 3 | 2 | | 2 | TABLE ACCESS FULL| T1 | 1 | 25 | 54400 | 3 (0)| 00:00:01 | 25 |00:00:00.01 | 3 | 2 | ------------------------------------------------------------------------------------------------------------------------------ select * from table(dbms_xplan.display_cursor('fb8szhn9h5r95',null,'all last')); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | COUNT STOPKEY | | | | | | | 2 | TABLE ACCESS FULL| T1 | 25 | 54400 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Starts为该sql执行的次数。 E-Rows为执行计划预计的行数。 A-Rows为实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。 A-Time为每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。 Buffers为每一步实际执行的逻辑读或一致性读。 Reads为物理读。 OMem、1Mem为执行所需的内存评估值，0Mem为最优执行模式所需内存的评估值，1Mem为one-pass模式所需内存的评估值。 0/1/M 为最优/one-pass/multipass执行的次数。查找低效执行计划： 1.比较A-Rows/Starts跟E-Rows，如果两值差别悬殊，则该行是低效执行计划。 2.查看Buffers/A-rows的比率，即返回一行平均消耗多少逻辑读 Buffers/A-rows<5 表示访问路径不错 Buffers/A-rows between 10 and 15，表示访问路径可以接受 Buffers/A-rows>15or20，表示路径不好，该行是低效执行计划，可以优化 iostats 控制I/O统计的显示 last 默认，显示所有执行计算过的统计。如果指定该值，则只显示最后一次执行的统计信息 memstats 控制pga相关统计的显示 allstats 此为iostats memstats的快捷方式，即allstats包含了iostats和memstats 三、总结 1、与display函数不同，display_cursor显示的为真实的执行计划 2、对于format参数，使用与display函数的各个值，同样适用于display_cursor函数 3、当statistics_level为all或使用gather_plan_statistics提示可以获得执行时的统计信息 4、根据真实与预估的统计信息可以初步判断SQL效率低下的原因，如统计信息的准确性、主要的开销位于那些步骤等