1 일반적인 대기 이벤트

1. 목적

성능저하의 원인이 DB 서버인지 Application 서버인지, 서버의 어느 자원인지의 판별

2. 서버 자원 여유 점검

Sar, vmstat, 작업관리자 등으로 DB 서버와 Application 서버의 여유율 점검


        $> vmstat -t 60 1000
	
	    kthr    memory              page              faults        cpu       time
	    ----- ----------- ------------------------ ------------ ----------- --------
	    r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa hr mi se
	    7 24 3132936  2000   0   0   0 5186 27716   0 5546 40941 14037 40  6  8 45 08:48:06
	    7 26 3137616  2062   0   1   0 7389 47361   0 5787 42744 17656 41  8  8 43 08:49:06
	    9 33 3145090  2081   0   1   0 7047 37573   0 5813 57915 20228 43  9  5 44 08:50:06
	    7 55 3140985  9712   0   1   0 9142 51835   0 8203 39344 20428 37  9  2 52 08:51:06
	    7 52 3152171  2001   0   1   0 8396 51356   0 7999 40604 20296 36  9  2 53 08:52:06

3. OS process 의 자원 점유율 식별

목적 : 성능저하의 원인이 DB process 에 있는지 여부를 점검
방법 : top, topas, nmon, 작업관리자 등으로 자원점유 상위 프로세스가 DB process 인지 확인

2. DB 성능 진단 방법

1. Wait Time 누적 치 점검

DB startup 시점부터 현재까지의 Batch 를 포함한 전체 성능 통계가 gathering 됨


SELECT *
FROM   V$SYSTEM_EVENT
WHERE  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', 
		   'lock manager wait for remote message',
		   'single-task message', 
		   'PL/SQL lock timer')
ORDER  BY TIME_WAITED DESC;

EVENT	TOTAL_WAITS	TOTAL_TIMEOUTS	TIME_WAITED	AVERAGE_WAIT	TIME_WAITED_MICRO
db file scattered read	22584478	29400	21218188	1	212181878128
buffer busy waits	22584478	29400	21218188	4	1212181878128
latch free	5126016	5087031	19254764	4	192547636520
enqueue	54587	52234	15791067	289	157910668926
SQL*Net more data to client	6968101	0	1608363	0	16083629190
db file sequential read	55003705	0	1086626	0	10866256691
control file parallel write	1155769	0	431080	0	4310799289
log file parallel write	393075	0	179585	0	1795854562
log file sync	291954	17	144272	0	1442723748

모니터링 툴 이용한 시구간 별 성능통계 분석
- 모니터링툴의 logging 기능을 이용하여 시간대별 성능통계의 snapshot 분석
- 상용화된 GUI 툴이나 Oracle Stats Pack 이용

Online 세션 점검
- 현재시점의 실행중인 세션들의 wait event 분석
  - GUI 툴이나 script 이용


SELECT /*+ ORDERED */
       S.SID SID,
       S.SERIAL#,
       Q.SQL_TEXT,
       S.USERNAME "USER",
       S.PROGRAM,
       S.MODULE,
       S.LAST_CALL_ET DUR,
       P.SPID "OS-Pid",
       W.SECONDS_IN_WAIT AS "W_time(Sec)",
       W.STATE, -- 9i above
       W.ENAME EVENT,
       W.P1TEXT || ':' ||
       DECODE(W.EVENT, 'latch free', W.P1RAW, TO_CHAR(W.P1)) || ',' ||
       W.P2TEXT || ':' || TO_CHAR(W.P2) || ',' || W.P3TEXT || ':' ||
       TO_CHAR(W.P3) "Parameter"
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', 
                               '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',
                               'lock manager wait for remote message',
                               'single-task message', 
                               'PL/SQL lock timer',
                               'jobq slave wait', 
                               'queue messages')) 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 S.LAST_CALL_ET DESC;

SID	SERIAL#	SQL_TEXT	USER	PROGRAM	MODULE	DUR	OS-Pid	W_time	STATE	EVENT	Parameter
51	22808	{code:SQL} SELECT ROWNUM AS ID , SUBSTR('15001852001001', 5, 3)
'9'

, '' AS MASTERID
'0' ||
JI.TOJI_SEQ ||
JI.TOJI_MOVE_SEQ ||
JI.O_NUM ||
JI.P_CHASU ||
, JI.SU_CHASU AS OBJECTID
, 'O' AS GUBUN
, '' AS CUSTKEY
, CS.P_SEQ ||
CS.TOJI_MOVE_SEQ ||
CS.P_SEQIN ||
CS.SO_KUBUN AS PID
, CS.P_NAME AS PNAME
, '' AS RID
, '' AS RNAME
, '' AS RIGHTS
, CS.P_JIBOON AS

|CMP|JDBC Thin Client|JDBC Thin Client|9|27560|9|WAITING|db file scattered read|file#:27,
block#:111707,
blocks:16|

    * Online 세션 점검
        ** DB 통계정보를 이용한 HIT 율 점검으로 DB Parameter 설정 점검

h1. 3. Top SQL 의 튜닝

*1. 목적*
    - 시스템에 부하를 많이 주거나 장시간 돌아간 SQL 이 SGA 에 cache 되어있는 동안 모니터링하여 튜닝

*2. 시스템에서 I/O Wait 비중이 높은 경우 Physical Read 를 많이 한 SQL 위주로 검출하여 튜닝*

*3. 시스템의 CPU 가 부족한 경우 Buffer Cache 를 많이 사용한 한 SQL 위주로 검출하여 튜닝*

*4. Shared Pool Latch Wait 이 많을 경우 Literal SQL 에 의한 Hard Parsing 을 발생시키는 SQL 을 검출하여 변수처리를 하여 SQL 이 공유되도록 튜닝*

*5. 자원이 여유 있는 상태에서 장시간 수행되는 SQL 튜닝*

{code:SQL}
SELECT SUBSTR(SQL_TEXT, 1, 30),
       MODULE,
       -- ACTION,
       -- PARSING_USER_ID,
       U.USERNAME,
       COUNT(*) SQL_NUM,
       SUM(EXECUTIONS) EXE,
       SUM(DISK_READS) DISK_READS,
       SUM(BUFFER_GETS) BUFFER_GETS,
       ROUND(SUM(BUFFER_GETS) / SUM(EXECUTIONS)) BUFF_PER_EXE,
       SUM(ROWS_PROCESSED) ROWS_PROCESSED,
       SUM(SHARABLE_MEM) SHAREABLE,
       SUM(ELAPSED_TIME) ETIME
FROM   V$SQLAREA A,
       ALL_USERS U
WHERE  A.PARSING_USER_ID = U.USER_ID
-- WHERE  SQL_TEXT LIKE '%select  *  from (select  inner_temp%'
-- AND    MODULE LIKE '%EPOPJ702%'
-- AND    USERNAME = 'PUBOWNER'
GROUP  BY SUBSTR(SQL_TEXT, 1, 30),
          MODULE,
          U.USERNAME
HAVING SUM(EXECUTIONS) > 0 
-- AND COUNT(*) = SUM(EXECUTIONS)
-- ORDER  BY 7 DESC -- BUFFER_GETS
ORDER  BY 8 DESC -- buff_per_exe

SUBSTR(SQL_TEXT,1,30)	MODULE	USERNAME	SQL_NUM	EXE	DISK	BUFFER	BUFF_PER_EXE	ROWS	SHAREABLE	ETIME
SELECT A.STEP1		CITIS_PROD	1	1	1	980935	980935	5541	150081	13262728
select to_char(sysdate,:"S		SYS	1	3	1	1478851	492950	147	174772	47164189
SELECT A.STEP1 AS STEP1, A.	SQL*Plus	CITIS_PROD	1	3	47	1330504	443501	6899	343586	21291087
SELECT gubun,code AS gubun_id		CITIS_PROD	1	9	1	3624029	402670	207	134070	15254069
SELECT count(**) as CNT FROM T		CITIS_PROD	1	1	190617	225411	225411	1	58820	24910460
select so.user_id,		CITIS_PROD	1	1	0	218155	218155	1	92240	1226768
SELECT "DOC_NO","ENTRY_DT" FRO		CITIS_PROD	1	6	326	347228	57871	27936	13848	11342225
select * from tb200 where tit	TOAD 8.0.0.47	CITIS_PROD	1	7	399	404834	57833	7	20216	4660097
SELECT "ENTRY_ID","ENTRY_DT","		CITIS_PROD	1	3	164801	166635	55545	11268	13077	80070363
SELECT doc_no,title,		CITIS_PROD	2	383	6795	18039807	47101	721	149669	816101593