리소스를 많이 차지 하는 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
'Oracle > Tunning' 카테고리의 다른 글
성능 개선 대상 sql 찾기 (튜닝의시작 튜닝방법론) (0) | 2017.08.17 |
---|