태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

[티팩] 지능적 대기 이벤트 분석 - Part 1

오라클 2010.06.29 16:10
오라클이 제공하는 대기이벤트(Wait Event)는 정말 독특합니다. 다른 어떤 제품(DBMS에 국한하지 않고)들을 봐도 대기 현상을 이렇게까지 자세하게 보고해주는 경우는 없습니다.

이것은 오라클 성능 문제를 분석하고 해결해야 하는 우리들에게 있어서는 큰 선물이자 고민거리입니다. 마치 초보 운전자에게 포르쉐를 주는 격이라고 할까요? 당장 운전 욕심이 나겠지만 포르쉐같은 차를 제대로 몰려면 탄탄한 운전 기본기가 있어야 합니다. (아... 포르쉐한번 몰아보고 싶다)

대기 이벤트 분석도 마찬가지입니다. 정확하게 해석하려면 오라클 아키텍처 전반에 대한 일정 수준의 지식이 필요합니다. 이런 것들이 대기 이벤트 분석 기법이 더 실용적이고 광범위하게 사용되는 것을 막는 장벽이 되고 있습니다. 결국 개인이 극복해야할 문제겠지만요.

대기이벤트 분석에서 한가지 명심해야할 것은 대기이벤트만으로는 의미있는 정보를 이끌어내기가 어렵다는 것입니다. 관련된 다른 정보들이 필요한 경우가 많습니다. 티팩에서는 이것을 지능적 대기이벤트 분석이라고 부릅니다. 예를 들어보면 이런 부가적인 정보들이 필요합니다.

  1. cache buffers chains 래치에서 경합이 발생할 경우 주로 어떤 블록(DBA)인지 알 수 있는가?
  2. row cache objects 래치에서 경합이 발생할 경우 주로 어떤 Dictionary Object인지 알 수 있는가?
  3. db file sequential read와 같은 I/O 대기일 경우 어떤 세그먼트의 어떤 종류의 블록에서 발생하는지 알 수 있는가?
  4. 래치, 락(Enqueue), library cache lock, library cache pin, row cache lock, 뮤텍스 등의 동기화 객체에서 경합이 발생할 경우 홀더 정보와 관련 객체 정보를 얻을 수 있는가?
앞으로 몇 차례에 걸쳐서 대기이벤트를 좀 더 지능적으로 분석하기 위해 추가적으로 어떤 데이터를 수집할 필요가 있는지 몇가지 예를 볼려고 합니다. 이 주제만으로도 책 한권을 쓸 수 있을 정도로 방대한 분야라서 몇 가지 핵심적인 내용만을 블로그를 통해서 소개하게 될 것입니다.

1. 홀더를 찾아라!

오라클에서의 경합은 다음 여섯 가지 정도의 동기화 객체에 의해 발생합니다.

  • 래치
  • 락(Enqueue)
  • Library cache lock
  • Library cache pin
  • Row cache lock
  • 뮤텍스
각 동기화 객체가 무엇을 의미하는지는 이미 알고 계신 분들도 많이 때문에 상세하게 설명하지는 않을 것입니다. 제 책이나 블로그나 기타 온라인 자료 등에서 이미 방대한 설명을 제공하고 있습니다.

동기화 객체의 종류별로 제공되는 뷰가 다르기 때문에 홀더 정보를 찾는데도 다른 뷰를 읽어야 합니다. 다행히 최근 버전의 오라클에서는 V$SESSION 뷰의 BLOCKING_SESSION, BLOCKING_INSTANCE등의 컬럼을 통해 대부분의 상황에 대해 홀더 정보를 제공합니다. 대단히 유용한 정보입니다. 하지만 어떤 경우에는 단순히 홀더를 아는 것만으로는 부족한 경우가 많습니다. 정확하게 어떤 오브젝트에서 발생하는지, 어떤 모드로 인해 발생하는지등의 정보가 필요한 경우가 있습니다.

밑에서 보게 될 정보는 아마도(!) 홀더를 찾기 위한 방법을 지금까지의 그 어떤 문서보다 가장 광범위하고 완벽하게 정리한 것일겁니다.(라고 자랑하고 싶지만 그냥 스크립트 정리한 것일뿐입니다)

1.1 래치 홀더 찾기

래치 홀더는 V$LATCHHOLDER를 통해서 얻을 수 있습니다.

-- 래치 홀더 찾기
select h.pid, h.sid, h.laddr, h.name, h.gets
from v$latchholder h, v$session_wait s
where s.sid = &sid
	  and s.p1raw = h.laddr;
래치는 워낙 짧은 시간에 잡고 놓기 때문에 위의 단순한 쿼리만으로는 원하는 결과를 얻지 못하는 경우가 많습니다. (몰론 오라클 버그 등에 의해 특정 래치를 오랫동안 잡는 경우가 있습니다. 이런 경우에는 이런 단순한 쿼리만으로도 추가적인 분석이 가능합니다)

이럴 때 사용할 수 있는 것이 프로파일 방법입니다. 티팩은 1) 스냅샷, 2) 프로파일 두 개의 방법을 사용한다고 말씀드린 바 있습니다. 이 중 프로파일은 짧은 시간 저장되고 사라지는 값을 가능한 여러 번 캡쳐해서 요약해서 보는 것을 말합니다. V$LATCHHOLDER 뷰도 다음과 같이 프로파일링할 수 있습니다.

-- 래치 홀더 프로파일링하기
TPACK@ukja1021> select /*+ ordered use_nl(x) */
  2     x.sid, x.name, count(*)
  3  from
  4     (select /*+ no_merge */ level from dual connect by level <= 10000) t1,
  5     (select  /*+ no_merge */ h.pid, h.sid, h.laddr, h.name, h.gets
  6             from v$latchholder h, v$session_wait s
  7             where s.sid = &sid
  8                             and s.p1raw = h.laddr) x
  9  group by x.sid, x.name
 10  ;
old   7:                where s.sid = &sid
new   7:                where s.sid =        138

       SID NAME                             COUNT(*)
---------- ------------------------------ ----------
       134 shared pool                            33
       134 library cache                         308
1.3 Enqueue 홀더 찾기

Enqueue 홀더는 V$LOCK 뷰를 보는 것만으로 충분합니다. Enqueue 경합을 관찰하는 방법은 이미 많이 알려져 있죠.

-- Enqueue 홀더 찾기
select
	h.sid,  -- 홀더 SID
	h.type,   -- 락 타입
	h.id1,  -- ID1
	h.id2,  -- ID2
	h.lmode, 
	t.name,
	t.id1_tag,
	t.id2_tag,
	t.description
from v$lock h, v$lock w, v$lock_type t
where w.sid = { waiter_sid }
	and h.id1 = w.id1
	and h.id2 = w.id2
	and h.lmode > 0 
	and h.block > 0
	and h.type = t.type
;
1.3 Library cache lock 홀더 찾기

Library cache lock은 LCO(Library Cache Object)를 보호하는 락입니다. 경합이 발생하면 library cache lock 대기이벤트가 발생하죠. Library cache lock 홀더는 X$KGLLK 뷰를 통해서 찾을 수 있습니다.

-- Library cache lock 홀더 찾기
select
	(select sid from v$session where saddr = k.kgllkuse) as sid,  -- 홀더 SID
	k.kglhdnsp,  -- 객체의 종류
	k.kglnaobj,  -- 객체명(SQL문장이나 테이블, 프로시저명 등)
	decode(k.kgllkmod, 3, '3(X)', 2, '2(S)', 1, '1(N)', k.kgllkmod) as lkmode
from x$kgllk k
where k.kgllkhdl = { v$session_wait.p1raw }
	  and k.kgllkmod > 0
;
1.4 Library cache pin 홀더 찾기

Library cache pin은 Cursor나 Procedure의 실행을 보호하는 락입니다. 경합이 발생하면 library cache pin 대기이벤트가 발생하죠. Library cache pin 홀더는 X$KGLPN 뷰를 통해서 찾을 수 있습니다.

-- Library cache pin 홀더 찾기
select
  (select sid from v$session where saddr = n.kglpnuse) as sid, 
  o.kglnaobj,
  o.kglhdnsp,
  decode(n.kglpnmod, 3, '3(X)', 2, '2(S)', 1, '1(N)', n.kglpnmod) as lkmode
from x$kglpn n, x$kglob o
where n.kglpnhdl = { v$session_wait.p1raw }
	  and n.kglpnmod > 0
	  and o.kglhdadr = n.kglpnhdl
;
1.5 Row cache lock 홀더 찾기

Row cache lock은 Dictionary Object에 대한 변경을 보호하는 락입니다. 경합이 발생하면 row cache lock 대기이벤트가 발생하죠. Row cache lock 홀더는 V$ROWCACHE_PARENT 뷰를 통해서 찾을 수 있습니다.

-- Row cache lock 홀더 찾기
select 
	(select sid from v$session where saddr = h.saddr) as sid,  -- 홀더 SID
	h.cache_name,  -- Dictionary Object의 종류
	h.lock_mode,    
	h.inst_lock_type 
from v$rowcache_parent h, v$rowcache_parent w, v$session s
where h.address = w.address
	  and w.saddr = s.saddr 
	  and s.sid = { waiter_sid }
	  and h.lock_mode > 0
;	  
1.6 뮤텍스 홀더 찾기

뮤텍스 홀더는 V$MUTEX_SLEEP_HISTORY 뷰를 통해서 찾을 수 있습니다.

-- 뮤텍스 홀더 찾기
select * from (
	select
		 blocking_session as sid,  -- 홀더 SID
		(select kglnaobj from x$kglob 
		where kglnahsh = mutex_identifier 
		and rownum = 1) as obj_name,   -- Object 명 (11g에서 추가!)
		 mutex_type,  -- 뮤텍스 종류
		 location,  -- 뮤텍스를 획득한 위치 정보, 즉 무슨 일을 하느라고 뮤틱스를 획득하려고 하는지?
		 sleeps,
		 gets,
		 to_char(sleep_timestamp,'yyyy/mm/dd hh24:mi:ss') as sleep_timestamp
	from v$mutex_sleep_history
	where requesting_session = session_id
	order by sleep_timestamp desc
) where rownum <= 1	;
11g에서는 MUTEX_IDENTIFIER라는 유용한 컬럼이 추가되어 뮤텍스 경합을 분석하기가 더 쉬워졌습니다.

위의 정보들에서 알 수 있듯이 홀더를 찾는 것도 중요하지만, 구체적으로 어떤 상태(또는 객체)에서 문제가 발생하는지를 파악하는 것또한 중요합니다. 이런 이유때문에 문서화가 잘 되어 있지 않은 V$ 뷰나 X$ 뷰를 볼 수 밖에 없습니다.

티팩에서는 Session Detail Report를 통해 위의 정보들을 보여줍니다. 위에서 소개한 것과 거의 비슷한 방법을 사용합니다.

Library cache pin 경합을 간단한 예로 소개해보겠습니다.

-- 아주 긴 SQL 문장을 만듭니다. 정확하게 말하면 하드 파스 시간이 긴 SQL 문장입니다.
-- http://sites.google.com/site/ukja/sql-scripts-1/j-m/make_long
TPACK@ukja1021> @make_long
select count(*) from TPACK_REPORT_PARAMS, TPACK_REPORT_CONDITIONS,
TPACK_REPORT_JOB_HIST, TPACK_REPORT_SESSION_TEMP, TPACK_FUNCTION_NAMES,
...

-- Session #1에서 위에서 만든 쿼리를 수행합니다.(제 로컬 노트북에서 2분 50초 정도 소요)
TPACK@ukja1021> exec dbms_application_info.set_client_info('session1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.39
TPACK@ukja1021> @long_parse
....

-- 동시에 Session #2에서 동일한 쿼리를 수행합니다. 
TPACK@ukja1021> exec dbms_application_info.set_client_info('session2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
TPACK@ukja1021> @long_parse
...
이렇게 하면 Session #2는 Session #1이 하드 파스를 끝내는 동안 대기 상태에 빠집니다. 다음과 같이 Session #2에 대해 Session Detail Report를 수집해서 대기 정보를 상세하게 분석합니다.
-- Session #2의 SID 구하기
TPACK@ukja1021> col sid new_value sid
TPACK@ukja1021> select sid from v$session where client_info = 'session2';

       SID
----------
       138

1 row selected.

Elapsed: 00:00:00.14

-- Session #2의 Session Detail Reort
TPACK@ukja1021> col name format a30
TPACK@ukja1021> col value format a45
TPACK@ukja1021> set pages 200
TPACK@ukja1021> set long 10000000
TPACK@ukja1021> select * from table(tpack.session_detail(&sid, 'wait_detail'));
old   1: select * from table(tpack.session_detail(&sid, 'wait_detail'))
new   1: select * from table(tpack.session_detail(       138, 'wait_detail'))

NAME                           VALUE
------------------------------ ---------------------------------------------
SID                            138
Serial#                        711
SPID                           3724
Program                        sqlplus.exe
Process                        5364:5208
Module                         SQL*Plus
SQL ID
Child No
SQL Text
Status                         ACTIVE
Blocking Instance              1
Blocking Session               134
Event                          library cache pin
Seq#                           4137
P1(P1raw)                      970230240(39D489E0)
P2(P2raw)                      970230240(36A9AB34)
P3(P3raw)                      970230240(000000C8)
Seconds in wait                10
State                          WAITING
Wait Event                     library cache pin
Holder SID                     134
Namespace                      CURSOR
Object                         select count(*) from TPACK_SGA_STAT, TPACK_SG
                               A_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK
                               _SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TP
                               ACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT,
                                TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_ST
                               AT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA
                               _STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_
                               SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_S

                               GA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPAC
                               K_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, T
                               PACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_D
                               UMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_
                               HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP,
                               TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_
                               DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK
                               _HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPA

                               CK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUM
                               P, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HE
                               AP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TP
                               ACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DU
                               MP, TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM,
                                TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM, TP
                               AC

Holding Mode                   3(X)
Namespace와 Object 정보로부터 library cache pin 대기에 대한 보다 정확한 분석이 가능합니다. Oracle 11g에서는 library cache pin이 아닌 뮤텍스에 대한 경합이 발생합니다.
TPACK@ukja1106> select * from table(tpack.session_detail(&sid, 'wait_detail'));
old   1: select * from table(tpack.session_detail(&sid, 'wait_detail'))
new   1: select * from table(tpack.session_detail(       127, 'wait_detail'))

NAME                           VALUE
------------------------------ ---------------------------------------------
SID                            127
Serial#                        1642
SPID                           2656
Program                        sqlplus.exe
Process                        5364:5208
Module                         SQL*Plus
SQL ID
Child No
SQL Text
Status                         ACTIVE
Blocking Instance              1
Blocking Session               139
SQL Exec Start
Event                          cursor: pin S wait on X
Seq#                           631
P1(P1raw)                      3859422310(00000000E60A1C66)
P2(P2raw)                      3859422310(00000000008B0000)
P3(P3raw)                      3859422310(0000000000050256)
Seconds in wait                0
State                          WAITING
Wait Event                     cursor: pin S wait on X
Holder SID                     139
Mutex Type                     Cursor Pin
Location
Target Object                  select count(*) from TPACK_SGA_STAT, TPACK_SG
                               A_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK
                               _SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TP
                               ACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT,
                                TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_ST
                               AT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA
                               _STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_
                               SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_S

                               GA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPAC
                               K_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, T
                               PACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_D
                               UMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_
                               HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP,
                               TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_
                               DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK
                               _HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPA

                               CK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUM
                               P, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HE
                               AP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TP
                               ACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DU
                               MP, TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM,
                                TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM, TP
                               AC

Last Sleep Time                2010/06/29 16:03:51
Gets                           1
Sleeps                         611
중요한 것은 대기 현상이 발생할 때 적절한 뷰에서 적절한 데이터를 관찰할 수 있는가입니다. 티팩은 그 중 핵심적인 데이터들을 자동으로 수집해줄 뿐입니다.

쓰다보니 너무 길어졌네요. 다음 포스트에서는 대기 이벤트 분석에 필요한 다른 기타 추가적인 데이터들을 소개하겠습니다.

이전 글 보기

  1. [티팩] 성능 문제를 트러블슈팅하는 두가지 틀(Frame)
  2. [티팩] oradebug
저작자 표시
신고
tags :
Trackback 0 : Comment 0

Write a comment

티스토리 툴바