[펌] (공개)리소스를 많이 차지하는 SQL 찾기

    
/**    리소스를 많이 차지 하는 sql찾기  **/ 

1) 총 메모리 사용량이 많은 SQL (상위 N개)
SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT
FROM (SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT
      FROM V$SQLAREA
      ORDER BY BUFFER_GETS DESC  )
WHERE ROWNUM <= :p_rank

2) 평균 메모리 사용량이 많은 SQL (상위 N개)
SELECT BUFFER_GETS,DISK_READS,
       EXECUTIONS,trunc(BUFFER_PER_EXEC) BUFFER_PER_EXEC, address, SQL_TEXT
FROM ( SELECT BUFFER_GETS,DISK_READS, EXECUTIONS,address,
   BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) BUFFER_PER_EXEC,
   SQL_TEXT
       FROM   V$SQLAREA
       ORDER BY BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC )
WHERE ROWNUM <=  50

3) 메모리, 디스크 사용량이 일정 기준치를 넘은 SQL
SELECT BUFFER_GETS,DISK_READS,
       EXECUTIONS,BUFFER_GETS/EXECUTIONS,SQL_TEXT
FROM   V$SQLAREA
WHERE  BUFFER_GETS >  :p_val1
OR     DISK_READS > :p_val2
OR     EXECUTIONS > :p_val3

from en-core

白面書生
 

4) 디스크 사용량 많은 SQL
SELECT BUFFER_GETS,DISK_READS,
       EXECUTIONS,trunc(BUFFER_PER_EXEC) BUFFER_PER_EXEC, address, SQL_TEXT
FROM ( SELECT BUFFER_GETS,DISK_READS, EXECUTIONS,address,
   BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) BUFFER_PER_EXEC,
   SQL_TEXT
       FROM   V$SQLAREA
       ORDER BY disk_reads DESC )
WHERE ROWNUM <=  50

-- address 에 해당하는 sql찾기
select *
from v$sqltext
where address = 'B443F724'
order by piece;

from zerobug