active session의 event 정보를 보여주는 sql script <wait_buffer.sql>

active session들의 현재 wait event와 sql 정보를 buffer get 기준으로 보여주는 sql script 입니다.
세션 모니터링의 기본이죠..

<내용>
set pagesize 250
col sid for 9999
col waiting_on for a90
col serial# for 999999
col prg for a19
col OraPid for 9999
col command for a9
col aa Heading "DB-User" for a8
col bb Heading "OS-Pid" for a7

set pau off
select to_char(sysdate,'MM/DD HH24:MI:SS') dat,s.sid SID,s.serial#,sql_hash_value,
decode(s.command,
        '0','NO',
        '1','Cr Tab',
        '2','Insert',
        '3','Select',
        '6','Update',
        '7','Delete',
        '9','Create Idx',
        '10','Drop Idx',
        '15','Alter Tbl',
        '24','Create Proc',
        '32','Create Link',
        '33','Drop Link',
        '36','Create RBS',
        '37','Alter RBS',
        '38','Drop RBS',
        '40','Alter TBS',
        '41','Drop TBS',
        '42','Alter Sess',
        '45','Rollback',
        '47','PL/SQL Exe',
        '62','Anal Table',
        '63','Anal Index',
        '85','Truncate') Command,
substr(s.machine,1,8)||'['|| substr(s.program, 1, 9)||']' prg,
round(q.buffer_gets/q.executions,2) getperexec,
substr(
rtrim(w.event) || ': ' ||
rtrim(p1text,' ') || ' ' || to_char(p1) || ',' ||
rtrim(p2text,' ') || ' ' || to_char(p2) || ',' ||
rtrim(p3text,' ') || ' ' || to_char(p3),1,75) ||',waiting:'||wait_time  as waiting_on
from  v$session s, v$session_wait w,v$sqlarea q
where w.wait_time = 0
and w.sid = s.sid
and s.sql_hash_value = q.hash_value
and event not like '%pmon timer%'
and event not like '%smon timer%'
and event not like '%rdbms ipc message%'
and event not like '%SQL*Net message%'
and event not like '%lock manager wait for%'
and event not like '%slave wait%'
and event not like '%io done%'
and event not like '%pipe get%'
and event not like '%wakeup time manager%'
and event not like '%queue messages%'
order by getperexec
/