cursor : pin S wait on X wait event 는 왜 발생할까요?

 

 

 

 

MUTEX, LATCH, ENQUEUE --- 참 어려운 용어고 완전하게 이해하기가 어려운 동네입니다...

이 3가지는 Oracle Kernel 이 동시에 여러 세션들이 동일 data resource (메모리 또는 Segment)에 접근하여 DML 트랜잭션을 일으켰을때 .....

Data의 동기화를 유지하기 위하여 즉, DBMS의 가장 큰 기능인 동일 Data resource에 대한 동시성과 일관성을 유지하기 위하여 사용하는 메커니즘 입니다.

간단하게 동기화 객체입니다....

여기에 하나더....크리티컬섹션(Critical Section)이라는 뮤텍스 보다 더 간단한 동기화객체 방법도 있습니다...(C++ 이나 Java 개발하는 사람들은 프로그램상에서 구현하므로 잘 알 겁니다...)

ORACLE DBMS 도 C언어로 작성되어 있으니.....당연히 객체 동기화를 위하여 뮤텍스나 크리티컬 섹션 기법도 사용했을 겁니다...10g r2 이기는 하지만...)

Latch 와 Enqueue는 Oracle Kernel이 제공하는 메커니즘이고....

Mutex는 OS가 제공하는 메커니즘인 것은 다 아실테고요....

cursor : pin S wait on X wait event ====> 이런 error는 왜 발생할까요?

ORACLE 10g R2(10.2.0.x 버전) 이후 부터는 디폴트로 SGA상의 data resource에 대한 동기화를 위하여 latch 대신에 Mutex 메커니즘을 사용하기 시작하면서 wait event에 다량으로 나타나는 현상입니다...

그럼 왜 Mutex를 사용하는데 이런 error가 발생할까요?

발생할수도 있고 안 할수도 있습니다... (ORACLE DBMS와 OS가 궁합이 안 맞으면 나타납니다....즉 재수가 없으면 나타 납니다...)

그럼 어떤 환경에서 발생하는냐?

어떤 A라는 Process 또는 Thread가 ORACLE DBMS에 접속이 되면 .... 하나의 session 이 발생합니다...

A세션이 DML 트랜잭션을 일으키면 .... ORACLE은 반드시 Cursor 를 SQL 트랜잭션당 1개를 할당합니다....

또한 A 세션이 insert, delete, update와 같은 DML 트랜잭션을 요구하게 되면....SGA상에 Cursor 단위에 해당하는 Object를 exclusive하게 LOCK을 걸게 됩니다...(이때 10g R2 이상에서는 디폴트로 Mutex 동기화 객체 메커니즘을 사용하게 됩니다....)

이때 Cursor Object ( SGA 상의 버퍼캐시임)을 exclusive하게 LOCK걸게하는 것이 바로 Mutex a 입니다...

​

이때 B와 C (Process 또는 Thread) 가 A세션이 잡고있는 동일 Cursor Object를 획득하려고 한다면....먼저 A세션에 속해 있는 Mutex 를 share 모드로 잡고, A세션이 exclusive lock 모드를 해제할 때 까지 기다립니다...

이때 중요한 사항은 Mutex a는 A세션에 종속되어 진다는 것 입니다....

그런데 A세션이 종료되어도 비 정상적으로 A 세션에 종속되었던 Mutex a가 지속적으로 pin 을 하면서 세션 A로 부터 해제가 되지 않으면  ... B와 C 세션은 계속 Mutex a를 잡기위해서 wait 해야 합니다....

이렇게 되면 갑자기 ... 세션의 수가 급격히 증가할 수 있겠죠...(왜냐하면 세션 A가 lock 했던 Buffer cache block을 필요로 하는 세션이 계속 나타날 것이기 때문에....특히 이 block이 Hot Block 이라면 ORACLE DBMS는 바로 Hang-Up이 될 겁니다...)

이런 현상은 10g R2 ORACLE DBMS( c 프로그램의 일종임...ㅎㅎ...) 도 C 프로그램이며, 개발자가 프로그래밍을 잘못해서 Bug 가 나타나는 현상입니다....

그런데 Mutex 메커니즘은 OS가 제공하는 기능이므로...ORACLE Kernel에서 어찌할 방법이 없게되고(통제 불가능 상태).....그래서 ORACLE DBMS가 Hang-Up 비슷한 현상을 일으킵니다...

그래서 나온 해결책이 고작.....Mutex를 동기화를 위한 방법으로 사용하지 않는 것이죠...

spfile 이나 initSID.ora 에서 히든 파라미터를 적용해서 말입니다...

_kks_use_mutex_pin = false 로 설정해서 말입니다... ​

 

우리는 기계적으로 cursor : pin S wait on X wait event ....라는 wait event 가 다량으로 발생하는것이 trace 파일에 나타나면 기계적으로 ​

_kks_use_mutex_pin = false 로 설정해서 해결합니다.... 하지만 원리는 정확하게 알아야 합니다....​​

오라클 메타링크에서 "cursor: pin S wait on X ​" wait를 일으키는 세션을 다음과 같은 쿼리로 찾을 수 있다고 친철하게 얘기하고 있네요.....

문서 ID: 786507.1

제목: HOW TO FIND BLOCKING SESSION FOR MUTEX WAIT EVENT cursor: pin S wait on X

-- 64비트 시스템에서는 8자리, 32비트 시스템에서는 4자리를 취한다.
SELECT p2raw
,to_number(substr(to_char(rawtohex(p2raw)), 1, 8), 'XXXXXXXX') sid
FROM v$session
WHERE event = 'cursor: pin S wait on X';

P2RAW               SID
----------------    --- 

0000001F00000000   31 ​

alter session kill 명령어로 죽여보시기 바랍니다.....

이와같은 Mutex 의 단점이 있는데도 .... 왜 오라클은 10g R2 버전 이후에는 객체 동기화를 위해서 Mutex를 왜 사용할까요?

 

이유는 속도 때문입니다... Latch spin도 어자피 System call을 ORACLE Kernel이 해야 하기 때문인데....Mutex는 OS가 바로 제공하는 메커니즘이니 속도가 빠를 수 밖에 없겠죠...

좀 더 자세하게 얘기하면.... Cursor 실행속도 개선, library cache 내에서의 hard parse time을 줄이기 위한 목적으로 Mutex  메커니즘을 도입했다고 합니다...(오라클 래리엘리슨 창업자가 ... ㅋㅋ)

Mutex 를 사용하면 library cache latch들을 library cache pin으로 대체가 되면서 속도가 개선 됩니다.

 

 

 

 

Mutex 관련 문제로 인하여 나타나는 문제사항의 패턴은

1. 이유없는 Library Cache Lock이 발생한다.

2. 이와 동시에 Cursor Pin S Wait on X Event가 대량으로 발생한다.

3. CPU사용 점유율은 높아지지 않는다. (Parseing Lock이라 그렇습니다.)

4. 특히 Remote DB에 연결하는 SQL구문일수록 그 비중은 높다.

해결방안

1. 근본적으로 대부분의 문제는 Oracle RDBMS의 Bug로 인식해야합니다.

2. Oracle 10G에서는 Mutex를 사용하지 않도록 Hidden Parameter를 적용하면 됩니다.

3. Oracle 11G에서는 11.2.0.2 까지의 버전에서는 Mutex 관련 부분이 매우 불안정하기 때문에 자주 나타날 수 있습니다.

4. Oracle 11.2.0.3 이상부터는 안정화가 되어 있어서 이슈가 거의 없다고 봐도 무관합니다.

5. Oracle 11G의 11.2.0.2 이하의 버전은 언제든 Mutex관련 이슈가 나타날 수 있기 때문에 Patchset Upgrade를 필히 수행하시기 바랍니다.