[오라클] RAC 튜닝 방법(3)

DFS lock handle

DFS lock handle은 요청한 Global lock의 lock handle을 기다리고 있는 대기 이벤트입니다. 이 lock handle은 global lock과 동일합니다. lock handle을 획득하면 global lock을 소유한 상태로 lock conversion이나 lock release와 같은 여러 작업을 수행할 수 있습니다. global lock은 DLM(Distributed Lock Manager)에 의해 관리됩니다.

DFS는 Distributed File System의 약자로, 오라클 제품 역사(History)와 관련 있는 용어로 생각됩니다. 오라클 10g의 RAC 기능이 완성되기 전까지 오라클은 상당한 기간동안 분산 데이터베이스를 구현해 왔으며, 최초의 분산 데이터베이스는 분산 파일 시스템에 기반하고 있었던 것으로 알려져 있습니다. 이러한 이유로, 아직까지 일부 global lock 경합에서 여전히 DFS라는 용어를 사용하고 있습니다.

대부분의 lock에 대한 글로벌 경합은 싱글 인스턴스 환경에서와 같은 대기 이벤트 명으로 관찰됩니다. 가령 글로벌 TM lock 경합은 enq: TM-contention 이벤트로 관찰되며, 글로벌 SQ lock 경합은 enq: SQ-contention 이벤트로 관찰됩니다. 이들과는 달리 DFS lock handle 대기 이벤트로 관찰되는 대표적인 global lock 경합에는 SV lock과 CI lock 경합이 있습니다.

Wait Time

세션이 DLM으로부터 lock handle을 소유할 때까지 loop를 돌며 대기한다. 각 loop 마다 0.5초씩 대기한다.

Parameter

P1(lock 유형과 요청(requested)모드를 나타낸다. lock 유형은 TX, TM, SQ, SV, CI 등과 같은 lock 유형을 의미한다. 모드는 1(N)~6(X)의 lock 모드를 의미한다. 아래의 SQL을 사용하여 enqueue명과 요청 모드를 확인할 수 있다.)

P2(ID1. V$LOCK.ID1 컬럼과 같은 의미이다.)

P3(ID2. V$LOCK.ID2 컬럼과 같은 의미이다.)

SELECT chr(to_char(bitand(p1,-16777216))/16777215||

chr(to_char(bitand(p1, 16711680))/65535) "Lock",

to_char(bitand(p1, 65535)) "Mode"

FROM v$session_wait

WHERE event='DFS lock handle'

(32 - bit오라클의 수행 결과)

SID EVENT P1 R1RAW Lock Mode

---- ---------------- ---------------- ------------------------ ----------- ----------

24 enqueue 1415053318 54580006 TX 6

(64 - bit오라클의 수행 결과)

SID EVENT P1 R1RAW Lock Mode

---- ---------------- ---------------- ------------------------- ----------- ---------

24 enqueue 1415053318 0000000054580006 TX 6

64-bit의 경우 앞의 0을 무시하고 마지막 4byte(32bit는 4byte로 이루어져 있음)에서 상위 2byte는 lock 유형입니다. 54580006의 경우 상위 2byte는 0x5458HEX입니다. 54는 10진수로 84이고, 58은 88이므로 lock 유형은 아래와 같이 알아낼 수 있습니다.

Select chr(84) || chr(88) from dual;

lock 모든는 나머지 2byte에 인코딩되어 있지만 쉽게 알 수 있습니다. 위의 예에서 lock 모드는 0x0006HEX이므로 6입니다. lock 모드는 다음과 같습니다.

Mode Value	Description
1	Null Mode
2	Sub-Shared
3	Sub-Exclusive
4	Shared
5	Shared/Sub-Exclusive
6	Exclusive

Common Causes and Actions

원인: sequence 속성에 따른 대기 이벤트

진단 방법: sequence 속성 중 cache 및 order 설정 값의 적정 여부를 조사한다.

개선 방법:

1. Sequence의 Cache Size를 늘려라.

DFS lock handle 이벤트는 OPS나 RAC 환경에서 버퍼 캐시 동기화를 제외한 row cache나 library cache의 동기화를 위해 lock을 획득하는 과정에서 대기하는 이벤트입니다. 여러 노드 간에 시퀀스의 순서를 보장하려면 글로벌하게 lock을 획득해야 하고 이 과정에서 DFS lock handle 대기가 발생하게 되는 것입니다. SV lock을 획득하는 과정에서 발생하는 DFS lock handle 대기 이벤트의 P1, P2 값은 enq: SQ-contention 대기 이벤트와 동일합니다.

(P1=mode+namespace, P2=object#) 따라서 P1 값으로부터 SV lock인지의 여부를 확인할 수 있고, P2 값을 통해 어떤 시퀀스에 대해 대기가 발생하는지 확인할 수 있습니다. SV lock 경합문제가 발생하는 경우의 해결책은 SQ lock의 경우와 동일합니다. 캐시 사이즈를 적절히 키워주는 것이 유일한 해결책입니다.

2. RAC의 경우 Sequence에 CACHE+NOORDER 속성을 부여하라.

RAC와 같은 멀티 노드 환경에서는 시퀀스의 캐시 사이즈가 성능에 미치는 영향은 싱글 노드 환겨에서 보다 훨씬 큽니다. 따라서 가능하면 CACHE + NOORDER 속성을 부여하고 충분한 크기의 캐시 크기를 부여하는 것이 바람직합니다. 만일 순서를 보장하는 것이 반드시 필요하다면 CACHE + ORDER 속성을 부여합니다. 하지만 이 경우 순서를 보장하기 위해 인스턴스간에 데이터 교환이 끊임없이 발생합니다. 이로 인해 NOORDER 속성을 부여한 경우보다 성능면에서 불리합니다.

* Sequence 속성에 따른 대기 이벤트

한가지 주의할 것은 CACHE 속성을 부여하지 않은 경우에는 ORDER 속성 사용여부나 RAC 환경 여부와 무관하게 항상 row cache lock 이벤트를 대기한다는 것입니다. Row cache lock은 글로벌하게 사용가능한 lock이며, 싱글 인스턴스 환경이나 멀티 인스턴스 환경에서 동일하게 사용됩니다. 시퀀스 생성시 부여한 속성에 따른 대기 이벤트를 정리하면 다음과 같습니다.

- row cache lock: Sequence.nextval을 호출하는 과정에서 딕셔너리 정보를 물리적으로 변경하는 경우에 획득합니다. NOCACHE 속성을 부여한 시퀀스에서 사용됩니다.

- SQ lock: 메모리에 캐시되어 있는 범위안에서 Sequence.nextval을 호출하는 동안 획득합니다. CACHE 속성을 부여한 시퀀스에서 사용됩니다.

- SV lock: RAC에서 노드간에 순서가 보장된 상태로 Sequence.nextval을 호출하는 동안 획득합니다. CACHE + ORDER 속성을 부여한 시퀀스에서 사용됩니다.

* Row cache lock 경합

캐시(Cache) 속성을 부여하지 않은 시퀀스를 동시에 많은 프로세스가 사용하는 경우 row cache lock 이벤트 대기가 광범위하게 발생할 수 있습니다. 캐시를 사용하지 않은 시퀀스에 대해 nextval을 호출하면 매번 Dictionary 정보가 변경되어야 하기 때문에 row cache lock을 SSX 모드로 획득해야 합니다. SSX모드간 에는 상호 호환성이 없기 때문에 이 과정에서 경합이 발생하게 됩니다. Row cache 덤프와 V$ROWCACHE_PARENT 뷰부터 정확한 객체 정보를 확인하는 방법을 통해 row cache lock의 정보를 알아보겠습니다.

SQL> create sequence seq_seq nocache; -- NOCACHE 속성의 시퀀스를 생성한다.

-- 시퀀스의 Ojbect ID를 얻고, 이를 16진수로 변환한다. 16진수로 변환된 값을 이용해야만 row cache 덤프 내에서 시퀀스에 대한 정보를 참조할 수 있다.

SQL> select object_id from DBA_OBJECTS where object_name='SEQ_SEQ';

OBJECT_ID

-----------

107886

SQL> select to_hex(107886) from dual;

TO_HEX(107886)

-------------------------------------------------

1A56E alter session set events 'immediate trace name row_cache level 12'

-- Row Cache 덤프 파일의 내용은 다음과 같다.

--------------(덤프 파일 시작)----------------------------------------------------------------------

....

BUCKET 104:

row cache parent object: address=201D46CC cid=13(dc_sequences)

has=ba7abee7 typ=9 transaction=00000000 flags=00000002

own=201D4740[201D4740,201D4740] wat=201D4748,201D4748] mode=N

status=VALID/-/-/-/-/-/-/-/-

data=

0001a56e 00020004 000f0002 00020001 000002c1 00000000 00000000 00000000

...

위의 덤프파일에서 의미있는 정보는 다음과 같습니다.

- Bucket: row cache 또한 일반적인 다른 객체들처럼 해시 값에 의한 버킷 구조로 관리됨을 알 수 있다.

- address: Row Cache object의 메모리 어드레스, V$ROWCACHE_PARENT 뷰와 조인할 수 있다.

- cid: Category id, V$ROWCACHE.CACHE#와 조인할 수 있다.

- own: row cache lock을 보유한 프로세스 목록

- wat: row cache lock을 대기하는 프로세스 목록

- mode: 현재 row cache lock 보유 모드(N=Null)

- data: 첫번째 16진수가 1a56e로 SEQ_SEQ의 object_id의 16진수 값과 동일함을 알 수 있습니다.

이제, nextval을 대량으로 호출하면서 row cache lock이 어떻게 획득되는지 관찰해보겠습니다. 아래와 같이 10000번 동안 seq_seq.nextval을 호출합니다. row cache lock을 획득하고 해제하는 시간은 매우 짧으므로 많은 횟수를 시도해야 관찰할 수 있습니다.

SQL> declare

SQL> v_seq number;

SQL> begin

SQL> for idx in 1 .. 10000 loop

SQL> select seq_seq.nexxtval into v_seq from dual;

SQL> end loop;

SQL> end;

SQL> /

위의 PL/SQL이 수행되는 동안 덤프를 통해 얻은 address=201D46CC 값을 이용해 V$ROWCACHE_PARENT 뷰 정보를 조회하면, 아래 결과와 같이 SSX(Shared Sub-Exclusive) 모드로 row cache lock을 획득함을 알 수 있습니다.

SQL> select * from v$rowcache_parent where address='201D46CC';

INDX : 2237

HASH : 103

ADDRESS : 201D46CC

CACHE# : 13

CACHE_NAME : dc_sequences

EXISTENT : Y

LOCK_MODE : 5

Shared Sub-Exclusive 모드의 의미는 객체 전체에 대해서는 Shared 모드로, 객체의 일부분에 대해서는 Exclusive 모드로 락을 획득하는 것입니다. Sequence.nextval 호출에 의해 시퀀스 딕셔너리 정보가 변경되는 경우, 시퀀스 자체를 변경시키는 것은 아니고 시퀀스의 "다음 값"만을 변경시키는 것이므로 SSX 모드로 row cache lock을 획득하는 것입니다.

시퀀스를 제외하고는 row cache의 정보를 이처럼 자주 변경하는 일은 거의 없습니다. 따라서 row cache lock 대기가 나타날 경우 시퀀스에 NOCACHE 속성이 부여되어 있지는 않은지 확인해봐야 합니다. OPS 환경에서 시퀀스의 순서를 완벽하게 보장하기 위해 NOCACHE 속성으로 시퀀스를 생성한 경우에 row cache lock 대기현상이 나타나는 경우가 많습니다. RAC 환경에서는 CACHE 속성을 사용하면서, 동시에 노드 간에 시퀀스의 순서를 완벽하게 보장하는 것이 가능합니다.

* SQ Lock 경합

CACHE 속성이 부여된 시퀀스에 대해 nextval을 호출하는 동안 SQ lock을 SSX 모드로 획득해야 합니다. 동시ㅏ에 많은 세션이 SQ lock을 획득하기 위해 경쟁하는 과정에서 경합이 발생하면 enq: SQ-contention 이벤트를 대기하게 됩니다. enq: SQ-contention 이벤트의 P2 값은 시퀀스의 오브젝트 아이디입니다. 따라서 P2의 값을 이용해 DBA_OBJECTS 뷰와 조인하면 어떤 시퀀스에 대해 대기현상이 발생하는지 알수 있습니다.

시퀀스 생성시 부여한 캐시의 크기가 작은 경우에 enq: SQ-contention 대기가 증가하는 경향이 있습니다. 캐시의 크기가 작은 경우에는 메모리에 미리 캐시된 값이 빠른 속도로 소진되며, 캐시값이 소진된 경우, 딕셔너리 정보를 물리적으로 변경하고 다시 캐시하는 작업을 해야합니다. 그동안 SQ lock을 계속해서 획득해야 하기 때문에 enq: SQ-contention 이벤트 대기 시간이 그만큼 증가하는 것입니다. SQ lock 경합에 의한 성능 문제는 CACHE 속성을 크게 해주는 것으로 문제를 해결할 수 있습니다. 불행하게도 시퀀스 생성시 캐시 크기의 기본값이 20으로 작게 설정되어 있습니다. 따라서 사용량이 많을 것으로 예상되는 시퀀스를 생성할때에는 CACHE 값을 1,000 이상으로 크게 잡아주는 것이 좋습니다.

간혹 한번에 많은 세션이 동시에 생성될 때 enq: SQ-contention 이벤트 대기가 발생하는 경우가 있습니다. 그 이유는 V$SESSION.AUDSID(Auditing session id) 컬럼값이 시퀀스를 이용해 생성되는데서 비롯됩니다. 오라클은 새로운 세션이 생성되면 SYS.AUDSES$ 라는 이름의 시퀀스의 nextval을 시용해 AUDSID 값을 생성합니다. SYS.AUDES$ 시퀀스의 캐시 크기는 기본 값인 20으로 설정되어 있습니다. 한번에 많은 세션이 동시에 접속하는 경우에는 SYS.AUDSES$ 시퀀스의 캐시 크기를 10,000 정도로 크게 늘려줌으로써 enq: SQ-contention 대기문제를 해결할 수 있습니다.

RAC에서는 시퀀스 생성시 CACHE 속성을 부여한 상태에서 ORDER 속성을 부여하지 않으면 각 노드가 다른 범위의 시퀀스 값을 메모리에 캐시합니다. 예를 들어 2개의 노드로 이루어진 RAC 환경에서 CACHE 100 속성으로 시퀀스를 생성하는 경우 1번 노드는 1~100번을 사용하고, 2번 노드는 101~200번을 사용하게 됩니다. 만일 양 노드간에 모두 순차적으로 증가하게끔 시퀀스를 사용하려면반드시 아래와 같이 ORDER 속성을 부여해야합니다.

SQL> create sequence ordered_sequence cache 100 order;

* SV Lock 경합
SV lock은 Sequence Value Lock의 약자로 RAC에서 ORDER 속성의 시퀀스 값을 보호하기 위해 사용되는 lock입니다.

CACHE + NOORDER 속성의 시퀀스가 성능 면에서는 가장 유리합니다. 하지만, NOORDER 속성의 시퀀스는 RAC 시스템에서 노드 간의 순서를 보장할 수 없다는 단점이 있습니다. 가령 캐시 크기가 100이고 NOORDER 속성이 부여된 시퀀스를 2 노드로 이루어진 RAC 시스템에서 사용하면 노드 1번에는 1~100번, 노드 2번에는 101~200번의 시퀀스 캐시를 사용하게 됩니다. 따라서 클라이언트가 RAC 시스템에 접속해서 해당 시퀀스를 사용하게 되면 시퀀스 값이 "1, 2, 101, 3, 102, 103"와 같이 양쪽 노드의 캐시 값이 혼재되어 추출됩니다. 대부분의 상황에서는 이러한 현상이 문제가 되지 않지만, 만일 시퀀스 값이 반드시 순차적으로 추출되어야 한다는 제한 조건이 필요하다면 ORDER 속성을 부여함으로써 이러한 현상을 해소할 수 있습니다. ORDER 속성의 시퀀스에서는 RAC의 모든 노드가 동일한 캐시값을 보유합니다. 즉, 노드 1번도 1~100번, 노드 2번도 1~100번의 시퀀스 캐시를 사용하며 두 노드는 SV lock을 이용해서 글로벌 시퀀스 동기화를 수행합니다.

ORDER 속성을 사용할 경우 시퀀스의 값을 추출할 때마다 SV lock에 대한 글로벌 동기화 작업이 발생하므로 NOORDER 속성을 사용할 때보다 성능 면에서는 상당히 불리합니다. 하지만 NOCACHE 속성을 부여할 때보다는 훨씬 성능면에서는 유리합니다. 따라서 RAC 시스템에서는 가능하면 CACHE + NOORDER 속성을 사용하고, 순서 보장이 필요한 경우에 한해 ORDER 속성을 부여하는 것이 좋습니다. NOCACHE 속성은 가능한 한 사용하지 않아야 합니다.

시퀀스 키 값이 누락(Miss) 없이 항상 순차적으로 증가해야 한다는 전제 조건 때문에 NOCACHE 속성의 시퀀스를 사용하는 경우가 종종 있습니다. 이것은 우선, 성능 면에서 대단히 불행한 선택이라고 할 수 있습니다. 특히 RAC와 같은 분산 데이터베이스 환경에서는 NOCACHE 속성의 시퀀스가 미치는 악영향은 치명적일 수 있습니다. 명심할 것은 시퀀스 값의 누락은 절대 피할 수 없다는 것입니다. NOCACHE 속성을 부여하더라도 시퀀스 값의 누락은 항상 발생합니다. 가령 50만건의 데이터의 키 값을 시퀀스를 이용해서 생성하다가 파일 공간 부족으로 롤백이 발생했다면, 이 50만개의 시퀀스 키 값은 누락된 상태가 됩니다. 이런 이유로 NOCACHE 속성의 시퀀스는 절대 사용하지 않는 것을 원칙으로 할 것을 권장합니다.

CACHE + ORDER 속성이 부여된 시퀀스인 경우에 오라클은 SQ lock이 아닌 SV lock을 사용해서 동기화를 수행합니다. 즉, Order속성이 부여된 Sequence에 대해서 nextval을 호출하면 SSX 모드로 SV lock을 획득해야 합니다. SV lock을 획득하는 과정에서 경합이 발생할 경우는 row cache lock 이벤트나 enq: SQ-contention 이벤트와는 전혀 다른 DFS lock handle 이라는 이름의 이벤트를 대기하게 됩니다. 이런 이유로, V%EVENT_NAME 뷰에는 "enq: SV-contention"과 같은 이름의 이벤트가 존재하지 않습니다.

gc cr failure

gc Block을 전송받는 과정에서 실패가 발생했음을 의미하는 Fixed-up Event입니다. 이는 보통 Block 손실, 잘못된 Block을 요청하여 프로세스가 요청에 대한 처리를 할 수 없었다고 볼 수 있습니다.

보통 gc cr failure 대기 이벤트가 발생하기 전에 gc cr request와 같은 이벤트가 발생하는 경향이 높습니다.

Parameter

gc cr failure 이벤트와 같은 Fixed-up 이벤트는 P1, P2, P3 값이 별도로 부여되지 않으며, Placeholder 이벤트(여기서는 gc cr request 이벤트)와 동일한 값을 가지는 것으로 해석하면 됩니다.

* gc blocks lost
블록 전송 과정에서 유실(Lost)된 블록의 수를 의미합니다. 블록 유실은 RAC 시스템의 성능에 결정적인 영향을 미치기 때문에 gc blocs lost 통계 값은 가능한 낮은 수치를 유지해야 합니다. 만일 gc blocks lost 통계 값이 증가한다면 다음과 같은 확인 절차를 거쳐야 합니다.

네트워크 설정이나 하드웨어 설정에 이상이 없는가를 점검합니다. netstat과 같은 툴을 이용해서 네트워크 패킷 에러가 발생하지 않는지 점검하고, 네트워크 파라미터가 지나치게 작게 설정되어 있지 않은지 확인합니다. 네트워크 버퍼 크기를 크게하고, MTU의 크기를 크게 하는 것 등도 해결 방법이 될 수 있습니다.

잘못된 네트워크 프로토콜을 사용하고 있지 않은가를 점검합니다. 대부분의 OS에서 오라클은 UDP를 기본 프로토콜로 사용할 것을 권장합니다. 많은 종류의 프로토콜에 대해 RAC 성능 테스트와 적용이 이루어졌지만, UDP에서 가장 안정적으로 작동하는 것이 경험적으로 검증되었습니다. 만일 특정 벤더가 제공하는 특정 프로토콜을 사용한다면 반드시 오라클로부터 검증을 거쳐야 합니다.

지나치게 인터커넥트 부하가 높은 경우에는 패킷 유실을 피할 수 없습니다. 이 경우에는 네트워크 대역폭을 높이거나 SQL/애플리케이션 튜닝을 통해 블록 전송수를 줄여야 합니다.

buffer busy waits

특정 블록(Block)을 액세스하는 프로세스는 해당 블록에 대해 Buffer Lock을 획득해야 합니다. 가령 특정 블록을 변경하려면 해당 블록에 대해 Buffer Lock을 Exclusive 모드로 획득해야 합니다. 반면, 특정 블록을 읽으려면 해당 블록에 대해 Buffer Lock을 Shared 모드로 획득해야 합니다.

프로세스 A가 블록 X에 대해 Buffer Lock을 획득하고 있는 상태에서 프로세스 B가 동일 블록에 대해 호환되지 않는 모드로 Buffer Lock을 획득하지 못하고 대기해야 합니다. 이때 발생하는 대기 이벤트가 buffer busy waits이벤트입니다.

buffer busy waits 이벤트가 가장 흔히 목격되는 경우는 동시에 여러 프로세스가 동일 블록에 대해 Insert를 하거나 Update를 하는 경우입니다. Insert나 Update 작업은 해당 블록에 대해 Buffer Lock을 Exclusive 모드로 획득할 것을 요구합니다. 여러 프로세스가 동시에 동일 블록에 대해 Buffer Lock을 Exclusive 모드로 획득하는 경우에는 Buffer Lock 경합이 발생하게 되고 buffer busy waits 이벤트에 대한 대기로 관찰됩니다.

Wait Time

일반적으로 1초까지 기다립니다. 만일 Exclusive 모드로 Buffer Lock을 획득하기 위해 buffer busy waits 이벤트를 1초 대기하고, 다시 대기하는 경우에는 3초까지 기다립니다. 특정 블록을 읽고자 하는 세션은 반드시 Buffer Lock을 획득해야 합니다.

Parameter

P1(File#), P2(Block#), P3(오라클 10g에서는 블록 클래스(Block Class)를 의미하며, 오라클 9i에서는 Reason Code를 의미)

Common Causes and Actions

원인: 비트맵 블록(블록 클래스=8,9,10)에 대한 경합

진단 방법: 이벤트 발생시 v$session_even, v$session 뷰를 통해 p3 값이 비트맵 블록에 해당되는지 조사한다.

개선 방법: ASSM(Automatic Segment Space Management)를 사용하는 경우에는 세 단계의 비트맵 블록에서 경합이 발생할 수 있습니다. 비트맵 블록에서 발생하는 블록 경합은 오라클 튜닝을 통해 개선할 수 없으며, 애플리케이션의 수정을 통해 과도한 동시 DML을 줄여야 합니다.

ASSM은 대규모의 동시 DML에 있어서는 FLM에 비해 약간의 오버헤드를 가지고 있습니다. 하지만 대부분의 경우 이러한 오버헤드는 무시할 정도입니다. ASSM은 FLM에 비해 여러가지 장점을 제공하며 특히 RAC에서 최적의 성능을 제공합니다.

원인: 데이터 블록(블록 클래스=1)에 대한 경합

진단 방법: 이벤트 발생시 v$session_even, v$session 뷰를 통해 p3 값이 데이터 블록에 해당되는지 조사한다.

개선 방법: 사용자 애플리케이션이 동시에 여러 세션이 동일 블록을 변경하게끔 동작하는 경우 이러한 블록을 흔히 핫 블록(Hot Block)이라고 부른다. 핫 블록을 해소하는 방법들에는 다음과 같은 것들이 있습니다.

- 사용자 애플리케이션의 수정: 애플리케이션 수정을 통해 동시에 여러 프로세스가 동일 블록을 변경하지 않게끔 한다.

- PCTFREE 값의 조정: PCTFREE값을 크게 해서 오브젝트를 생성하면 하나의 블록에 들어가는 로우 수가 줄어들고 그만큼 블록 경합이 줄어든다.

- 우편향 인덱스의 변경: 인덱스의 키가 우편향되어 있으면 인덱스 리프 노드에 대한 블록 경합이 발생할 수 있다. Sequence 값을 이용해 생성된 키 값이 인덱스로 쓰이는 경우가 대표적이다. 리버스 인덱스(Reverse Index)를 이용해 키 값을 분산시키면 블록 경합을 줄일 수 있다.

- FLM을 사용하는 경우: FLM(Free List Management)를 사용하는 경우에는 오브젝트의 Storage 속성 중 FREELISTS 속성 값?? 을 충분히 크게 주어야 한다. FREELISTS의 값이 1이면 동시에 여러 프로세스가 동일 블록에 Insert를 수행하기 때문에 블록 경합이 생길 수 있다. FREELISTS 속성 값은 동시에 Insert를 수행하는 프로세스의 최대 개수만큼 부여하는 것이 좋다.

- 파티셔닝: 세그먼트 파티셔닝을 통해 블록을 임의로 분산할 수 있다. 가령 해시 파티션(Hash Partition)과 같은 기법을 통해 하나의 블록에 모여 있던 블록들을 물리적으로 임의의 공간에 분산시키면 블록 경합을 줄이는 효과를 얻을 수 있다.

원인: 세그먼트 헤더 블록(블록 클래스=4)에 대한 경합

진단 방법: 이벤트 발생시 v$session_event, v$session 뷰를 통해 p3 값이 세그먼트 헤더 블록에 해당되는지 조사한다.

개선 방법: FLM을 사용하는 경우, Storage 속성 중 FREELISTS 속성 값을 충분히 크게 해준다. 만일 OPS나 RAC 환경이라면 FREELIST GROUPS 속성 값을 인스턴스의 개수와 동일하게 부여한다. FREELIST GROUPS 속성 값을 부여하면 세그먼트 헤더 블록과 별도로 프리리스트 블록을 사용하기 때문에 그만큼 세그먼트 헤더 블록의 경합이 줄어든다.

원인: 언두 헤더 블록(블록 클래스=15+2*r)에 대한 경합

진단 방법: 이벤트 발생시 v$session_event, v$session 뷰를 통해 p3 값이 언두 헤더 블록에 해당되는지 조사한다.

개선 방법: AUM(Automatic Undo Management)를 사용하는 경우에는 언두 헤더에서의 블록 경합은 잘 발생하지 않는다. 수동 모드의 롤백 세그먼트를 사용하는 경우에는 롤백 세그먼트의 개수를 충분히 키워줌으로써 언두 헤더 블록에 대한 경합을 줄일 수 있다.

* buffer busy wait와 reason code

Buffer Busy Wait Event의 경우, 어디에 경합이 발생했는지까지도 P3 파라미터를 통하여 알 수 있습니다. 따라서 오라클 버전별로 P3가 의미하는 Reason Code와 Block Class에 대해 간략하게 정리해 보도록 하겠습니다.

오라클 9i와 8i에서 제공하는 Reason Code의 종류와 의미는 아래 표와 같습니다. 괄호 안의 Reason Code는 오라클 8.1.5 이하에서 사용되는 값입니다.

Reason Code	설명
100(1003)	블로킹(blocking)세션은 버퍼 캐쉬로 블록을 적재하는 중이며, 롤백을 위한 Undo블록일 가능성이 높다. 해당 정보를 이용하여 새로운 버전의 블록을 생성하기 위해, 배타적인(exclusive) 액세스를 하려는 세션은 대기해야 한다.
110(1014)	대기 세션은 블로킹 세션이 버퍼 캐쉬로 적재하고 있는 블록에 대한 현재(current) 이미지를 읽거나, 기록하려고 한다.
120(1014)	하나 이상의 세션이 버퍼 캐쉬에 존재하지 않는 블록을 액세스하려고 할 경우, 하나의 세션이 db file sequential read 또는 db file scattered read 이벤트를 발생시키면서 I/O 작접을 수행하는 동안, 다른 세션들은 해당 원인코드를 가지고 buffer busy waits 이벤트를 발생시킨다.
130(1013)	하나 이상의 세션이 버퍼 캐시에 존재하지 않는 블록을 액세스하려고 할 경우, 하나의 세션이 db file sequential read 또는 db file scattered read 이벤트를 발생시키면서 I/O 작업을 수행하는 동안, 다른 세션들은 해당 원인코드를 가지고 buffer busy watis 이벤트를 발생시킨다.
200(1007)	블로킹 세션이 버퍼 캐쉬 안의 블록을 변경하는 동안, 새로운 버전의 블록을 생성하기 위해 해당 블록에 배타적인(exclusive) 액세스를 해야 하는 세션은 대기해야 한다.
210(1016)	블로킹 세션이 블록을 변경 중일 때 배타적인(exclusive) 모들르 블록의 현재(current) 버전을 원하는 세션은 대기해야 한다. 두 개의 세션이 동일한 블록을 변경하려고 할 때 발생한다.
220(1016)	블로킹 세션이 블록을 변경 중일 때 현재(current) 모드로 블록을 액세스하려는 세션은 대기해야 한다.
230(1010)	블로킹 세션이 블록을 변경 중일때 해당 블록을 공유(shared) 모드로 액세스하려는 세션은 대기해야 한다.
231(1012)	블로킹 세션이 블록을 변경 중일 때, 해당 블록의 현재(current) 버전을 읽고 있는 세션이 해당 블록에 대한 공유(shared) 액세스를 하려고할 경우에 대기해야 한다.

free buffer waits

free buffer waits 대기 이벤트는 버퍼 캐시 내부에 데이터 블록을 읽어 들이거나, CR(consistent read) 이미지를 생성하기 위한 프리 버퍼(free buffer)를 찾지 못할 때 발생합니다. 이것은 버퍼 캐시가 너무 작거나, 더티(dirty) 블록들을 디스크로 기록하는 작업이 추분히 빠르지 못하다는 것을 의미합니다. 버퍼 캐시 내의 프리버퍼를 찾지 못한 프로세스는 DBWR 프로세스에 요청신호(더티버퍼를 디스크에 기록해달라는 신호)를 보낸 후 free buffer waits 대기 이벤트를 대기합니다.

Free buffer가 필요한 포그라운드 프로세스는 정의된 임계치까지 lru(least recently used) 리스트를 스캔합니다. 임계치는 lru 리스트의 특정 퍼센트만큼 지정되며, 오라클 9i에서의 임계치는 40%입니다. 이 수치는 X$KVIT(kernel performance information transitory instance parameters) 뷰의 "Max percentage of lru list foreground can scan for free"로 표시됩니다. 만일 임계치에 도달할 때까지 프리 버퍼를 찾지 못하면, 포그라운드 프로세스가 해당 작업을 수행하는 동안, 오라클 세션은 free buffer wiats 대기 이벤트를 대기합니다.

오라클은 모든 프리 버퍼 요청에 대한 횟수를 기록합니다. 통계정보 명은 V$SYSSTAT 뷰의 free buffer requested입니다. 오라클은 프리 버퍼 요청에 대한 실패 횟수도 기록합니다. 이것은 free buffer wiats 이벤트의 TOTAL_WAITS에서 찾아볼 수 있습니다. 프리 버퍼 요청은 기술적으로는 buffer gets이고, 프리 버퍼 요청 실패는 buffer misses라 할 수 있습니다. V$SYSSTAT의 free buffer inspected 통계정보는 오라클 프로세스가 요청된 프리 버퍼를 획득하기 위해서 얼마나 많은 버퍼를 검색했는지를 알려줍니다. 만일 free buffer inspected 수치가 free buffer requested보다 월등히 높은 경우는, 프로세스들이 가용한 버퍼를 획득하기 위해 lru 리스트를 그만큼 더 검색했다는 것을 의미합니다. 아래의 쿼리는 시스템 레벨의 free buffer requested, free buffer inspacted 및 free buffer waits의 통계정보를 보여줍니다.

select *

from v$sysstat

where name in ('free buffer requested', 'free buffer inspected');

STATISTIC# NAME CLASS VALUE

---------------- ------------------------ -------------- -------------------

75 free buffer requested 8 3, 311, 443, 932

79 free buffer inspected 8 108, 685, 547

select *

from v$system_event

where name = 'free buffer waits';

EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT

----------------- ------------- ---------------- ------------- -----------

free buffer waits 30369 15795 2187602 72.03404479

Wait Time

가용 프리버퍼를 위해 1초까지 대기하며, 타임아웃 발생 후 또 다시 1초를 대기한다.

Parameter

P1(오라클이 버퍼캐쉬로 읽어 들이려는 블록에 해당되는 File#)

P2(오라클이 버퍼캐쉬로 읽어 들이려는 Block#)

P3(오라클이 10g 이전에서는 사용되지 않았다. 오라클 10g부터 lru, lruw 리스트의 SET=ID#를 나타낸다)

Common Causes and Actions

원인 : 비효율 SQL문

진단 방법 : V$SQL 및 DBA_HIST_SQLSTAT 테이블 등에서 비효율 SQL이 존재하는 확인한다.

개선 방법 : V$SQL 및 DBA_HIST_SQLSTAT 테이블 등에서 DISK I/O가 높은 SQL을 추출하여, BLOCK I/O 횟수를 최소화한다. 더불어 INDEX FAST FULL SCAN 및 Full Table Scan 등의 경우, DIRECT PATH I/O를 고려해 BUFFER CACHE를 우회하도록 한다.

원인 : 불충분한 DBWR 프로세스 수

진단 방법 : LGWR 프로세스가 로그 버퍼의 공간을 확보하는 역할을 수행하는 것처럼, DBWR 프로세스는 lru 리스트상의 프리 버퍼를 확보하는 역할을 수행한다. DBWR 프로세스는 버퍼 캐시의 "working set"과 관계가 있다. "working set"은 lru와 lruw 리스트로 구성된다. 만일 하나의 DBWR 프로세스만 존재한다면, 모든 "working set"에 대한 서비스를 하나의 DBWR 프로세스가 수행해야 한다. 다수의 DBWR 프로세스가 존재한다면, 오라클은 "working set"을 균등하게 분배하게 된다.

당연히, 더 많은 DBWR 프로세스는 더욱 효율적으로 "working set"을 서비스할 수 있고 더 높은 처리량을 보장할 수 있다. 아래의 X$KCBWDS(kernel cache buffer working set descriptors) 뷰의 결과는 8개의 "working set"과 2개의 DBWR 프로세스가 있음을 나타낸다.

개선 방법: 많은 사람들이 비동기식 I/O 환경에서는 하나의 DBWR 프로세스를 사용하라는 이야기를 들은적이 있을 것이다. 이것은 단지 가이드라인에 불과하며, free buffer waits 대기이벤트가 발생하는 경우에는 비동기식 I/O 환경에서도 다수의 DBWR 프로세스를 사용할 수 있다. CPU_COUNT에 따라서 DB_WRITER_PROCESSES 파라미터를 조정하여 DBWR 프로세스의 개수를 증가시킬 수 있다. 하지만, SQL문이 최적화 되지 않아서 다수의 버퍼를 사용할 경우에는 별다른 효과가 없게 된다.

게다가 다수의 DBWR 프로세스를 사용하면, DBWR 체크 포인트 횟수가 증가함에 따라 free buffer waits 발생 횟수를 감소 시킬 수 있다. 오라클 9i에서, FAST_START_MTTR_TARGET 파라미터를 이용하여 MTTR(Mean Time To Recovery)을 단축하는 방법으로도 동일한 효과를 낼 수 있다. 더 짧은 MTTR은 DBWR 프로세스의 활동성을 높이게 하여, 결과적으로 클린 버퍼(clean buffer)의 공급을 더 원활하게 만든다. 하지만, DBWR 프로세스의 활동성을 높이는 것은 디스크로 전송되는 블록을 변경하려는 프로세스들이 write complete waits 대기이벤트를 대기할 가능성을 높이게 된다. 따라서 애플리케이션의 유형에 따라 적절한 균형점을 찾아야 한다.

원인 : 느린 I/O 서브 시스템

진단 방법 : DBWR 프로세스에서 db file parallel write의 대기시간이 길지 않은지 확인한다.

개선 방법 : DBWR 프로세스에서 db file parallel write 대기시간이 길어지면, 서버 프로세스는 연쇄적으로 free buffer waits 대기나 write complete waits 대기를 겪게 된다. 로디바이스(Raw Device)와 AIO(Asynchronous IO, 비동기 I/O)를 조합해서 사용하는 것이 I/O 성능을 개선시키는 가장 좋은 방법으로 알려져 있다. 로디바이스가 아닌 경우라도 AIO를 지원하는 시스템이 있으므로 이를 활용할 수 있다. AIO를 사용하는 경우, DBWR 프로세스의 개수를 늘리는 것은 의미가 없다는 의견이 많다. 복수개의 DBWR 프로세스를 사용하는 목적이 결국 AIO를 소프트웨어적으로 흉내내는 것이기 때문이다. 하지만 Write가 매우 많은 시스템이라면 AIO와 복수개의 DBWR 프로세스를 동시에 구성하는 것도 가능하다. OS차원에서 Direct I/O를 사용하는 것도 성능에 큰 도움이 된다. Direct I/O를 사용하는 경우에는 로디바이스를 사용할 필요가 없다는 의견도 있다. Direct I/O를 사용하는 경우에는 CPU 개수가 충분하다면, DB_WRITER_PROCESSES 값을 조정해서 DBWR 프로세스의 개수를 증가시키는 것을 병행할 수 있다. 오라클의 기본 DBWR 개수는 CPU_COUNT/8이다.

원인 : Delayed Block Cleanouts

진단 방법 : 실질적으로 delayed block cleanout에 대해 어느 정도 발생할지 예측하기는 어렵다. 다만 delayed block cleanout과 free buffer waits 대기 이벤트를 감소 시키기 위해, DML이 자주 발생하는 테이블에 대해서는 애플리케이션 수행전 Full Table Scan을 한다면, 경합을 줄일 수 있다.

테이블이 버퍼 캐쉬로 적재된 후에, 애플리케이션에서 해당 테이블을 사용하기 전에 테이블에 대한 Full Table Scan 오퍼레이션을 수행할 필요가 있다. 이러한 이유는 해당 데이터 블록을 처음으로 읽는 프로세스가 delayed block cleanout을 수행해야 하며, 이것은 free buffer waits 대기이벤트를 야기시킨다. 따라서, 애플리케이션에서 해당 테이블을 사용하기 전에 Full Scan을 수행함으로써 애플리케이션에서 발생할 free buffer waits 대기이벤트를 제거할 수 있게 된다. 아래의 예제 테스트를 이용하여 오라클 9i에서의 처리 방법을 알 수 있을 것이다.

FAST_START_MTTR_TARGET을 5초로 낮게 설정한다. 이것은 DBWR가 화랍ㄹ하게 더티 버퍼를 디스크에 기록하게 하여 향후에 해당 블록에 대한 쿼3리를 수행할 경우 반드시 delayed block cleanout을 수행하도록 한다.

인덱스 없는 테이블에 많은 양의 레코드(약 150만 건 정도)를 insert한 후 커밋을 수행한다. 데이터베이스를 셧다운(shutdown)한 후 재구동한다. 테이블의 레코드를 카운트해보자. 오라클 프로세스는 해당 테이블에 대한 Full Table Scan 오퍼레이션을 수행할 것이다. V$SESSION_EVNET 뷰를 조회하여 이 세션을 찾아보자. delayed block cleanout으로 인해 free buffer waits 대기이벤트를 대기하고 있음을 확인할 수 있을 것이다.

4번과 5번의 과정을 반복해 보자. 하지만, 이번에는 old ITL( Interested Transacation List )들이 정리되어 free buffer waits 대기 이벤트가 발생하지 않는다.

delayed block cleanout과 commit cleanout - 트랜잭션은 버퍼 캐쉬 내의 많은 블록들을 더티로 만들 수 있다. DBWR 프로세스는 다양한 간격으로 커밋 블록과 커밋되지 않은 블록들을 데이터파일에 기록한다. 트랜잭션이 커밋 되었을때, 오라클 프로세스는 DBWR 프로세스에 의해 디스크로 기록되지 않은 블록에 대해서 commit cleanout을 수행한다. 이미 디스크에 기록된 블록들은 해당 블록을 처음 읽는 프로세스에 의해 정리(cleaned)된다. 이것을 delayed block cleanout이라고 한다.

* Buffer 적재 과정

- 사용자가 요청한 블록에 대해 해시 함수를 적용해서 적절한 해시 버킷을 찾는다.

- 해시 커밋에 연결된 해시 체인(Hash Chain)을 탐색해서 블록에 해당하는 버퍼 헤더가 존재하는지 확인한다. 버퍼 헤더가 이미 존재하고 해당 블록이 버퍼 캐시에 올라와 있는 상태라면 해당 블록을 사용한다.

- 아직 버퍼 캐시가 존재하지 않으면 우선 lru 리스트를 가장 덜 사용된 순서로 프리 버퍼를 찾는다. 이 과정에서 더티 버퍼가 발견되면 lru 리스트로 이동시킨다. 프리 버퍼를 찾게 되면 데이터 파일로부터 블록을 해당 버퍼로 읽어 들인다.

- lru 리스트에서 프리 버퍼를 찾을 때 _DB_BLOCK_SCAN_PCT(기본값 40) 파라미터 값만큼 스캔을 하고도 프리 버퍼를 찾지 못하면 오라클은 스캔을 멈추고 DBWR에게 더티 블록을 디스크에 기록하고 프리 버퍼를 확보할 것을 요청한다. 쓰기가 완료되면 해당 버퍼를 사용한다.

저작자표시

'데이터베이스(DA, AA, TA) > Oracle' 카테고리의 다른 글

[오라클] 트러블 슈팅 팁(1) (0)	2018.02.25
[오라클] 클러스터 클래스 대기 이벤트(Placeholder/Fixed-up) (0)	2018.02.17
[오라클] Network 성능 튜닝 (0)	2018.02.15
[오라클] RAC 튜닝 방법(2) (1)	2018.02.15
[오라클] RAC 튜닝 방법(1) - gc cr/current block 2-way/3-way (0)	2018.01.07

길은 가면, 뒤에 있다.

[오라클] RAC 튜닝 방법(3) - DFS lock handle

'데이터베이스(DA, AA, TA) > Oracle' 카테고리의 다른 글

티스토리툴바