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效率低下的原因，如统计信息的准确性、主要的开销位于那些步骤等