set termout off feedback off verify off linesize 200 define v_inst_number="&1" define v_begin_snap_id="&2" define v_end_snap_id="&3" set termout on serveroutput on size 1000000 format wrapped DECLARE v_low NUMBER; v_high NUMBER; v_end_t VARCHAR2(30); v_output VARCHAR2(200); type srec is record ( sql_id VARCHAR2(13), plan_hash_value NUMBER, elapsed_seconds NUMBER, cpu_seconds NUMBER, rows_processed NUMBER, buffer_gets NUMBER, disk_reads NUMBER, executions NUMBER, parses NUMBER ); type stab is table of srec index by pls_integer; v_stab stab; CURSOR c1 (inst_number NUMBER, begin_snap_id NUMBER, end_snap_id NUMBER) IS WITH sql_stats_delta AS ( SELECT SNAP_ID, SQL_ID, PLAN_HASH_VALUE, ELAPSED_TIME_DELTA, CPU_TIME_DELTA, ROWS_PROCESSED_DELTA, BUFFER_GETS_DELTA, DISK_READS_DELTA, EXECUTIONS_DELTA, PARSE_CALLS_DELTA FROM DBA_HIST_SQLSTAT WHERE INSTANCE_NUMBER=inst_number AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id ), sql_stats_delta_rollup AS ( SELECT sql_id, plan_hash_value, ROUND(SUM(elapsed_time_delta) /1000000) elapsed_seconds, ROUND(SUM(cpu_time_delta) /1000000) cpu_seconds, DENSE_RANK() OVER (ORDER BY SUM(cpu_time_delta) DESC) rank_by_cpu_seconds, SUM(rows_processed_delta) rows_processed, SUM(buffer_gets_delta) buffer_gets, SUM(disk_reads_delta) disk_reads, SUM(executions_delta) executions, SUM(parse_calls_delta) parses FROM sql_stats_delta GROUP BY sql_id,plan_hash_value ) SELECT sql_id,plan_hash_value,elapsed_seconds,cpu_seconds,rows_processed,buffer_gets,disk_reads,executions,parses FROM sql_stats_delta_rollup WHERE rank_by_cpu_seconds <= 10 ORDER BY rank_by_cpu_seconds; BEGIN FOR i IN &v_begin_snap_id...&v_end_snap_id LOOP v_low:=i; v_high:=i+1; IF v_high > &v_end_snap_id THEN EXIT; END IF; DBMS_OUTPUT.PUT_LINE(''); SELECT TO_CHAR(END_INTERVAL_TIME, 'DD-MON-YYYY HH24:MI') into v_end_t FROM DBA_HIST_SNAPSHOT WHERE INSTANCE_NUMBER=&v_inst_number AND SNAP_ID=v_high; DBMS_OUTPUT.PUT_LINE('Begin snapshot id='||v_low||' End snapshot id='||v_high||' End snapshot time='||v_end_t); IF c1%isopen THEN CLOSE c1; END IF; OPEN c1(&v_inst_number, v_low, v_high); FETCH c1 bulk collect into v_stab; v_output:=''; v_output:=v_output||lpad('Elapsed', 43,' '); v_output:=v_output||lpad('CPU', 10,' '); DBMS_OUTPUT.PUT_LINE(v_output); v_output:=''; v_output:=v_output||rpad('Sql Id',15,' '); v_output:=v_output||lpad('Plan Hash Value',16,' '); v_output:=v_output||lpad('Seconds',12,' '); v_output:=v_output||lpad('Seconds', 12,' '); v_output:=v_output||lpad('Rows',13,' '); v_output:=v_output||lpad('Buffer Gets',15,' '); v_output:=v_output||lpad('Disk Reads',15,' '); v_output:=v_output||lpad('Executions', 12,' '); v_output:=v_output||lpad('Parses', 12,' '); DBMS_OUTPUT.PUT_LINE(v_output); DBMS_OUTPUT.PUT_LINE(rpad('-',125,'-')); FOR j IN 1..v_stab.count LOOP v_output:=''; v_output:=v_output||rpad(v_stab(j).sql_id, 15,' '); v_output:=v_output||to_char(v_stab(j).plan_hash_value, 999999999999); v_output:=v_output||to_char(v_stab(j).elapsed_seconds ,'999,999,999'); v_output:=v_output||to_char(v_stab(j).cpu_seconds , '999,999,999'); v_output:=v_output||to_char(v_stab(j).rows_processed , '999,999,999,999'); v_output:=v_output||to_char(v_stab(j).buffer_gets , '999,999,999,999'); v_output:=v_output||to_char(v_stab(j).disk_reads , '999,999,999,999'); v_output:=v_output||to_char(v_stab(j).executions , '999,999,999'); v_output:=v_output||to_char(v_stab(j).parses , '999,999,999'); DBMS_OUTPUT.PUT_LINE(v_output); END LOOP; IF c1%isopen THEN CLOSE c1; END IF; END LOOP; END; /