Library cache lock

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] Basic Info

Library cache locklibrary cache object(이하 LCO)에 접근 또는 변경 할 때, LCO의 핸들(handle)에 대해 획득하는 락이다. 핸들은 실제 LCO에 대한 메타정보, 포인터 정보를 가지고 있다.

특정 SQL문을 실행하려는 프로세스는 소프트 파싱하는 동안 SQL문에 해당하는 LCO에 대하여 library cache lock을 shared 모드로 획득해야 한다. 소프트 파싱이 끝나고 나면(실행 또는 하드 파싱 단계) library cache lock을 null 모드로 변환한다. 이 때, SQL문에 해당하는 LCO 이외에 SQL이 참조하는 모든 객체에 대해서도 동일한 모드로 library cache lock을 획득한다.

소프트 파싱하는 동안 shared 모드로 library cache lock을 획득한다는 것은 여러 개의 프로세스가 동시에 같은 SQL문을 수행할 수 있다는 의미이다. 즉, 동일 SQL에 대해 소프트 파싱하는 동안에는 library cache lock의 획득자체는 필요하나 이벤트의 대기, 경합은 발생하지 않는다.

소프트 파싱이 끝나면 null 모드로 library cache lock의 획득 모드를 변환하는데, 그 이유는 SQL이 참조하는 LCO에 대하여 무효화(invalidation)을 자동화 하기 위해서이다. SQL 커서와 같은 객체들은 자신이 참조하는 객체가 변경되면 자동으로 무효화가 되어야 한다. 따라서, 해당 객체에 대해 DDL(alter, drop 등)이 수행되면 null 모드로 획득되어 있는 library cache lock 정보를 참조해서 관련된 LCO를 무효화하게 된다. 이러한 이유로 오라클 concept 매뉴얼에 library cache lockbreakable parse lock이라고 부른다.

Create or replace procedure 명령으로 프로시저를 생성/변경하는 프로세스는 프로시저에 해당하는 LCO에 대해 library cache lock을 exclusive 모드로 획득해야 한다.

Table을 변경(alter, create, drop)하는 경우에도 테이블에 해당하는 LCO에 대해 library cache lock을 exclusive 모드로 획득해야 한다. 따라서, 많은 세션이 특정 테이블을 조회(select)하는 중에 해당 테이블에 대해 변경작업을 수행하면, library cache lock 이벤트의 대기로 인해 select 세션의 성능 저하가 발생된다.

[편집] Parameter & Wait Time

[편집] Wait Parameters

Library cache lock 대기이벤트의 파라미터 정보는 다음과 같다.

  • P1 = handle address
  • P2 = lock address
  • P3 = mode*100 + namespace
    • 오라클 7.0부터 8.1.7 까지는 mode*10 + namespace로 표현되며, 오라클 9.0부터는 mode*100 + namespace로 표현된다. namespace 부분은 번호로 표시된다.
< mode >                     
1: Null
2: Shared
3: Exclusive
< namespace > 0: SQL Area 3: Trigger 6: Object 14: Java Resource 1: Table/Procedure/Function/Package header 4: Index 7: Pipe 32: Java Data 2: Package body 5: Cluster 13: Java Source

[편집] Wait Time

PMON 프로세스는 1초까지 대기하며, 다른 프로세스들은 3초까지 대기한다. 해당 대기시간 후에도 락을 획득하지 못할 경우 반복적으로 대기한다.

[편집] Check Point & Solution

[편집] 업무시간 중 DDL문의 수행을 피하라.

Library cache lock 대기에 의한 성능저하현상은 대부분 부적절한 DDL(create, alter, compile, flush 등)에 의해 발생한다. 따라서 트랜잭션이 왕성한 시스템에 대해서 DDL을 수행할 때는 이 내용을 충분히 고려한 후 수행하도록 해야 한다. 간혹 하드 파싱이 왕성한 시스템에서 shared pool 메모리 고갈을 피하기 위해(ORA-4031 에러를 피하기 위해) flush를 수행하는 경우가 있으나 시스템에 악영향을 주는 경우가 많다. 하드 파싱도 나쁘지만, 하드 파싱이 발생하는 도중에 DDL을 수행하는 것은 말할 수 없이 나쁘다.

[편집] Event Tip

[편집] library cache의 구조

latch: library cache#Library Cache 구조를 참조한다.

[편집] SQL의 수행 과정

latch: library cache#SQL 수행을 참조한다.

[편집] Analysis Case

[편집] 1. 부적절한 DDL 수행에 의한 성능 저하 분석 사례

트랜잭션이 왕성한 시스템에서의 부적절한 DDL은 Library Cache에서의 경합을 증가시키고 시스템의 성능을 저하시키는 원인이 되는 경우가 많다. Oracle DBMS의 성능진단/분석 툴인 Maxgauge(맥스게이지)를 활용하여, 부적절한 DDL에 의한 성능 저하 문제의 원인을 규명해 보고자 한다.

[편집] 성능저하구간의 확인

09시36분 구간에서 Active Session 수와 이벤트 대기 시간이 급증하는 것을 확인할 수 있다.

■Active Session수의 추이 그림:Case5_1.jpg

■ 대기시간의 추이 그림:Case5_2.jpg

[편집] 대기이벤트의 검출 및 분석

문제 구간의 대기이벤트를 조회하면, library cache pin, library cache lock, library cache load lock 이벤트 대기와 같은, Library Cache와 관련된 대기현상이 많이 발생한 것을 확인할 수 있다. 그림:Case5_3.jpg

[편집] 대기이벤트 발생원인의 조사

문제 구간의 Active Session 목록을 조회하면 아래 그림과 같이 대부분의 세션들이 library cache pin 이벤트나 library cache lock 이벤트를 대기한다. 그림:Case5_4.jpg

library cache pin 이벤트와 library cache lock 이벤트 발생이 의미하는 것은 DDL에 의해 Library Cache Object가 변경되었다는 것을 의미한다. 아래 그림은 문제 구간에서 DDL을 수행한 세션을 [Session List] 화면에서 캡쳐한 것이다. SQL*Plus에 수행된 특정 세션에서 alter index … nomonitoring 류의 DDL 문장이 계속적으로 수행된 것을 확인할 수 있다. 그림:Case5_5.jpg

[편집] 세션 및 SQL의 분석을 통한 문제원인의 규명

성능 문제가 발생한 정확한 원인을 규명하려면 library cache lock 이벤트와 library cache pin 이벤트 대기가 발생하는 이유를 명확하게 이해할 필요가 있다.

library cache lock 이벤트 대기는 Library Cache Object의 정의를 바꾸거나 참조하는 과정에서 경합이 발생할 때 관찰된다. 가령 alter index … 명령을 수행하는 세션은 인덱스에 해당하는 Library Cache Object에 대해 library cache lock을 Exclusive 모드로 획득해야 한다. 만일 이 때 다른 세션이 해당 인덱스를 경유하는 SQL문을 수행하기 위해 library cache lock을 Shared 모드로 획득 중이라면, alter index … 명령을 수행하는 세션은 library cache lock 이벤트를 대기한다.

library cache pin 이벤트 대기는 Library Cache Object의 실행정보를 바꾸거나 참조하는 과정에서 경합이 발생할 때 관찰된다. 가령 특정 SQL에 대해 최초로 하드파싱을 수행하는 세션은 해당 Library Cache Object에 대해 library cache pin을 Exclusive 모드로 획득한다. 하드파싱이 이루어지는 동안 같은 SQL을 수행하고자 하는 세션들은 library cache pin을 Shared 모드로 획득하기 위해 대기해야 한다. 이때 library cache lock 이벤트를 대기한다.

위의 지식을 바탕으로 문제 구간의 대기 현상을 연관 분석하면, 문제 구간에서 library cache pin 이벤트와 library cache lock 이벤트 대기가 발생하는 원인을 다음과 같이 이끌어 낼 수 있다.

현재 많은 수의 세션들이 SQL을 수행 중이며, 수행하는 동안 SQL 커서 객체 및 SQL이 참조하는 테이블/인덱스에 대해 library cache lock과 library cache pin을 획득한다.

이 때, SQL*Plus에서 수행된 세션이 많은 수의 인덱스에 대해 alter index … nomonitoring 구문을 수행한다.

alter index … nomonitoring 구문을 수행하려면 인덱스에 대해 library cache lock을 Exclusive 모드로 획득해야 한다. 동시에 많은 수의 세션들이 인덱스를 사용하는 SQL문을 수행 중이기 때문에, alter index … nomonitoring 구문을 수행한 세션은 모든 SQL문의 수행이 끝날 때까지 library cache lock 이벤트를 대기한다.

alter index … nomonitoring 구문을 수행하는 세션은 library cache lock을 Exclusive 모드로 획득한 후 작업을 수행한다. 작업이 수행되는 동안 해당 인덱스를 참조하는 모든 세션들은 library cache lock 이벤트를 대기한다. alter index 작업이 끝나면 해당 인덱스를 참조하는 모든 SQL 커서 객체가 무효화(Invalidation)된다.

무효화된 SQL 커서를 최초로 접근하는 세션은 하드파싱을 수행하며, 하드파싱이 수행되는 동안 library cache pin을 Exclusive 모드로 획득한다. 동일 SQL 커서를 실행하고자 하는 모든 세션들은 하드파싱이 끝날 때까지 library cache pin 이벤트를 대기한다.

문제 구간에서 library cache pin 이벤트 대기가 특히 높은 것은 하드파싱과정에서 많은 시간이 소요되었다는 것을 의미한다. 하드파싱과 관련된 두 가지 통계값인 하드파싱 회수(parse count(hard))와 하드파싱 시간(parse time elapsed)을 조회하면 아래 그림과 같다. 그림:Case5_6.jpg

하드파싱 회수는 20회 정도(10회 → 30회) 증가하고, 하드파싱 시간은 최대 20초(2000 cent second)까지 크게 증가하는 것을 확인할 수 있다. 즉, alter index … 구문에 의해 SQL 커서가 무효화되는 과정에서 하드파싱에 많은 시간이 소요되고, 이 과정에서 library cache pin 이벤트 대기시간이 크게 증가하게 된다.

DDL 수행에 의한 library cache lock, library cache pin 이벤트 대기의 발생을 막을 수 있는 방법은 없으며, 트랜잭션이 왕성한 시점에서는 DDL 수행을 수행하지 않는 것만이 유일한 해결책이다.

[편집] 결 론

성능 저하 현상 (library cache lock, library cache pin이벤트 대기) →

부적절한 DDL수행으로 인한 library cache pin, library cache lock 이벤트 대기 발생 →

운영 중인 시스템에서는 부적절한 DDL 수행 삼가



[편집] 2. Library Cache Lock 경합의 원인 추적 사례

맥스게이지 실시간 모니터링 중 15만초 이상 처리가 되지 못하고 대기하고 있는 세션들이 확인되었다.

그림:7_3_1.jpg

Library Cache Lock 은 Library Cache Handle에 대한 락이므로 해당 오브젝트에 대한 조회가 필요하다. 이 정보를 알기 위해, V$SESSION 혹은 V$SESSION_WAIT 뷰를 이용한다. 특히, 10g에서는 V$SESSION에서 V$SESSION_WAIT의 내용을 모두 확인할 수 있다.

select sid, serial#, event, p1text, p1, p1raw, p2text, p2, p2raw, p3text, p3, p3raw
from v$session 
where event like 'library cache lock%'

그림:7_3_2.jpg

v$session 조회 결과 중, P3 컬럼의 301은 Exclusive 모드(3) * 100 + 1(Table/Procedure/Others)를 의미한다. 즉, 테이블/프로시저 등의 것에 변경작업으로 인하여 Library Cache Lock이 발생하였다.

Handle Address인 P1Raw 값이 모두 같으므로, 동일한 오브젝트에 변경작업이 발생하였음을 알 수 있다.

x$kglob 뷰를 이용해 P1Raw 값을 조회하면, 대기가 발생하는 오브젝트를 찾아낼 수 있다.

그림:7_3_3.jpg

이와 같은 방법으로 Library Cache Lock이 발생한 원인을 추적할 수 있다.