메뉴 건너뛰기

bysql.net

1._Library_Cache_Lock_Pin

2012.05.21 22:09

남송휘 조회 수:3956

(1) 라이브러리 캐시 Lock


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


  • 상호 호환성(compatibility)


Lock 모드NullSharedExclusive
Nullooo
Sharedoox
Exclusiveoxx


  • 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)


1.png




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 단계에서 나타나는 경우도 있음


Misses in library cache during parse: 0
Misses in library cache during execute: 1


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


2.png



  • 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힙에 저장된 포인터로 실행계획을 찾아감
    • 실행계획에대한 핸들 캐시확인했지만 힙영역은 없어져버림

3.png

 

  • 실행단계에서 라이브러리 캐시 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 타입LockPin
테이블,
인덱스,
클러스터등
생성/변경할대, Exclusive 읽을때 , Share변경할때,Exclusive 읽을때, Share
함수,
프로시저,
패키지등
컴파일할대,Exclusive 읽을때, Share 실행할때, Null컴파일 할때, Exclusive 실행할때, Share
SQL커서항상 Null커서를 찾을때, Share 하드 파싱할때 , Exclusive


7-2.png

  • LCO 정보가 변경되면 해당 LCO를 참조하고 있는 다른 실행가능 LCO의 Parse Lock을 연달아 해제
  • get_dname 함수를 사용중인 세션이 없음
  • DEPT테이블에 변경에 가해지면 get_dname함수에 설정된 Parse Lock은 해제
  • dependency 체인을 따 select 문에 커서에 대한 Parse Lock까지 해제
  • get_dname 함수를 Pin하는 프로세스가 없으므로 이후 컴파일하려는 세션은 정상작업 진행

7-3.png

 

  • 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을 둠으로 동시성을 높임




  • 오라클 고도화 원리와 해법 1 (bysql.net 2012년 1차 스터디)

  • 작성자:  남송휘

  • 최초작성일: 2012년 6월 2 일

  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net

  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^