[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);
'01.DB튜닝' 카테고리의 다른 글
[DB튜닝]Cursor PIN S Wait On X 대기 이벤트 (0) | 2012.12.19 |
---|---|
[DB튜닝]AWR 활용한 정보 얻기 (0) | 2012.12.19 |
[DB튜닝]DBMS_XPLAN 이용한 정보 얻기 (0) | 2012.12.19 |
[DB튜닝]튜닝 로드맵 (0) | 2012.12.19 |
[DB튜닝]서비스 로직 흐름을 변경하여 DB 튜닝 (0) | 2012.12.19 |