set termout off feedback off verify off linesize 200

define v_inst_number="&1"

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 (
        inst_id NUMBER,
        event      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) IS
        WITH db_time AS (
        SELECT DISTINCT INST_ID,
          ROUND((MAX(VALUE) OVER (PARTITION BY (SELECT DBID FROM V$DATABASE),INST_ID))/1000000,0) db_t
        FROM gv$SYS_TIME_MODEL
        WHERE STAT_NAME='DB time' AND INST_ID=inst_number
        ),
        event_time AS (
        SELECT inst_id,event,waits,time_s,avg_ms,pos
        FROM (
          SELECT inst_id,event,waits,time_s,
            DECODE(waits, NULL, NULL, 0, NULL, ROUND((time_s/waits)*1000) ) avg_ms,
            ROW_NUMBER() OVER (PARTITION BY inst_id ORDER BY time_s desc) pos
          FROM (
            SELECT DISTINCT INST_ID, EVENT,
              MAX(TOTAL_WAITS) OVER (PARTITION BY (select DBID from v$database),INST_ID, EVENT) waits,
              ROUND((MAX(TIME_WAITED_MICRO) OVER (PARTITION BY (select dbid from v$database),INST_ID,EVENT))/1000000) time_s
            FROM gv$SYSTEM_EVENT
            WHERE WAIT_CLASS<>'Idle'  and INST_ID=inst_number
            UNION
            SELECT DISTINCT INST_ID, 'CPU time', NULL,
              ROUND((MAX(VALUE) OVER (PARTITION BY (select DBID from v$database),inst_id))/1000000)
            FROM gv$SYS_TIME_MODEL
            WHERE STAT_NAME='DB CPU' and inst_id=inst_number
          )
        )
        WHERE pos<6
        )
        SELECT db_time.inst_id,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.inst_id=event_time.inst_id
        ORDER BY db_time.inst_id,pos;
BEGIN


        DBMS_OUTPUT.PUT_LINE('');

        IF c1%isopen THEN
                CLOSE c1;
        END IF;
        OPEN c1(&v_inst_number);
        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).inst_id), 9,' ');
            v_output:=v_output||lpad(v_stab(j).event, 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;
/