如何查看一条SQL的历史执行计划

如果一条SQL加了索引 ，或者用sql profile固态了执行计划，想知道效率高了还是变得更低了，该怎么办呢？

可以用以下语句查询，它显示最近一段时间的执行时间，执行成本，执行次数

建议在plsql之类的工具中查询

select trunc(BEGIN_INTERVAL_TIME,'hh24') time_, s.instance_number   ,       
        case when executions_delta=0 and
       --sometimes executions are not counted when query is long and keeps running crossing the hour. It still addup buffer gest and other stats but not executions
        (trunc(BEGIN_INTERVAL_TIME,'hh24')  - lag(trunc(BEGIN_INTERVAL_TIME,'hh24') ,1) over (order by BEGIN_INTERVAL_TIME,s.instance_number))*24 = 1
        then
        trunc(sum(executions_delta)  over ( order by trunc(BEGIN_INTERVAL_TIME,'hh24') rows  1 PRECEDING  ))
           else trunc(executions_delta)
        end executions,
        executions_delta old_executions,
          case when executions_delta=0 and
        (trunc(BEGIN_INTERVAL_TIME,'hh24')  - lag(trunc(BEGIN_INTERVAL_TIME,'hh24') ,1) over (order by BEGIN_INTERVAL_TIME,s.instance_number,s.instance_number))*24 = 1
        then
        trunc(sum(buffer_gets_delta)  over ( order by trunc(BEGIN_INTERVAL_TIME,'hh24') rows  1 PRECEDING  ) / decode(sum(executions_delta)   over ( order by trunc(BEGIN_INTERVAL_TIME,'hh24') rows  1 PRECEDING  ),0,1, sum(executions_delta)   over ( order by trunc(BEGIN_INTERVAL_TIME,'hh24') rows  1 PRECEDING  ) ))
        else trunc(buffer_gets_delta/decode(executions_delta,0,1,executions_delta))
        end lio_per_exec,
        case when executions_delta=0 and
       --sometimes executions are not counted when query is long and keeps  running crossing the hour. It still addup buffer gest and other stats but not executions
        (trunc(BEGIN_INTERVAL_TIME,'hh24')  - lag(trunc(BEGIN_INTERVAL_TIME,'hh24') ,1) over (order by BEGIN_INTERVAL_TIME,s.instance_number))*24 = 1
        then
        trunc(sum(d.disk_reads_delta)  over ( order by trunc(BEGIN_INTERVAL_TIME,'hh24') rows  1 PRECEDING  ) / decode(sum(executions_delta)   over ( order by trunc(BEGIN_INTERVAL_TIME,'hh24') rows  1 PRECEDING  ),0,1, sum(executions_delta)   over ( order by trunc(BEGIN_INTERVAL_TIME,'hh24') rows  1 PRECEDING  ) ))
        else trunc(d.disk_reads_delta/decode(executions_delta,0,1,executions_delta))
        end pio_per_exec,
        case when executions_delta=0 and
       --sometimes executions are not counted when query is long and keeps running crossing the hour. It still addup buffer gest and other stats but not executions
        (trunc(BEGIN_INTERVAL_TIME,'hh24')  - lag(trunc(BEGIN_INTERVAL_TIME,'hh24') ,1) over (order by BEGIN_INTERVAL_TIME,s.instance_number))*24 = 1
        then
        trunc(sum(d.cpu_time_delta)  over ( order by trunc(BEGIN_INTERVAL_TIME,'hh24') rows  1 PRECEDING  ) / decode(sum(executions_delta)   over ( order by trunc(BEGIN_INTERVAL_TIME,'hh24') rows  1 PRECEDING  ),0,1, sum(executions_delta)   over ( order by trunc(BEGIN_INTERVAL_TIME,'hh24') rows  1 PRECEDING  ) )/1000000 )
        else trunc(d.cpu_time_delta/de