슬로우 쿼리 (slow query) 찾기

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

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));