슬로우 쿼리 (slow query) 찾기

Posted by

아래 쿼리문을 실행 해 보자.

1.  SQL 문을 buffer gets per execution 값으로 정렬

SELECT username,
buffer_gets,
disk_reads,
executions,
buffer_get_per_exec,
parse_calls,
sorts,
rows_processed,
hit_ratio,
module,
sql_text -- elapsed_time, cpu_time, user_io_wait_time, ,
FROM (SELECT sql_text,
b.username,
a.disk_reads,
a.buffer_gets,
trunc(a.buffer_gets / a.executions) buffer_get_per_exec,
a.parse_calls,
a.sorts,
a.executions,
a.rows_processed,
100 - ROUND (100 * a.disk_reads / a.buffer_gets, 2) hit_ratio, module
-- cpu_time, elapsed_time, user_io_wait_time
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id
AND b.username NOT IN ('SYS', 'SYSTEM', 'RMAN','SYSMAN')
AND a.buffer_gets > 10000
ORDER BY buffer_get_per_exec DESC)
WHERE ROWNUM <= 20

2.  아래 쿼리를 먼저 실행 후

SELECT * FROM
(SELECT
sql_fulltext,
sql_id,
elapsed_time,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10

sql_id 와 child 를 입력하여 상세하게 분석 한다.

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child));

Leave a Reply

이메일 주소는 공개되지 않습니다. 필수 항목은 *(으)로 표시합니다