(1) 라이브러리 캐시 Lock

• 라이브러리 캐시 오브젝트(LCO) 에 대한 핸들을 보호

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

• 상호 호환성(compatibility)

• Stroed Object
• 테이블,인덱스,뷰,트리거,함수/프로시저, 패키지
• 상황에따라 세가지중 하나의 Lock 모드를 사용

첫번째 세션

• Lock을 Exclusive 모드로 수행
  

SQL> create table t (id number, name char(10) );

Table created.


SQL> insert into t

select rownum, lpad(rownum,10,'0') from dual

connect by level <= 1000000;  2    3

1000000 rows created.

SQL> commit;

Commit complete.

SQL> alter table t modify name char(20);

두번째세션

• SQL문 파싱을 위해 LCO핸들에 대해 Shared 모드로 Lock을 요청

SQL> select count(*) from t

 2  ;

• 두번째 세션은 라이브러리 캐시 Lock을 대기
• 두번째 세션의 SID를 확인하고 v$session wait 조회

세번재 세션

SQL> select * from v$session_wait where sid in ('9','12','13');

      SID       SEQ# EVENT

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

P1TEXT                                                                   P1 P1RAW

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

P2TEXT                                                                   P2 P2RAW

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

P3TEXT                                                                   P3 P3RAW     WAIT_TIME SECONDS_IN_WAIT

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

STATE

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

        9       2631 db file sequential read

file#                                                                     1 00000001

block#                                                                58699 0000E54B

      SID       SEQ# EVENT

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

P1TEXT                                                                   P1 P1RAW

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

P2TEXT                                                                   P2 P2RAW

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

P3TEXT                                                                   P3 P3RAW     WAIT_TIME SECONDS_IN_WAIT

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

STATE

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

blocks                                                                    1 00000001         -1               0

WAITED KNOWN TIME

      SID       SEQ# EVENT

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

P1TEXT                                                                   P1 P1RAW

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

P2TEXT                                                                   P2 P2RAW

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

P3TEXT                                                                   P3 P3RAW     WAIT_TIME SECONDS_IN_WAIT

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

STATE

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

       13        134 library cache lock

handle address                                                   1530300244 5B368754

lock address                                                     1509792372 59FD9A74

      SID       SEQ# EVENT

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

P1TEXT                                                                   P1 P1RAW

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

P2TEXT                                                                   P2 P2RAW

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

P3TEXT                                                                   P3 P3RAW     WAIT_TIME SECONDS_IN_WAIT

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

STATE

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

100*mode+namespace                                                      201 000000C9          0             384

WAITING

 

    SID       SEQ# EVENT

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

P1TEXT                                                                   P1 P1RAW

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

P2TEXT                                                                   P2 P2RAW

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

P3TEXT                                                                   P3 P3RAW     WAIT_TIME SECONDS_IN_WAIT

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

STATE

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

       12         35 SQL*Net message from client

driver id                                                        1650815232 62657100

#bytes                                                                    1 00000001

      SID       SEQ# EVENT

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

P1TEXT                                                                   P1 P1RAW

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

P2TEXT                                                                   P2 P2RAW

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

P3TEXT                                                                   P3 P3RAW     WAIT_TIME SECONDS_IN_WAIT

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

STATE

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

                                                                         0 00                0             366

WAITING

SQL>

• Exclusive 와 Shared 모드는 서로 호환이 되지 않아 두번째 세션은 라이브러리 캐시 Lock을 대기

Null모드

• Lock을 장시간 유지시 사용
• 커서 , 프로시저, 함수, 패키지 처럼 실행 가능한 오브젝트 Lock을 Null 모드로 설정가능
• 커서는 항상 Null 모드만 사용
• Breakable Parse Lock 
• Null 모드의 Parse Lock은 대기 없이 해제 가능
• 스키마 오브젝트가 변경되거나 Drop되면 오브젝트를 참조하는 실행가능 LCO
  (sql 커서와 pl/sql단위 프로그램에 대한LCO)는 뮤효화(invalidate)
• Shared Pool 에 캐싱된 실행가능 LCO는 자신이 참조하는 각 스키마 오브젝트에 대해 하나의 Parse Lock을 보유
• 오브젝트 정보가 변경되면 Parse Lock을 해제함으로써 그것을 참조하는 실행가능 LCO를 무효화 (invalidate)

alter table EMP;

• 1,3 번 Parse Lock을 해제
  
• 오브젝트 참조 하던 두 SQL 커서 무효화
• Null 모드의 Parse Lock은 대기없이 언제든 해제할수있음
• Parse Lock은 SQL또는 PL/SQL이 처음 수행되면서 파싱되는 시점에 얻어지고
  해당 SQL문을 위한 Shared SQL area가 Shared Pool 에 남아있는한 유지

(2) 라이브러리 캐시 Pin

• LCO의 실제 내용이 담긴 힙(heap)을 보호
• 라이브러리 캐시 Pin을 얻은 프로세스만 해당 LCO의 실제 내용을 읽고, 변경하고 , 실행
• 파싱/ 컴파일 하거나 새로 로드할때도 PIn을 얻어야 함
• 라이브러리 캐시 락을 얻은후 힙을 Pin
• 세션에 의해 얻어진 Null Lock이 해제(break)된 상태라면 Pin 오퍼레이션은 실패
• Lock을 얻고 Pin하려는 순간, 힙에 저장된 내용이 캐시에서 밀려나고 없다면 다시 적재

• Parse 단계에서 SQL커서를 찾지못해 하드파싱

Misses in library cache during parse: 1

• Execute 단계에서 나타나는 경우도 있음

• Parse 단계에서 커서 LCO핸들을 찾았지만 실행시점에 오픈하고 힙을 확인해보니 캐시에서 밀려난 경우

• parse와 execute단계에서 모두 발생

• 루프로 17번 수행한경우라면?
• 실행계획이 내부적으로 커서 LCO힙과는 별도로 힙영역에 저장

select e.ename, d.dname
from emp e,dept d
where d.deptno=e.deptno;

select e.ename,d.dname,d.loc
from emp e, dept d
where d.deptno=e.deptno;

• text가 다르므로 각각 다른 Hash Value와 Address를 가지고 라이브러리 캐시에 캐싱
• 실행계획은 동일할수도 있음

• v$sql을 조호해보면 SQL Hash Value와 Address는 달라도 Plan Hash Value는 동일

• SQL커서를 찾지 못해 Parse 단계에서 하드파싱
• 같은 실행계획이 라이브러리 캐시에 공유 확인
• 실행계획에 대한 포인터만 LCO 에 담고 Parse단계 종료
• 실행단계
• 커서 오픈을 위해 LCO힙에 저장된 포인터로 실행계획을 찾아감
• 실행계획에대한 핸들 캐시확인했지만 힙영역은 없어져버림

• 실행단계에서 라이브러리 캐시 Miss항목이 1보다 크거나 같고 그단게의 블록 읽기가 과도하게 나타나는 경우
• 메모리 크기가 작거나 잦은 하드 파싱으로 인해 커서 힙영역이 캐시에서 밀려나는 일이 자주 발생함을 의미
• 라이브러리 캐시 효율을 위해 중요한 것은
  라이브러리 캐시 힙에 저장된 오브젝트가 새로 로딩되는 비율을 감소시키는 것
  

select (pins-reloads)/pins + 100 "LCO HitRatio"
from v$librarycache
where namespace='SQL AREA'

• Pin 모드
• Share, Exclusive 두가지
• Stored 오브젝트와 Transient 오브젝트 둘다 두가지 모드중 하나를 사용
• Shared 모드 Pin -> 읽기 작업
• LCO를 변경할 때는 에러와 보안 체크를 위해 먼저 Shared모드로 Pin
• 실제 변경작업을 위해 Exclusive 모드로 다시 Pin 설정
• Pin은 힙에 설정되지만 Pin 소유자와 대기자 목록도 내부적으로 LCO 핸들에서 관리

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

LCO 타입 별로 작업유형에 따른 LCO과 PIN

• LCO 정보가 변경되면 해당 LCO를 참조하고 있는 다른 실행가능 LCO의 Parse Lock을 연달아 해제

• get_dname 함수를 사용중인 세션이 없음
• DEPT테이블에 변경에 가해지면 get_dname함수에 설정된 Parse Lock은 해제
• dependency 체인을 따 select 문에 커서에 대한 Parse Lock까지 해제
• get_dname 함수를 Pin하는 프로세스가 없으므로 이후 컴파일하려는 세션은 정상작업 진행

• Parse Lock을 해제할 대상 LCO가 현재 사용중인 경우
• 좌측 상단에 select 문이 수행
• 해당커서힙에 Shared 모드로 Pin설정
• select 문이 사용중인 get_dname 함수에 Call (Execute,Fetch call등) 진행되는 동안 Shared 모드로 Pin 유지
• DEPT 테이블에 변경이 가능해지면 get_dname 함수는 Pin이 설정된 상태임에도 Parse Lock은 즉시 해제,
  dependenct 체인을 따라 select 문 커서에 대한 Parse Lock을 해제
• Parse Lock은 해제 됬지만 Select 커서가 수행되는 동안 get_dname 함수에 대한 Pin은 계속 유지
• Exclusive 모드로 get_dname을 컴파일 (명시적으로 컴파일 하거나 첫번째 수행)하려는 또 다른 세션이 있다면 library cache pin 대기 이벤트를 만나게됨

• select 문 수행중에도 dependency 체인을 따라 종속성을같은 DEPT테이블 변경 이유
• select 커서가 get_dname 함수를 PIN 하지만 그순간 DEPT 테이블 정보를 담고 있는 LCO 를 PIN하지 않음
• DEPT 테이블 LCO는 함수를 컴파일 하는 동안에만 짧게 Pin을 얻었다가 곧바로 해제
• 함수를 통하지 않고 SQL문이 직업 참조하는 테이블에 대해서도 SQL무 수행도중 컬럼을 추가하거나 삭제할수 있음
• 하나의 LCO에 Lock과 Pin 두개의 직렬화 장치를 따로 두는 이유
• 실제 LCO 컨텐츠의 정합성은 Pin을 통해 보장, 별도로 Lock을 둠으로 동시성을 높임