--yong.zhao
--查询SQL历史执行性能消耗情况，默认sysdate-7，可修改
select 'GV$' flag,
       0 snap_id,
       inst_id,
       plan_hash_value phv,
       executions execs,
       disk_reads reads,
       disk_reads / decode(executions, NULL, 1, 0, 1, executions) reads_per,
       buffer_gets gets,
       buffer_gets / decode(executions, NULL, 1, 0, 1, executions) gets_per,
       rows_processed,
       rows_processed / decode(executions, NULL, 1, 0, 1, executions) rows_per,
       elapsed_time/1000 elap_ms,
       (elapsed_time/1000) / decode(executions, NULL, 1, 0, 1, executions) elap_per_ms
  from gv$sql
 where sql_id='&sql_id'
 union all
select to_char(sht.begin_interval_time,'dd hh24:mi')||'--'||to_char(sht.end_interval_time,'hh24:mi') flag,
               sta.snap_id,
               sta.instance_number inst,
               sta.plan_hash_value phv,
               sta.executions_delta execs,
               sta.disk_reads_delta reads,
               sta.disk_reads_delta /
               decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) reads_per,
               sta.buffer_gets_delta gets,
               sta.buffer_gets_delta /
               decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) gets_per,
               sta.rows_processed_delta,
               sta.rows_processed_delta /
               decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) rows_per,
               sta.elapsed_time_delta/1000 elap_ms,
               (sta.elapsed_time_delta/1000) /
               decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) elap_per_ms
          from dba_hist_sqlstat sta,dba_hist_snapshot sht
         where 1=1
            and sta.instance_number=sht.instance_number
            and sta.snap_id=sht.snap_id
            and sht.begin_interval_time>= sysdate-7
            and sta.sql_id='&sql_id'
 order by 1,2;