set lines 333 

set pages 1000

col shijian for a12

col execu_d for 9999999

col bg_d for 9999999999

col dr_d for 9999999999

col et_d for 99999999

col ct_d for 99999999

col io_time for 999999

col clus_time for 999999

col ap_time for 999999

col cc_time for 999999

col et_onetime for 999999

select to_char(b.END_INTERVAL_TIME,'yyyymmddhh24') shijian,

    a.instance_number,

    a.sql_id,

    a.plan_hash_value,

    sum(a.EXECUTIONS_DELTA) execu_d,

    sum(a.BUFFER_GETS_DELTA ) bg_d,

    sum(a.DISK_READS_DELTA ) dr_d,

    sum(a.ELAPSED_TIME_DELTA/1000000) et_d,

    sum(a.CPU_TIME_DELTA/1000000)  ct_d,

    sum(IOWAIT_DELTA/1000000) io_time,

    sum(CLWAIT_DELTA/1000000) clus_time,

    sum(APWAIT_DELTA/1000000) ap_time,

    sum(ccwait_delta/1000000) cc_time,

decode(sum(a.EXECUTIONS_DELTA),0,sum(a.ELAPSED_TIME_DELTA/1000000),sum(a.ELAPSED_TIME_DELTA/1000000)/sum(a.EXECUTIONS_DELTA)) et_onetime

from dba_hist_sqlstat a , dba_hist_snapshot b

where a.SNAP_ID =b.SNAP_ID

and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER

and a.sql_id='&sql_id'  
and b.END_INTERVAL_TIME > sysdate -7

group by a.sql_id,a.instance_number,to_char(b.END_INTERVAL_TIME,'yyyymmddhh24'),a.plan_hash_value

order by 1,2;


exec