나름 프로그램 만들다보면, 성능에 신경쓰지 못하고 그냥 넘어가는 경우가 있다.
그리고 나중에, 어느부분에서 느린지 프로그램도 프로그램이지만 DB에서 찾아야 하는 경우도 있다.
그럴때 아래 쿼리를 이용해서 많은 블록에 접근하여 성능에 영향을 주는 SQL을 찾아낼수 있다.
SELECT /*+ NO_MERGE(v) */
s.HASH_VALUE
, s.MODULE
,s.last_active_time
,
( SELECT username
FROM DBA_USERS
WHERE user_id =s.parsing_user_id
) username
, executions
, ROUND(elapsed_time/(DECODE(executions,0,1,NULL,1,executions)*1000000),1) elapsed_t
, ROUND(buffer_gets/DECODE(executions,0,1,NULL,1,executions),1) buffer_gets_per_exec
, rows_processed
, sql_text
FROM
( SELECT /*+ NO_MERGE(V1) */
MAX(HASH_VALUE) HASH_VALUE
FROM
(
SELECT HASH_VALUE, SQL_TEXT
FROM V$SQL S
WHERE 1=1
AND ( ROUND(buffer_gets/DECODE(executions,0,1,NULL,1,executions),1) > 10000
OR elapsed_time/(DECODE(executions,0,1,NULL,1,executions)*1000000) >10 )
AND (module IS NULL
OR (module NOT LIKE 'TOAD%'
AND module NOT LIKE 'Orange%'
AND module NOT LIKE 'Golden32.exe%'
AND module NOT LIKE 'PL/SQL Developer%'
AND module NOT LIKE 'T.O.A.D%'
AND UPPER(module) NOT LIKE 'SQL*PLUS%')
)
AND PARSING_USER_ID IN
( SELECT user_id
FROM DBA_USERS
WHERE username NOT IN ('SYS','SYSTEM')
)
) V1
GROUP BY SUBSTR(SQL_TEXT,1,150)
) v,
V$SQLAREA s
WHERE v.hash_value=s.hash_value
AND parsing_user_id IN
( SELECT user_id
FROM DBA_USERS
WHERE username NOT IN ('SYS','SYSTEM')
)
AND s.module NOT LIKE 'SmartSQL%'
ORDER BY buffer_gets;위 쿼리의 제목은 "쿼리수행시간이 10초이상걸리거나, 10000블록 이상 접근하는 쿼리 " 이 정도가 되겠다.
※아래는 이해를 돕기위한 설명.
- Buffer Gets : 해당 SQL이 수행되면서 액세스한 블록의 수이며 V$SQL 등에서는 Executions가 곱해져서 보여진다.
- Executions : 해당 SQL이 수행된 횟수를 의미하며 변수 처리를 바인 드 변수 처리로 수행하지 않으면 다른 SQL로 집계된다.
- Elapsed Time : 해당 SQL이 수행된 수행 시간이며 V$SQL에서는 1,000,000으로 나눠야 초가 된다. 물론 Executions로 곱해져 있기 때 문에 Executions로도 나눠야 1회 수행 시 수행 시간이 된다.
- (ROUND(BUFFER_GETS/DECODE(EXECUTIONS,0,1,NULL,1, EXECUTIONS),1) > 10000 - 10,000 Block 이상 액세스한 SQL
- ELAPSED_TIME/(DECODE(EXECUTIONS,0,1,NULL,1,EXECUT IONS)*1000000) >1- 수행시간 1초 이상
- order by BUFFER_GETS; - Block 액세스에 수행 횟수를 곱한 순으로 결과 추출
프로그램(DB) 유지보수에 큰 도움이 되길 ..