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 (
        instance_number NUMBER,
        event_name      VARCHAR2(64),
        waits           NUMBER,
        time_s          NUMBER,
        avg_ms          NUMBER,
        pct             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 db_time AS (
        SELECT DISTINCT INSTANCE_NUMBER,
          ROUND((MAX(VALUE) OVER (PARTITION BY DBID,INSTANCE_NUMBER) - MIN(VALUE) OVER (PARTITION BY DBID,INSTANCE_NUMBER))/1000000,0) db_t
        FROM STATS$SYS_TIME_MODEL,STATS$TIME_MODEL_STATNAME
        WHERE STATS$SYS_TIME_MODEL.stat_id=STATS$TIME_MODEL_STATNAME.stat_id and STATS$TIME_MODEL_STATNAME.STAT_NAME='DB time' AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id and INSTANCE_NUMBER=inst_number
        ),
        event_time AS (
        SELECT instance_number,event,waits,time_s,avg_ms,pos
        FROM (
          SELECT instance_number,event,waits,time_s,
            DECODE(waits, NULL, NULL, 0, NULL, ROUND((time_s/waits)*1000) ) avg_ms,
            ROW_NUMBER() OVER (PARTITION BY instance_number ORDER BY time_s desc) pos
          FROM (
            SELECT DISTINCT INSTANCE_NUMBER, EVENT,
              MAX(TOTAL_WAITS) OVER (PARTITION BY DBID,INSTANCE_NUMBER, EVENT) - MIN(TOTAL_WAITS) over (PARTITION BY DBID,INSTANCE_NUMBER, EVENT) waits,
              ROUND((MAX(TIME_WAITED_MICRO) OVER (PARTITION BY dbid,INSTANCE_NUMBER,EVENT) - MIN(TIME_WAITED_MICRO) OVER (PARTITION BY DBID,INSTANCE_NUMBER, EVENT))/1000000) time_s
            FROM STATS$SYSTEM_EVENT
            WHERE
            event_id not in (select event_id  from V$SYSTEM_event where wait_class_id=2723168908) and
            SNAP_ID BETWEEN begin_snap_id AND end_snap_id and INSTANCE_NUMBER=inst_number
            UNION
            SELECT DISTINCT INSTANCE_NUMBER, 'CPU time', NULL,
              ROUND((MAX(VALUE) OVER (PARTITION BY DBID,INSTANCE_NUMBER) - MIN(VALUE) OVER (PARTITION BY DBID, INSTANCE_NUMBER))/1000000)
            FROM STATS$SYS_TIME_MODEL,STATS$TIME_MODEL_STATNAME
            WHERE STATS$SYS_TIME_MODEL.stat_id=STATS$TIME_MODEL_STATNAME.stat_id and STATS$TIME_MODEL_STATNAME.STAT_NAME='DB CPU' AND SNAP_ID BETWEEN begin_snap_id AND end_snap_id and INSTANCE_NUMBER=inst_number
          )
        )
        WHERE pos<6
        )
        SELECT db_time.instance_number,event,nvl(waits,0),time_s,nvl(avg_ms,0),ROUND(time_s*100/db_t ,1) pct
        FROM db_time, event_time
        WHERE db_time.instance_number=event_time.instance_number
        ORDER BY db_time.instance_number,pos;
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(SNAP_TIME, 'DD-MON-YYYY HH24:MI') into v_end_t
        FROM STATS$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||rpad('Instance',9,' ');
        v_output:=v_output||lpad('Averge',76, ' ');
        v_output:=v_output||lpad('% Total',34,' ');
        DBMS_OUTPUT.PUT_LINE(v_output);
        v_output:='';
        v_output:=v_output||rpad('Number',9,' ');
        v_output:=v_output||lpad('Event Name',64,' ');
        v_output:=v_output||lpad('Waits',12,' ');
        v_output:=v_output||lpad('Time (s)', 12,' ');
        v_output:=v_output||lpad('Wait (ms)',12, ' ');
        v_output:=v_output||lpad('Call Time',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(to_char(v_stab(j).instance_number), 9,' ');
            v_output:=v_output||lpad(v_stab(j).event_name, 64,' ');

            IF v_stab(j).waits = 0 THEN
                v_output:=v_output||lpad(' ', 12,' ');
            ELSE
                v_output:=v_output||lpad(to_char(v_stab(j).waits,'999,999,999'), 12,' ');
            END IF;

            v_output:=v_output||lpad(to_char(v_stab(j).time_s), 10,' ');

            IF v_stab(j).avg_ms = 0 THEN
                v_output:=v_output||lpad(' ', 10,' ');
            ELSE
                 v_output:=v_output||lpad(to_char(v_stab(j).avg_ms), 10,' ');
            END IF;

            v_output:=v_output||lpad(to_char(v_stab(j).pct), 12,' ');
            DBMS_OUTPUT.PUT_LINE(v_output);
        END LOOP;

        IF c1%isopen THEN
                CLOSE c1;
        END IF;
    END LOOP;
END;
/