DISK IO를 많이 발생시키는 SQL 문장을 찾는 쿼리

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : DISK IO를 많이 발생시키는 SQL 문장을 찾는 QUERY

다음은 V$SQLAREA table을 사용하여 library cache에서 공유되고 있는 SQL statement 중 Disk read를 많이 유발시키는 문장들을 찾는 Query임

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

set echo on; DEFINE blocks_read = 1000 (NUMBER) COLUMN parsing_user_id FORMAT 9999999 HEADING 'User Id' COLUMN executions FORMAT 9999 HEADING 'Exec' COLUMN sorts FORMAT 99999 HEADING 'Sorts' COLUMN buffer_gets FORMAT 999,999,999 HEADING 'Buffer Gets' COLUMN disk_reads FORMAT 999,999,999 HEADING 'Block Reads' COLUMN sql_text FORMAT a40 HEADING 'Statement' WORD_WRAPPED SET LINES 130    SELECT parsing_user_id, executions, sorts, buffer_gets, disk_reads, sql_text FROM v$sqlarea WHERE disk_reads > &&blocks_read  ORDER BY disk_reads;   set echo off; SET LINES 80

위의 문장에서 일정 횟수 이상의 실행이 되었던 문장을 찾는 경우에는 

아래의 Query가 사용 될 수 있음

set echo on;
DEFINE blocks_read = 1000 (NUMBER) 
DEFINE executions = 100 (NUMBER)
COLUMN parsing_user_id FORMAT 9999999 HEADING 'User Id'
COLUMN executions FORMAT 9999 HEADING 'Exec'
COLUMN sorts FORMAT 99999 HEADING 'Sorts'
COLUMN buffer_gets FORMAT 999,999,999 HEADING 'Buffer Gets'
COLUMN disk_reads FORMAT 999,999,999 HEADING 'Block Reads'
COLUMN sql_text FORMAT a40 HEADING 'Statement' WORD_WRAPPED
SET LINES 130 
 
SELECT parsing_user_id, executions, sorts / executions, buffer_gets / 
executions, disk_reads / executions, sql_text
FROM v$sqlarea
WHERE disk_reads > &&blocks_read and executions > &&executions
ORDER BY disk_reads;
 
set echo off;
SET LINES 80

결과값 : 

+직접 쿼리를 입력하지 않고 AWR Report로도 확인가능함

참조 : http://dbtech.co.kr/bbs/?bo_c=1030&bo_v=443