Library Cache Lock & Pin

라이브러리 캐시

• SQL 실행에 필요한 모든 객체(SQL, Table, View, Procedure)에 대한 정보를 관리하며, 모든 Session이 접근 가능하도록 Shared pool에 저장한다.
• Hash Table -> Hash Bucket -> Hash Chain -> Handle -> Object로 구성

• • Library Cache 객체 = Handle + LCO
  • Handle = Name + 메타정보
  • LCO(Library Cache Object)
    • Dependency Table:
      • 현존 LCO(SQL문)가 의존하는 LCO(Object LCO)들에 대한 정보
      • 예) SQL문장은 해당 문장이 참조하는 테이블, 뷰 등에 대해 의존성(dependency)를 가짐
    • Child Table:
      • 현재 LCO의 자식 LCO들에 대한 정보
      • 프로시저, 테이블과 같은 객체에 대해 스키마명을 항상 같이 저장하여 유일성 보장
      • SQL텍스트를 갖는 부모LCO를 생성하고, 실제 SQL커서에 대한 정보는 자식LCO에 저장
      • 예) 다른 객체에서 같은 이름의 테이블을 읽으려고 할 때, scott, system 두 계정에 동일한 스키마인 emp테이블이 있고, select * from emp where empno=7788; 한다면, SQL텍스트에 해당하는 부모LCO와 스키마A가 수행한 자식 LCO, 스키마B가 수행한 자식 LCO 총 세 개의 LCO가 생성.
    • Data Blocks:
      • LCO가 포함하는 실제 데이터를 저장하는 청크 영역들에 대한 포인터 정보.
      • 예) SQL커서의 경우 SQL문장, 실행계획, 실행문맥정보등은 특정 메모리 영역(청크)에 보관, 청크들의 주소 값이 LCO의 Data Blocks 영역에 관리.
      • SQL Cursor: SQL문장, Parse Tree 및 실행계획
      • PL/SQL: Source, Diana, P-code, errors
    • Authorization Table: 권한이 부여된 유저에 대한 정보
• Library Cache Lock는 Handle을 보호하기 위해, Library Cache Pin는 LCO를 보호하기 위해 사용.

라이브러리 캐시 Lock

Library Cache Lock은 Library Cache Object(LCO)의 정의(Specification)를 보호하는 Lock이다. LCO에 접근 또는 변경할 때, LCO의 핸들을 획득하는 Lock이다.
LCO의 정의를 참조하는 작업이 대부분 파싱 단계에서 발생하기 때문에 Parse Lock이라고도 부른다.

이 Lock은 아래와 같은 세가지 모드를 사용한다.

• Shared 모드: 읽기 작업 시
• Exclusive 모드: 생성 또는 변경 작업 시
• Null 모드: Lock을 장시간 유지하려 할 때 사용. 실제 대기를 발생시키지는 않으며, 오브젝트간 의존성(dependency)을 관리하는 데 사용.

각 Lock모드의 상호 호환성

Stored Object(테이블, 인덱스, 뷰, 트리거, 함수/프로시저, 패키지 등)는 상황에 따라 이 세가지 중 하나를 Lock 모드로 사용한다.

Library Cache Lock이 필요로 하는 작업들

• 일반 SQL을 파싱하는 세션은 파싱 시간동안 해당 SQL문장과 SQL문장이 참조하는 오브젝트의 LCO에 대해 Library Cache Lock를 Shared모드로 획득해야 한다. (파싱 시간동안 오브젝트의 정의가 변경되면 안되므로)
• DDL문장을 수행하는 세션은 수행이 완료될 때까지 해당 오브젝트의 LCO에 대해 Library Cache Lock을 Exclusive모드로 획득해야 한다. (DDL 수행되는 동안 해당 객체에 대한 어떤 종류의 참조도 원천적으로 방지해야 하므로)
• SQL문장의 파싱이 완료한 세션을 SQL커서 및 커서가 참조하는 LCO에 대해서 Library Cache Lock을 해제하지 않고, Null모드로 계속 유지한다. 개별 서버프로세스는 PGA공간에 LCO를 캐싱할 수 있는데, 만일 DDL에 의해 커서가 참조하는 객체가 무효화되면, 개별적으로 보유한 캐싱정보 또한 무효화 되어야 하기 때문이다.

예) 특정 SQL문을 실행한다면?

• 해당 SQL문에 해당하는 library cache 객체에 대해 파싱을 수행하는 동안은 Shared 모드로 library cache lock을 획득
• 파싱이 끝나고 나면 Null모드로 변환
• Shared모드로 lock을 획득한다는 것은 여러 개의 프로세스가 동시에 같은 SQL문을 수행할 수 있다는 의미가 되므로, 동일 library cache 객체에 대해서 파싱과 수행만 하면 library cache lock이나 library cache pin과 관련된 대기는 발생하지 않는다.
  단, 그런 경우도 latch와 lock의 획득 자체는 필요하므로 파싱과 수행이 과다한 경우는 shared pool latch와 library cache latch와 관련된 대기가 발생하게 된다.

예) create or replace procedure명령을 이용해서 프로시저를 생성/변경하는 프로세스라면?

• 프로시저에 해당하는 library cache 객체에 대해 library cache lock을 Exclusive하게 획득해야 한다.(Table Alter도 동일)
• 따라서 많은 세션이 특정 table에 대해서 select를 수행하는 중에 해당 table에 대해 alert작업을 하는 경우 library cache lock대기로 인해 select세션의 성능저하가 발생할 수 있다.

(질문) SQL문이나 프로시저 수행 시 library cache객체에 대해서 library cache lock을 Shared 모드로 획득한 후, 완전히 해제하지 않고, Null모드로 변환하고 해당 Lock을 계속 보유하는 이유는?

답) Library Cache객체의 Invalidation을 자동화하기 위해서.
해설) SQL Cursor와 같은 경우는 해당 Cursor가 참조하고 있는 객체가 변하면 자동으로 Invalidation되어야 한다. SQL Cursor는 자신이 참조하는 모든 객체에 대해서 library cache lock을 Null모드로 획득하고 있다. 따라서 해당 객체에 대해 DDL문이 수행되면 Null모드로 획득되어 있는 library cache lock 정보를 참조해서 관련된 library cache 객체를 Invalidation하게 된다. 이러한 이유로 Oracle의 Concept Manual에서는 library cache lock을 Breakable parse lock이라고 부른다.

라이브러리 캐시 Pin

Library Cache Pin은 LCO의 실행 정보를 보호한다.
라이브러리 캐시 Pin은 Library cache객체에 대한 수행이나 변경 시 library cache object(LCO)에 대해 획득하는 Lock이다. Library cache pin은 library cache lock을 획득한 후에 library cache 객체에 대해 추가적인 작업이 필요할 때 획득하게 된다.
예를 들어, 프로시저를 컴파일 하는 경우에는 library cache pin을 Exclusive하게 획득해야한다. 또한, 하드파싱이 일어날 경우도 해당 SQL Cursor에 대해서 library cache pin을 Exclusive하게 획득한다.

SQL문의 실행단계

• Parse 단계 : Library Cache영역을 탐색해서 동일한 SQL문이 존재하는지 확인한다. 동일한 SQL문인지 판단하는 동안 Library Cache Lock을 Shared 모드로 획득한다. 파싱이 끝나면 Library Cache Lock을 Null모드로 다운그레이드 한다.
• Execute 단계: SQL 커서에 해당하는 LCO에 대해 Library Cache Pin을 Shared 모드로 획득한 후 실행계획을 읽고 실행한다. 실행이 끝나면 Library Cache Pin을 해제한다. LCO를 실행하는 동안 LCO의 변경이 생기는 것을 방지하기 위한 메커니즘을 Library Cache Pin을 통해 구현한 것으로 이해할 수 있다.
• Fetch 단계 : 데이터를 실제로 추출한다. 이 단계에서 주로 Logical Reads와 Physical Reads등이 발생한다.

Library Cache Pin을 획득하는 경우

• SQL문을 수행하는 세션은 SQL커서에 해당하는 LCO에 대해서 Library Cache Pin을 Shared모드로 획득한다. 실행시간동안 LCO의 실행정보가 변경되는 것을 방지하기 위해서다. 참조하는 LCO에 대해서도 Library Cache Pin을 Shared모드로 획득한다.
  수행(Execute)이 끝나면 Library Cache Pin은 즉각 해제된다.
• 하드파싱이 발생하는 경우에는 SQL커서에 해당하는 LCO에 대해 Library Cache Pin을 Exclusive모드로 획득한다. 하드파싱이 진행되는 동안 참조하는 LCO에 대해서는 Library Cache Pin을 Shared모드로 획득한다. 이런 이유로 현재 하드파싱이 진행중인 동일한 SQL문장을 수행하고자 하는 다른 세션들은 하드파싱이 완료될 때 까지 대기해야한다.
• 프로시저를 컴파일 하는 경우에도 위와 동일하다.
• 기타 실행정보를 변경해야 하는 모든 DDL작업들은 항상 Library Cache Pin을 Exclusive모드로 획득해야 한다. 예를 들어 GRANT 명령을 이용해 특정 프로시저에 대한 실행권한을 특정사용자에게 부여하는 경우, 이 세션은 실행중인 작업이 종료 될 때까지 GRANT작업은 플로킹된다.

a.	Syntax 체크
b.	Semantic 체크
c.	권한체크
d.	Shared Pool의 동일 SQL 검색
========================================? 소프트 파싱
e.	Parse Tree 생성
f.	Execution Plan 생성
========================================? 하드 파싱

Library cache 객체에 대해서 Library cache lock을 잡는 것은 소프트파싱까지의 단계이며, 하드파싱을 수행하는 동안에는 Library cache lock을 Null모드로 변환하고, Library cache pin을 Exclusive하게 획득한다. 이는 하드파싱이 발생하는 동안 해당 LCO에 대해 변동이 발생하는 것을 막기 위해서다. 하드파싱 단계가 끝나고 나면, library cache pin을 Shared모드로 변환하고 수행 단계로 나아간다.

라이브러리 캐시 Lock과 Pin, 두 개의 직렬화 장치를 따로 두는 이유

Library cache lock은 Handle에 대해 획득하며, Library cache pin은 LCO에 대해 획득한다. 따라서 이론적으로 두 개의 프로세스가 부모 Handle에 대해서 각각 library cache lock을 Shared모드로 획득한 상태로, 두 개의 프로세스가 각각 별도의 Child LCO에 대해서 library cache pin을 Exclusive하게 획득하는 것이 가능하다. 따라서, SQL Cursor와 같이 하나의 논리적 객체에 대해 여러 개의 LCO가 생성되는 경우에 library cache영역에 대한 접근성을 최대한으로 높일 수 있다.
실제 LCO 컨텐트의 정합성은 Pin을 통해 보장되고, 그것과 별도로 Lock을 둠으로써 동시성을 높이기 위해서

문서에 대하여

• 최초작성자 : 오정희
• 최초작성일 : 2010년 1월 18일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.*