by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

부록 Library Cache Lock & Pin




(1)라이브러리 캐시 LOCK & Pin : 라이브러리 캐시 오브젝트(테이블, 인데스, 뷰, 트리거, 함수/프로시저, 패키지)에 대한 핸들을 보호할 목적

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

Exclusive , Shared 모드 예시

1번 세션
SQL> create table t( id number, name char(10))
SQL> /

테이블이 생성되었습니다.
SQL> insert into t
     select rownum, lpad( rownum, 10, '0' ) from dual
     connect by level <= 1000000
SQL> /

1000000 개의 행이 만들어졌습니다.

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

테이블이 변경되었습니다.
exclusive lock mode	
2번 세션

SQL> select sid from v$mystat where rownum = 1;

       SID                                                                      
----------                                                                      
       161

SQL> select count(*) from t  <= 1번 세션에서  alter table t modify name char(20) 실행 후
SQL> /

  COUNT(*)                                                                      
----------                                                                      
   1000000  

shared lock mode

3번 세션

2번 세션 실행 후 대기 상태 확인 후 실행

SQL> select event
  2        ,wait_time
  3        ,seconds_in_wait
  4        ,state
  5        ,p1text || '->' || p1 || '  ,  ' || p2text || '->' || p2 ||'  ,  '||p3text||'->' ||p3 param
  6   from v$session_wait where sid = '161';

EVENT                WAIT_TIME  SECONDS_IN_WAIT    STATE                PARAM 
-------------------- ----------  ---------------   -------------------  -------------------      
library cache lock           0               63               WAITING    handle address->1742193216, lock address->1789945348, 100*mode+namespace->201

Null 모드 예시(커서, 프로시저, 함수, 패키지처럼 실행 가능한 오브젝트는 Lock을 Null모드로 설정)

  • Null mode = Breakable Parse Lock(Parse Lock은 대기 없이 언제든 해제(breack) 될 수 있다.
  • 실행 가능한 오브젝트 : Shared Pool에서 캐싱된 실행가능 LCO는 자신이 참조하는 각 스키마 오브젝트에 대해 하나의 Parse Lock을 보유함.
    1. 참조하는 오브젝트가 변경되거나 Drop되면 그 오브젝트를 참조하는 실행가능 오브젝트는 무효화(invalidate)됨
    2. 무효화 이후 첫 번째 수행할 때 다시 하드 파싱되거나 재컴파일됨
SQL> create table emp_t as select * from emp;

테이블이 생성되었습니다.

SQL> create table dept_t as select * from dept
SQL> /

테이블이 생성되었습니다.

SQL>  alter system flush shared_pool
SQL> /

시스템이 변경되었습니다.

SQL>  select * from emp_t e, dept_t d where d.deptno = e.deptno
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO     DEPTNO DNAME          LOC               
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ---------- -------------- -------------      
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30         30 SALES          CHICAGO            
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30         30 SALES          CHICAGO            
      7566 JONES      MANAGER         7839 81/04/02       2975                    20         20 RESEARCH       DALLAS  

						-  생략  -       
      7934 MILLER     CLERK           7782 82/01/23       1300                    10         10 ACCOUNTING     NEW YORK              

13 개의 행이 선택되었습니다.

SQL>  select count(*) from emp_t e
SQL> /

  COUNT(*)                                                                                                                      
----------                                                                                                                       
        14 

SQL>  select INVALIDATIONS from v$sql where SQL_TEXT like 'select * from emp_t e, dept_t d where d.deptno = e.deptno%'
SQL> /

INVALIDATIONS                                                                                                                    
-------------                                                                                                                     
            0  
SQL> select INVALIDATIONS from v$sql where SQL_TEXT like 'select count(*) from emp_t e%'
SQL> /

INVALIDATIONS                                                                                                                    
-------------                                                                                                                      
            0 
SQL>  alter table emp_t modify EMPNO number(5);

테이블이 변경되었습니다.

SQL>  select INVALIDATIONS from v$sql where SQL_TEXT like 'select * from emp_t e, dept_t d where d.deptno = e.deptno%'
SQL> /

INVALIDATIONS                                                                                                                  
-------------                                                                                                                  
            1
SQL> select INVALIDATIONS from v$sql where SQL_TEXT like 'select count(*) from emp_t e%'
SQL> /

INVALIDATIONS                                                                                                                   
-------------                                                                                                                     
            1

(2)라이브러리 캐시 Pin :

  • 개념적으로 Pin은 힙에 설정되지만 Pin소유자와 대기자 목록은 내부적으로 LCO핸들에서 관리됨.
  • LCO핸들은 영구적인 Fixed Array영역에 할당되는 반면 LCO자체는 동적으로 관리되는 힙(Heap)영역에 할당됨
1.Parse단계에서 커서 LCO핸들 찾음, 실행 시점에 커서를 오픈하려고 LCO힙을 확인해 보니 캐시에서 밀려나고 없어 실행단계에서 하드파싱을 하는 경우
  • Misses in library cache during parse : 0
  • Misses in library cache during execute : 1
2.SQL커서가 같은 실행계획을 공유하는 경우( Hash Value와