[Oracle] 대기 이벤트

• DBMS 내부에서 활동하는 수많은 프로세스 간에서는 상호작용이 필요하며, 그 과정에서 다른 프로세스가 일을 마칠 때까지 기다려야 하는 상황이 발생
• 그때마다 해당 프로세스는 자신이 일을 계속 진행할 수 있는 조건이 충족될 때까지 수면(Sleep)상태로 대기

구분	이벤트명	설 명
라이브러리 캐시 부하	latch : shared pool	라이브러리 캐시에서 SQL 커서를 찾고 최적화 하는 과정에서 경합이 발생하여 나타난 대기이벤트
	latch : library cache	라이브러리 캐시에서 SQL 커서를 찾고 최적화 하는 과정에서 경합이 발생하여 나타난 대기이벤트
	library cache lock	SGA내의 library cache를 참조하기 위한 대기
	library cache pin	수행중인 SQL이 참조하는 오브젝트에 다른 사용자가 DDL문장을 수행할때
데이터베이스 call과 네트워크 부하 애플리케이션과 네트워크 구간에서 소모된 시간에 의해 나타난 이벤트	SQL*Net message from client	Client로부터 다음 명령이 올때까지 idle 상태로 기다릴때 발생
	SQL*Net message to client	메시지를 보냈는데 메시지를 받았다는 신호가 늦게 도착하는 경우, 혹은 클라이언트가 너무 바쁠 경우.
	SQL*Net more data to client	메시지를 보냈는데 메시지를 받았다는 신호가 늦게 도착하는 경우, 혹은 클라이언트가 너무 바쁠 경우.
	SQL*Net more data from client	클라이언트로부터 더 받을 데이터가 있는데 지연이 발생한 경우
디스크 부하 디스크 I/O 발생할 때 나타나는 대기 이벤트	db file sequential read	Single Block I/O. 한번의 I/O call에 하나의 데이터 블록만 읽음. 인덱스 블록을 읽을때 발생
	db file scattered read	Multi Block I/O . Table Full Scan 또는 Index Fast Full Scan 시 나타남
	direct path read	direct path I/O를 수행하기 위해 대기
	direct path write	direct load 작업의 수행으로 인한 대기
	direct path write temp	정렬 작업을 위해 임시 영역을 읽고 쓰는 경우 temp 이벤트를 대기한다.
	direct path read temp	정렬 작업을 위해 임시 영역을 읽고 쓰는 경우 temp 이벤트를 대기한다.
	db file parallel read	여러 개의 데이터 파일로부터 연속되지 않는 싱글 블록들을 동시에 읽어들일 때 발생.
버퍼 캐시 경합 버퍼 캐시에서 블록을 읽는 과정에서 경합이 발생하여 나타나는 대기 이벤트	latch : cache buffers chains	동일 테이블이나 인덱스를 여러 세션이 동시에 스캔
	latch : cache buffers lru chain	다른 테이블이나 인덱스들을 여러 세션이 동시에 스캔
	buffers busy waits	동일 블록에 대한 동시 액세스에 따른 경합
	free buffer waits	free buffer를 할당받기 위해 DBWR의 Write를 대기
LOCK관련 대기 이벤트	enq : TM - contention	DML을 수행할 때 TM 락을 획득하는 과정에서 경합이 발생하여 대기
	enq : TX - row lock contention	로우 레벨 락에 의한 경합이 발생하여 대기
	enq : TX - index contention	동시에 여러 세션이 인덱스가 생성되어 있는 테이블에 대해서 많은 양의 DML을 수행하는 경우에 주로 발생
	enq : TX - allocate ITL entry	ITL 엔트리 부족에 의한 TX락 경합이 발생하여 대기
	latch free	특정 자원에 대한 래치를 여러차례(2000번 가량) 요구했지만 해당 자원이 계속 사용중이어서 잠시 대기 상태로 빠질때마다 발생
기타 대기 이벤트	log file sync	변경 log buffer를 log file에 반영하는 동안 대기
	log file switch completion	현재 사용 중인 redo log file이 꽉 차서 더 이상 여유 공간을 확보할 수 없을 때 발생
	log buffer space	Log buffer를 할당 받기 위해 LGWR의 write를 대기

 

---

1. 라이브러리 캐시 부하

라이브러리 캐시에서 SQL 커서를 찾고 최적화 하는 과정에서 경합이 발생하여 나타난 대기이벤트

• latch : shared pool

shared pool의 heap 영역에서 free chunk를 할당 받으려는 프로세스는 share pool latch를 획득해야 한다.

→ 동시에 여러 세션이 shared pool의 메모리를 사용하려 경합이 발생하는 경우

 

• latch : library cache

library cache chain을 검색하기 위해 library cache latch를 획득하는 과정에서, 경합이 발생하여 나타나는 대기 이벤트.

→ 똑같은 SQL이 여러 세션에서 동시에 수행할 때 library cache latch를 경합

 

---

2. 데이터베이스 call 과 네트워크 부하

SQL*Net 관련 대기 이벤트 -> System API Call 에서 비롯된다.

• 이 이벤트들은 대부분 Idle 이벤트들로 취급되어, 성능 문제를 분석할 때 제외하는 경우가 많음.
• 하지만 특정 상황에서 이 이벤트들이 성능 저하 원인 분석에 있어서 결정적 단서 제공하는 경우 종종 발생함.

 

• SQL*Net message from client

client로부터 다음 명령이 올때까지 idle 상태로 기다릴때 발생

• SQL*Net message to client

메시지를 보냈는데 메시지를 받았다는 신호가 늦게 도착하는 경우, 혹은 클라이언트가 너무 바쁠 경우.

SQL*Net message from client

SQL*Net message to client

• SQL*Net more data to client

메시지를 보냈는데 메시지를 받았다는 신호가 늦게 도착하는 경우, 혹은 클라이언트가 너무 바쁠 경우.

서버 프로세스가 클라이언트에 더 많은 데이터/메시지를 보낸다. 클라이언트에 행한 이전 작업도 보내는 작업 이었다.

• SQL*Net more data from client

클라이언트로부터 더 받을 데이터가 있는데 지연이 발생한 경우

 

--> more : 데이터의 전송량이 많아서 한번에 전송하지 않고 여러 번에 나누어서 한다는 것을 의미

                 more이 붙은 경우 message가 아닌 data라는 용어가 사용된다.

 

---

3. 디스크 부하

• db file sequential read

한번의 싱글 블록 I/O가 발생할 때마다 한번의 db file sequential read 이벤트 대기 발생

싱글 블록 I/O는 파일로부터 하나의 블록을 읽는 모든 작업들에서 발생 가능하며 일반적으로 인덱스 스캔 및 ROWID에 의한 테이블 액세스 시에 발생

 

• db file scattered read

full table scan 및 index fast full scan을 수행하는 경우, 성능 향상을 위해서 여러 개의 블록을 한번에 읽는 멀티 블록 I/O를 수행

멀티 블록 I/O를 한번 수행할 때마다 Physical I/O가 끝나기를 기다리게 되며, db file scattered read 이벤트를 대기

Disk에 있는 데이터 블록을 읽어서 SGA내의 버퍼 캐시 영역으로 로딩한 후, 사용하는 형태

 

• direct path read

Parallel Query 수행 시 Slave Session 이 수행하는 direct path I/O에 의해 발생

direct path I/O : SGA 내의 버퍼 캐시를 거치지 않고 세션의 PGA로 직접 블록을 읽어 들이는 것

full table scan 및 index fast full scan을 수행하는 경우에 나타나는 이벤트

Disk에 있는 데이터 블록을 읽어서 (SGA내의 버퍼 캐시가 아닌) PGA에 로딩한 후, 사용하는 형태

 

 

+) db file scattered read 와 direct path read

두 대기 이벤트 모두 full table scan이나 index fast full scan 하는 경우에 나타나는 이벤트이다.

버퍼 캐시에는 모든 데이터를 올려놓을 수 없기 때문에 자주 쓰는 데이터들만 버퍼 캐시에 남아있어야 한다.

데이터의 크기가 클 경우, db file scattered read로 메모리에 올라가서 다른 데이터들을 밀어내는 것을 방지하기 위해

direct path read로 공유 메모리인 버퍼 캐시를 건드리지 않고, 개인 프로세스 메모리 영역인 PGA에 올려서 다른 프로세스에 영향을 주지 않는다.

 

https://jack-of-all-trades.tistory.com/16

db file scattered read 와 direct path read 의 차이

 

• direct path write

SGA를 거치지 않고 세션 PGA 내부의 버퍼로부터 바로 데이터 파일로 기록할 때 발생

원인 : Direct load 작업(CTAS - Create Table As Select, insert /*+ append*/ ...) 의 수행

 

• direct path write temp
• direct path read temp

정렬 작업을 위해 임시 영역을 읽고 쓰는 경우 temp 이벤트를 대기한다.

정렬 세그먼트에서의 direct path I/O는 정렬해야 할 데이터가 정렬을 위해 할당된 PGA(Program Global Area) 메모리 영역보다 큰 경우에 발생

 

 

+) Sort

SQL에서 데이터를 정렬하기 위해서는 Sort Area(정렬 공간)이 필요하다. Sort Area는 PGA 메모리에 할당된다.

Sort Area(정렬 공간, 정렬 영역) - Order By 또는 Group By 등의 정렬을 수행하기 위한 공간

Sort Area가 부족해지면 디스크의 Temp 테이블 스페이스를 사용하는데, 이 때 direct path write temp / direct path read temp 이벤트로 측정된다.

 

Sort Area가 작을 경우, 데이터를 정렬 하기 위하여 Temp 공간을 사용하게 되며, 이로 인하여 쿼리 성능이 저하될 수 있다.

 

SORT를 메모리에서만 하는지 TEMP 공간을 사용하는지에 따라서 Optimal / One Pass / Multi Pass 로 구분되어 진다.

• Optimal : Sort가 PGA 영역에서 모두 이루어짐
• One Pass  : Sort 영역의 메모리 공간에서 한번에 정렬이 이루어 지지 않아서, Temp영역을 이용하여 한 번의 Merge 작업을 통해 정렬을 완료 함.
• Multi Pass  : Merge 작업이 2회 이상 발생 함. 

https://bstar36.tistory.com/246

Sort시 필요한 메모리 공간 확인

 

• db file parallel read

데이터베이스 복구 수행 시 복구해야 하는 블록들을 여러 개의 데이터 파일로부터 동시에 읽어 들일 때 발생한다. 또한, 하나 이상의 데이터 파일로부터 연속되지 않는 싱글 블록들을 동시에 읽어들이는 Prefetching 시에 발생한다.

Prefetch : 한번에 여러개 Single Block I/O를 동시 수행하는 것. 한번의 I/O Call로 앞으로 읽을 가능성이 높은 블록을 함께 읽어오는 기능.

 

---

4. 버퍼 캐시 경합

Database Buffer Cache에서 블록을 읽는 과정에서 경합이 발생하여 나타나는 대기 이벤트

• latch : cache buffers chains

버퍼 캐시에서 특정 블록을 탐색하고자 하는 (데이터 찾는) 프로세스는 cache buffers chains 래치를 획득해야 한다. 이 과정에서 경합이 발생하면 latch : cache buffers chains 이벤트 대기

chain : 같은 함수값을 가진 블록들을 나열한 것

 

• latch : cache buffers lru chain

버퍼 캐시에서 프리 버퍼와 더티 버퍼를 탐색하려는 프로세스는 cache buffers lru chain 래치를 획득해야 하는데, 이 과정에서 경합이 발생하면 latch: cache buffers lru chain 이벤트 대기한다.

 

 

+) cache buffers chains 래치와 cache buffers lru chain 래치 경합간의 차이

동일 테이블이나 인덱스를 여러 세션이 동시에 스캔

--> cache buffers chains 래치 경합이 발생할 확률이 높다 (동일 체인에 대한 경합)

 

다른 테이블이나 인덱스들을 여러 세션이 동시에 스캔

--> cache buffers lru chain 래치 경합이 발생할 확률이 높다

(여러 세션들이 모두 다른 블록들을 메모리에 올리는 과정에서 프리 버퍼를 확보하기 위한 요청이 많아지고, 이로 인해 Working Set에 대한 경합이 발생할 확률이 높아진다. 특히 데이터의 변경이 빈번해서 더티 버퍼의 개수가 많고 이로 인해 DBWR가 체크 포인트를 위해 lruw 리스트를 탐색하는 횟수가 잦다면 cache buffers lru chain 래치의 경합은 더욱 심해진다.)

 

• buffer busy waits

특정 블록을 액세스하는 프로세스는 해당 블록에 대해 buffer lock을 획득해야 한다.

→ 특정 블록을 변경 하려면 해당 블록에 대해 buffer lock을 exclusive 모드로 획득해야 한다.

→ 특정 블록을 읽으려면 해당 블록에 대해 buffer lock을 shared 모드로 획득해야 한다

 

※ Exclusive Lock과 Shared Lock의 호환성

    Exclusive ←//→ Exclusive ( X )

    Exclusive ←//→ Shared     ( X )

    Shared     ←→  Shared     ( O )

 

동시에 여러 session이 buffer 내의 다른 record를 수정하고자 하는 경우 buffer lock 경합이 발생할 수 있다.

만일 프로세스 A가 블록 X에 대해 Buffer Lock 을 획득하고 있는 상태에서 프로세스 B가 동일 블록에 대해 호환되지 않는 모드로 Buffer Lock 을 획득하지 못하고 대기해야 한다.

이때 발생하는 대기 이벤트가 buffer busy waits 이벤트이다.

ex) 동시에 여러 프로세스가 동일 블록에 대해 insert를 하거나 update를 하는 경우

 

• free buffer waits

free buffer를 만들 때까지 서버 프로세스가 기다리는 것

--> 버퍼 캐시가 너무 작거나, 더티 블록들을 디스크로 기록하는 작업이 충분히 빠르지 못하다는 것을 의미.

원인 : 비효율적인 SQL문, 불충분한 DBWR 프로세스 수, 느린 I/O 서브 시스템, 작은 버퍼 캐시

 

---

5. LOCK 관련 대기 이벤트

DML 락 : Data lock. DML 수행시 데이터를 보호하기 위한 락.

              로우 락(TX)은 특정 로우를 보호하고, 테이블 락(TM)은 전체 테이블을 보호한다.

DDL 락 : Data dictionary lock. USER/TABLE/VIEW/PROCEDURE 등의 정의를 보호한다.

→ 별개의 락이 아니라, 락을 적절히 분류하기 위해 부여한 이름.

 

Enqueue Lock :

공유 리소스에 대한 액세스를 관리하는 LOCK 매커니즘. ( 테이블, 트랜잭션, 테이블스페이스, 시퀀스, TEMP 시퀀스 등)

대기자 큐에 가장 먼저 lock 요청을 등록한 세션이 가장 먼저 lock을 획득한다. 다른 세션이 해당 오브젝트를 변경 중이어서 Enqueue를 획득하지 못하는 경우 'enq:XXX'류의 이벤트를 대기한다.

 

• enq : TM - contention

DML이 수행되는 동안, DML과 관련된 객체에 대한 변경을 방지하기 위해 DML을 수행하는 프로세스는 반드시 해당 테이블에 대해 TM 락을 획득해야 한다. TM 락을 획득하는 과정에서 경합이 발생하면 enq : TM - contention 이벤트를 대기하게 된다.

 

TM LOCK 경합이 발생하는 경우

1. 인덱스가 없는 Foreign key의 부모키를 변경하는 경우
2. DML과 DDL 간의 (부적절한 DDL로 인한) TM 락 경합
3. Lock table .. 을 이용해 의도적으로 TM 락을 획득하는 경우

 

• enq : TX - row lock contention

로우 레벨 락(row level lock)에 의한 경합이 발생하여 enq : TX - row lock contention 이벤트를 대기하게 된다.

 

로우 레벨 락에 의한 경합이 발생하는 경우

1. 여러 세션이 동일 로우를 변경하는 경우

특정 프로세스가 특정 로우를 변경하기 위해 해당 로우를 방문했을 때, 현재 로우가 변경된 상태라면 ITL로부터 해당 로우를 변경한 트랜잭션을 확인하고, 자기 자신을 TX Enqueue 목록에 추가하여 enq : TX - row lock contention 이벤트를 대기한다. 이 대기는 해당 로우에 대해 TX락을 보유한 프로세스가 락을 해제할 때까지 계속된다.

EX) 세션B는 세션A가 이미 변경한 로우를 업데이트하기 원하므로 대기 상태에 들어간다.

2. 여러 세션이 동일 unique key 충돌을 일으키는 경우

Unique Key 또는 Primary Key 충돌이 발생할 때도 TX락 경합이 발생한다.

프로세스 A가 Insert를 수행한 후, 프로세스 B가 Unique Key 충돌이 발생하게끔 Insert를 수행하면, 프로세스 B는 TX락을 Shared 모드로 획득하기 위해 대기한다.

3. 여러 세션이 비트맵 인덱스 충돌을 일으키는 경우

 

• enq : TX - allocate ITL entry

ITL (interested transaction list) : 특정 블록을 변경하고자 하는 트랜잭션의 목록. 블록의 헤더에서 그 정보를 관리한다.

블록을 변경하고자 하는 모든 트랜잭션은 블록 헤더의 ITL의 엔트리 중 하나로 자신을 등록해야 한다. 만일 ITL이 약속된 최대치(MAXTRANS에 의해 지정된 값)를 초과하거나 블록 내의 여유 공간이 부족해서 엔트리를 등록하는 것이 불가능한 경우, 엔트리 등록을 위해 enq : TX - allocate ITL entry 이벤트를 대기한다.

 

+) Row Chaining이나 Row Migration이 발생한 로우의 경우 하나의 로우를 업데이트할 때 여러개의 블록에 대해 각각 ITL 엔트리를 할당해야 한다. 그러므로 ITL 엔트리 부족에 의한 TX락 경합이 발생할 확률이 높아진다.

 

---

6. 그 외 대기 이벤트

• log file sync

서버 프로세스는 commit 명령을 내린 후 LGWR가 (관련 redo record를 redo log buffer에서 redo log file에) 성공적으로 기록을 할 때까지 기다리게 되는데, 이 때 log file sync 이벤트를 대기한다.

일반적으로 log file sync는 평범한 대기 이벤트로 매우 단시간에 발생하지만, log file sync의 과다 현상은 응답 시간을 상당히 지연시킨다.

 

원인 : Commit 과다 발생,

느린 I/O 시스템 성능 - redo log file이 위치한 I/O 시스템의 성능이 느리면 LGWR의 sync write를 수행하는 시간이 늘어나고, 이로 인해 log file sync 대기 시간이 증가할 수 있다.

큰 크기의 redo log buffer - 기록해야 할 데이터의 양이 보다 많아지므로 log file sync의 대기 시간이 증가할 수 있다.

 

• log file switch completion

서버 프로세스가 redo log에 기록하려는 시점에 redo log file이 꽉 차서 더 이상 쓰기를 할 수 없으면 , 프로세스는 LGWR에게 log file switch를 수행할 것을 요청한다.

서버 프로세스는 log file switch가 끝날 때까지 log file switch completion 이벤트를 대기한다.

 

+) log file switch가 끝나는 시점에, 새로 사용할 redo log file에 대해 아직 종료되지 않은 작업이 있다면, 아래의 이벤트를 추가적으로 대기해야 한다.

 

log file switch (checkpoint incomplete)

log file switch (archiving needed)

log file switch (private strand flush incomplete)

 

원인 : redo 데이터의 양에 비해 redo log file의 크기가 작다

개선 방법 : log buffer의 크기를 충분히 크게 한다. direct load operation이나 nologging 옵션을 사용하여 redo 데이터의 양을 줄이는 것도 도움이 된다.

 

• log buffer space

위의 log file switch completion의 경우, 현재 사용 중인 redo log file이 꽉 차서 더 이상 여유 공간을 확보할 수 없을 때 LGWR 프로세스가 log file switch를 수행하여 대기하는 이벤트이다.

그 외의 경우 (여유 공간을 확보할 수 있을 때), 서버 프로세스는 변경 내용을 redo log buffer에 저장하기 위해 공간이 확보되기를 대기하고 이 대기 이벤트를 log buffer space 라 한다.

ex) 오라클에 많은 변경이 생겨 빠른 속도로 redo 데이터가 증가하고, 트랜잭션을 수행하는 세션들이 log buffer space 이벤트를 대기하게 된다.

 

원인 : redo 데이터의 양에 비해 redo log buffer의 크기가 작다

개선 방법 : log buffer의 크기를 충분히 크게 한다.