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

栏目:eas cloud知识作者:金蝶来源:金蝶云社区发布:2024-09-22浏览:1

如何查看一条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/decode(executions_delta,0,1,executions_delta)/1000000)
        end  cpu_per_exec_sec,       
        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  ) ) )
        else trunc(d.cpu_time_delta/decode(executions_delta,0,1,executions_delta))
        end  cpu_per_exec_mssec,
        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.rows_processed_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.rows_processed_delta/decode(executions_delta,0,1,executions_delta))
        end rows_per_exec,
        trunc(d.cpu_time_delta/1000000) sec_total,
        plan_hash_value
        from dba_hist_sqlstat d,dba_hist_snapshot s
        where s.SNAP_ID=d.SNAP_ID
           and  s.INSTANCE_NUMBER=d.INSTANCE_NUMBER
        and sql_id='dx6t46nachfm8'
        order by 1

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

如果一条SQL加了索引 ,或者用sql profile固态了执行计划,想知道效率高了还是变得更低了,该怎么办呢?可以用以下语句查询,它显示最近...
点击下载文档
确认删除?
回到顶部
客服QQ
  • 客服QQ点击这里给我发消息