SQL 모니터링 스크립트

출처 : http://sulisys.egloos.com/368459

 

--wait event 보기(값이 없다면 현재 업무시스템에 심각한 문제를 일으키는 쿼리는 없는 것으로 간주해도 무방함
select /*+ ordered / distinct /* 속도를 위해 v$sql을 조인할 경우 중복되는 레코드 제거 */
s.sid SID, s.username, s.program, p.spid "OS-Pid",w.seconds_in_wait as "W_time(Sec)",
decode(w.wait_time,0,'Wai-ting', 'Waited') Status, w.ename event,
-- p1text || ':' || decode(event,'latch free',p1raw, to_char(p1)) ||','||
-- p2text || ':' || to_char(p2) ||','|| p3text || ':' || to_char(p3) "Additional Info",
q.sql_text
from ( select a.*, decode(a.event,'latch free', 'latch free (' ||b.name||')',
'row cache lock', 'row cache lock (' || c.parameter || ')',
'enqueue', 'enqueue ('||chr(bitand(p1, -16777216)/16777215)||
chr(bitand(p1,16711680)/65535)||':'||
decode(bitand(p1,65535), 1, 'N', 2, 'SS',3,'SX',4,'S',5,'SSX',6,'X') ||')',
a.event ) ename
from v$session_wait a, v$latchname b, v$rowcache c
where a.p2 = b.latch#(+) and a.p1 = c.cache#(+) and c.type(+) = 'PARENT'
and a.event not in ('rdbms ipc message','smon timer','pmon timer','slave wait','pipe get','null event',
'SQL*Net message from client', 'SQL*Net message to client','PX Idle Wait',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'ges remote message', 'wakeup time manager', /* idle event 적절히 수정 */
'lock manager wait for remote message', 'single-task message')
) w, v$session s, v$process p, v$sql q
where w.sid = s.sid and s.paddr = p.addr
and s.sql_hash_value = q.hash_value(+) and s.sql_address = q.address(+)
order by w.ename;



--가장 많이 실행된 쿼리(비실시간)
select sql_text
, round(decode(executions,null,0,0,0,(nvl(buffer_gets,0)/executions)),1) BUFGETSPEREXEC
,EXECUTIONS,PARSE_CALLS,DISK_READS,BUFFER_GETS,ROWS_PROCESSED
,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,MODULE,USERS_EXECUTING
,SORTS,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,LOADS
,FIRST_LOAD_TIME,INVALIDATIONS,COMMAND_TYPE,OPTIMIZER_MODE
,OPTIMIZER_COST, PARSING_USER_ID
from v$sql
where executions > 10000
and PARSING_USER_ID <> 0
order by executions DESC



--비효율적인 SQL List 보기(비실시간)
select sql_text
, round(decode(executions,null,0,0,0,(nvl(buffer_gets,0)/executions)),1) BUFGETSPEREXEC
,EXECUTIONS,PARSE_CALLS,DISK_READS,BUFFER_GETS,ROWS_PROCESSED
,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,MODULE,USERS_EXECUTING
,SORTS,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,LOADS
,FIRST_LOAD_TIME,INVALIDATIONS,COMMAND_TYPE,OPTIMIZER_MODE
,OPTIMIZER_COST, PARSING_USER_ID
from v$sql
where decode(executions,null,0,0,0,(nvl(buffer_gets,0)/executions)) > 10000
and PARSING_USER_ID <> 0
order by BUFGETSPEREXEC DESC


위의 SQL문장은 Execution마다 평균 몇 개의 Oracle Block을 읽었는지(Buffer로부터. 만일 Buffer
에 오러클 Block이 없었다면 Disk로부터 가져온 값도 포함됨)의 역순으로 보여주므로 위의 Top
순서 위주로 비효율적인 SQL로 판단하면 됨. 특히 ROWS_PROCESSED(처리된 Row수)가 작으면
서 BUFGETSPEREXEC(평균 Exec당 Buffer Block Read Count)가 큰 SQL위주로 Tuning.
또한 EXECUTIONS가 높은 SQL문장이 자주 실행되므로 이들 SQL문장은 반드시 Tuning. 그러나
1회성 SQL들은 EXECUTIONS이 작지만 SQL로 자주 실행되는 형태를 판단해야 함.
위의 SQL을 TOAD와 같은 Tool을 이용하면 효과적

SQL_TEXT : SQL 의 Text(V$SQL.SQL_TEXT는 최대 1KB만 보여줌)
 EXECUTIONS : SQL문장이 실행된 횟수 (Instance Startup이후 의 누적치).
    비 공유 SQL일 경우 대부분 1. 공유 SQL일 경우 이 값이 높다.
 FIRST_LOAD_TIME: SQL이 처음으로 Cache에 Loading된 시간
 PARSE_CALLS : Parse Request를 요청한 수(대부분 < executions)
    68 DISK_READS : SQL을 실행하기 위해 Disk로부터 읽어온 Oracle Block수
(읽은 Size는 이 값 * db_block_size)
 BUFFER_GETS : SQL을 실행하기 위해 Buffer로부터 읽어온 Oracle Block수.
주로 이 Column의 값을 exections으로 나누어 높은 값의 SQL이 비효율적인 SQL임. (Disk로
부터 읽어 온 값도 여기에 포함되어 있음)
 ROWS_PROCESSED : SQL로 실행되어 처리된(Select Low수 또는 Transaction대상 Row수)
 OPTIMIZER_MODE : 이 SQL문장이 실행될 때의 OPTIMIZER_MODE
 OPTIMIZER_COST : 이 SQL문장이 실행될 때의 Optimizer(Cost Base Optimizer)에 의해 계산된 Cost수
이며, 큰 값일수록 비 효율적이나 이 값은 다른 SQL의 Cost와는 비교대상이 아님)
 PARSING_USER_ID: 이 SQL문장을 실행했던 User ID(0은 SYS user로 대부분 Recursive SQL,5는 SYSTEM)
 MODULE : 이 SQL문장을 실행했던 APP의 Module명 (예. SQL*Plus,T.O.A.D).
DBMS_APPLICATION_INFO.SET_MODULE 로 Application에서 실행하게 되면 나타나며
어떤 module에서 들어온 SQL인지 확인할 수 있다.



--과다한 Sorting유발 SQL 찾기 및 TEMP Tablespace의 Sort Space 현황 보기
select /*+ ORDERED */
se.username ,
session_num ,
se.process ,
segfile# ,
segblk#,
segtype,
extents ,
blocks,
hash_value
from v$sort_usage so, v$session se, v$sql sq
where so.session_addr = se.saddr
and se.sql_address = sq.address
--and se.audsid != userenv('sessionid')