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

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

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


발췌 : OTN Discussion Forums