과도한 I/O 유발 쿼리 찾기

col program for a20
col username for a10
col first_load_time  for a20
col module format a20
col sql_id format a16
col machine for a20;

 

 select /* ordered use_nl(a b) */ 
         b.sql_Id, substr(a.program,1,20) program, substr(b.module,1,20) module, a.machine,
         username, B.executions, buffer_gets, disk_reads,ROWS_PROCESSED,
         trunc(buffer_gets/DECODE(nvl(executions,0),0,1,executions) ) b_e,
         trunc((buffer_gets+disk_reads)/DECODE(nvl(executions,0),0,1,executions) ) b_d_e,
         trunc(buffer_gets/DECODE(nvl(ROWS_PROCESSED,0),0,1,ROWS_PROCESSED) ) b_r,
         first_load_time        
            from Gv$session a, Gv$sqlarea b
            where  a.status = 'ACTIVE'
             and  username not in ('SYS','SYSTEM')
             and a.sql_id=b.sql_id
             and a.inst_id = '1' and b.inst_id = a.inst_id
order by b_d_e;