[DB튜닝]AWR을 이용한 튜닝 대상 SQL 추출

SQL 튜닝을 위한 대상 선정을 하기 위하여 AWR을 이용하여

다음의 스크립트를 활용하여 추출할 수 있다.

추출조건(실행횟수는 제외)은 각 시스템의 규모나 성능에 맞도록 조정하여 사용하시길...

-------------------------------------------------------------------------------
1. 소요시간 2초 이상 OR

   읽은 블럭 10,000블럭 이상 SQL 추출(SQL_ID 중복없이 추출)

-------------------------------------------------------------------------------
SELECT 
       A.EXECUTIONS_DELTA,
       ROUND(NVL(A.ROWS_PROCESSED_DELTA,0) /

          DECODE(A.EXECUTIONS_DELTA,0,1,A.EXECUTIONS_DELTA),0) AS AVG_ROWS_PROCESSED,
       ROUND(NVL(A.ELAPSED_TIME_DELTA,0)   /

          DECODE(A.EXECUTIONS_DELTA,0,1,A.EXECUTIONS_DELTA)/1000000,2) AS AVG_ELAPSED,
       ROUND(NVL(A.BUFFER_GETS_DELTA,0)    /

          DECODE(A.EXECUTIONS_DELTA,0,1,A.EXECUTIONS_DELTA),0)        AS AVG_BUFFER_GETS,
       A.MODULE,
       A.ACTION,
       A.SQL_ID,
       A.PARSING_SCHEMA_NAME         AS USERNAME,
       B.SQL_TEXT                    AS SQL_TEXT,
       TO_CHAR(C.BEGIN_INTERVAL_TIME,'YYYY.MM.DD HH24:MI:SS')  AS SQL_BEGIN_SNAP_TIME,
       TO_CHAR(C.END_INTERVAL_TIME,'YYYY.MM.DD HH24:MI:SS')    AS SQL_END_SNAP_TIME,
       A.INSTANCE_NUMBER             AS INST_NO,
       A.SNAP_ID,
       A.PLAN_HASH_VALUE,
       A.BUFFER_GETS_DELTA,
       A.VERSION_COUNT,
       A.OPTIMIZER_MODE,
       A.ELAPSED_TIME_DELTA,
       A.ROWS_PROCESSED_DELTA
  FROM DBA_HIST_SQLSTAT  A,
       DBA_HIST_SQLTEXT  B,
       DBA_HIST_SNAPSHOT C
 WHERE A.SQL_ID          = B.SQL_ID(+)
   AND A.DBID            = B.DBID(+)
   AND A.SNAP_ID         = C.SNAP_ID
   AND A.DBID            = C.DBID
   AND A.INSTANCE_NUMBER = C.INSTANCE_NUMBER
   AND C.BEGIN_INTERVAL_TIME BETWEEN TO_DATE('20100405090000','YYYYMMDDHH24MISS')

                                 AND TO_DATE('20100406235959','YYYYMMDDHH24MISS') 
   AND (A.SNAP_ID,A.BUFFER_GETS_DELTA,A.EXECUTIONS_DELTA,A.ELAPSED_TIME_DELTA)
        = (SELECT MAX(A.SNAP_ID),

                  MAX(BUFFER_GETS_DELTA),

                  MAX(EXECUTIONS_DELTA),

                  MAX(ELAPSED_TIME_DELTA)
             FROM DBA_HIST_SQLSTAT

            WHERE A.DBID            = B.DBID

              AND A.INSTANCE_NUMBER = INSTANCE_NUMBER

              AND A.SQL_ID = SQL_ID)
   AND (A.ELAPSED_TIME_DELTA   >= 2000000 *

          DECODE(A.EXECUTIONS_DELTA, NULL, 1, 0, 1, A.EXECUTIONS_DELTA) OR
        A.BUFFER_GETS_DELTA    >=   10000 *

          DECODE(A.EXECUTIONS_DELTA, NULL, 1, 0, 1, A.EXECUTIONS_DELTA))
   AND A.PARSING_SCHEMA_NAME  = '사용자';

 

-------------------------------------------------------------------------------
2. BIND 변수 값 추출
-------------------------------------------------------------------------------

SELECT /*+ USE_NL(A B C) */
       B.SNAP_ID,
       B.DBID,
       B.INSTANCE_NUMBER,
       B.SQL_ID,
       B.POSITION,
       B.NAME,
       B.VALUE_STRING,
       B.DATATYPE_STRING,
       B.LAST_CAPTURED,
       A.MODULE,
       C.BEGIN_INTERVAL_TIME,
       C.END_INTERVAL_TIME
  FROM DBA_HIST_SQLSTAT  A,
       DBA_HIST_SQLBIND  B,
       DBA_HIST_SNAPSHOT C
 WHERE A.DBID               = B.DBID
   AND A.SNAP_ID            = B.SNAP_ID
   AND A.INSTANCE_NUMBER    = B.INSTANCE_NUMBER
   AND A.SQL_ID             = B.SQL_ID
   AND B.DBID               = C.DBID
   AND B.SNAP_ID            = C.SNAP_ID
   AND B.INSTANCE_NUMBER    = C.INSTANCE_NUMBER
   AND C.BEGIN_INTERVAL_TIME BETWEEN TO_DATE('20100405090000','YYYYMMDDHH24MISS') and TO_DATE('20100406235959','YYYYMMDDHH24MISS') 
   AND (A.ELAPSED_TIME_DELTA   >= 2000000 * DECODE(A.EXECUTIONS_DELTA, NULL, 1, 0, 1, A.EXECUTIONS_DELTA) OR
        A.BUFFER_GETS_DELTA    >=   10000 * DECODE(A.EXECUTIONS_DELTA, NULL, 1, 0, 1, A.EXECUTIONS_DELTA))
   AND A.PARSING_SCHEMA_NAME  LIKE '%WAS'
ORDER BY B.SNAP_ID, B.DBID, B.INSTANCE_NUMBER, B.SQL_ID, TO_NUMBER(POSITION);