[Oracle] Active Session 모니터링

-- ###########################################################
-- ACTIVE SESSION 모니터링
--
-- ###########################################################
SELECT b.username
     , status
     , b.machine
     , b.osuser
     , b.module
     , b.sql_id
     , sql_child_number AS child
     , (SELECT spid FROM v$process WHERE addr = b.paddr) AS spid
     , b.sid
     , b.serial#
     , b.last_call_et
     , ROUND(RATIO_TO_REPORT(delta_cpu_time) OVER(),3) * 100 AS cpu
     , a.event
     , SUBSTR(s.sql_text,1,50) AS sql_text
     , ROUND(DECODE(delta_buffer_gets,0,1,DECODE(delta_buffer_gets,0,1,delta_buffer_gets))/delta_elapsed_time * 1000000) AS lr_per_sec  
     , ROUND(RATIO_TO_REPORT(delta_buffer_gets) OVER(),3) * 100 AS "lr_per (%)"
     , ROUND(DECODE(delta_disk_reads,0,1,DECODE(delta_disk_reads,0,1,delta_disk_reads))/delta_elapsed_time * 1000000) AS pr_per_sec     
     , ROUND(RATIO_TO_REPORT(delta_disk_reads) OVER(),3)  * 100 AS "pr_per (%)"
     , ROUND(DECODE(delta_execution_count,0,1,DECODE(delta_execution_count,0,1,delta_execution_count))/delta_elapsed_time * 1000000) AS exec_per_sec
     , c.plan_hash_value
     , DECODE(b.blocking_session,NULL,NULL,b.blocking_session||'('||b.blocking_instance||')') AS lock_sess
     , logon_time
     , s.first_load_time
     , s.last_active_time
     , 'alter system kill session ''' || a.sid || ',' || serial# || ''';' AS kill
  FROM v$session_wait a, v$session b, v$sqlstats c, v$sql s
 WHERE a.sid = b.sid 
   AND c.sql_id = s.sql_id
   AND NVL(b.sql_id,b.prev_sql_id) = c.sql_id
   AND NVL(b.sql_child_number,prev_child_number) = s.child_number
   AND b.status = 'ACTIVE'
   AND b.type   = 'USER'
--   AND b.sql_id = ''
--   AND b.sid    = 
 ORDER BY b.last_call_et DESC, cpu DESC
;