오라클 slow query 찾기..

국내포스팅된 글을 찾다가 해외에서 여러가지 방법이 있다는걸 알았다.. 운영서버에서는 아직 돌려보지는 않았
지만 테스트서버에서는 잘돌아가더구만..

방법1. 아래 쿼리 실행

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-1. 아래 쿼리 실행

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

방법2-2. 아래 쿼리 실행 위에 나온쿼리중 sql_id,child_reads번호를 가지고 재조회

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR($SQL_ID, $CHILD_READS));