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

Library Cache Lock & Pin




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

  • Shared모드 : 읽기 작업
  • Exclusive모드 : 생성 또는 변경 작업
  • Null모드 : Lock을 장시간 유지하려 할 때 사용, 실제 대기를 발생시키지는 않으며, 오브젝트간 의존성을 관리하는 데 사용
    Breakable Parse Lock : 오브젝트가 변경되거나 Drop되면 그 오브젝트를 참조하는 실행가능 커서, 프로시저, 함수, 패키지를 무효화(INVALIDATE)
  • 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   from v$session_wait where sid = '161';

EVENT                WAIT_TIME  SECONDS_IN_WAIT    STATE                 
-------------------- ----------  ---------------   -------------------       
library cache lock           0               63               WAITING 

Exclusive와 Shared모드는 서로 호환이 되지 않아 결국 두 번째 세션은 라이브러리 캐시 lock을 대기 한다. 
  

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와 Address는 다르더라도 Plan Hash Value는 동일)
  • Misses in library cache during parse : 1
  • Misses in library cache during execute : 1
  • 위와 같은 현상이 자주 발생하면 메모리크리가 작거나 잦은 하드 파싱으로 인해 커서 힙영역이 캐시에서 밀려나는 일이 자주 발생하는 경우를 의미함.
    
    SQL> select e.ename, d.dname from emp e, dept d where d.deptno = e.deptno
      2  /
    
    ENAME      DNAME                                                                
    ---------- --------------                                                       
    CLARK      ACCOUNTING                                                           
         -생 략 -                                                               
    WARD       SALES                                                                
    
    13 개의 행이 선택되었습니다.
    
    SQL> select e.ename, d.dname, d.loc from emp e, dept d where d.deptno = e.deptno
    SQL> /
    
    ENAME      DNAME          LOC                                                   
    ---------- -------------- -------------                                         
    CLARK      ACCOUNTING     NEW YORK                                              
    		-생략-                                           
    WARD       SALES          CHICAGO                                               
    
    13 개의 행이 선택되었습니다.
    
    SQL> SELECT HASH_VALUE, ADDRESS ,PLAN_HASH_VALUE 
      2    FROM V$SQL 
      3   WHERE SQL_TEXT LIKE 'select e.ename, d.dname from emp e, dept d where d.deptno = e.deptno%'
    SQL> /
    
    HASH_VALUE ADDRESS  PLAN_HASH_VALUE                                             
    ---------- -------- ---------------                                             
    2776686202 690E4470      3191017203                                             
    
    SQL> SELECT HASH_VALUE, ADDRESS ,PLAN_HASH_VALUE
      2   FROM V$SQL 
      3   WHERE SQL_TEXT LIKE 'select e.ename, d.dname, d.loc from emp e, dept d where d.deptno = e.deptno%'
    SQL> /
    
    HASH_VALUE ADDRESS  PLAN_HASH_VALUE                                             
    ---------- -------- ---------------                                             
    2946835987 68E47DE8      3191017203
    
    
    SQL커서를 찾지 못해 Parse단계에서 하드파싱을 한다. 하드파싱한 결과 같은 실행계획이 이미 라이브러리 캐시에 공유된 것이 확인되었다. 그래서 그 실행계획에 대한 포인터만 LCO에 담고 Parse단계를 마친다.
    이제 실행 단계로 넘어가서 커서를 오픈하려고 LCO힙에 저장된 포인터를 실행계획을 찾아가 보니 그 실행계획에 대한 핸들은 캐싱돼 있는데, 그 실제 내용에 해당하는 힙 영역은 사라지고 없었던 것이다.
    
    

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

  • 1.미사용중(Pin 설정 없음)
    • LCO정보가 변경되면 해당 LCO를 참조하고 있는 다른 실행가능 LCO의 Parse Lock을 연달아 모두 해제 한다.

  • 2.사용중(Pin 설정)
    • 라이브러리 캐시 Pin은 LCO의 실제 내용이 담긴 힙(Heap)을 보호한다.
    • 라이브러리 캐시 힙(Heap)을 Pin하려면 먼저 라이브러리 캐시 Lock(Parse Lock= NULL MODE)을 얻어야 한다.
    • Parse Lock은 해제됐지만 select 커서가 수행되는 동안 get_dname함수에 대한 Pin은 계속 유지되므로 Exclusive모드로 get_dname을 컴파일(명시적으로 컴파일하거나 첫 번째 수행)하려는 또 다른 세션이 있다면 library cache pin대기 이벤드를 만나게 된다.

1번 세션
SQL> create or replace function test_pin( v_loop in number )
  2  return number
  3  is
  4  v_deptno number;
  5  begin
  6  	 select deptno into v_deptno from dept where deptno = 10;
  7       for count in 1..v_loop
  8  	 loop
  9  	    dbms_lock.sleep(10);
 10  	 end loop;
 11  	 return v_deptno;
 12  end;
 13 / 

함수가 생성되었습니다.

SQL> select test_pin(10) from dual
SQL> /

TEST_PIN(30)                                                                    
------------                                                                    
          10

2번 세션
SQL>  select sid from v$mystat where rownum = 1
SQL> /

       SID                                                                      
----------                                                                      
       155                                                                      

SQL> alter table dept modify deptno number(3) <= 1번 세션에서 함수 실행 중 일 때
SQL> /

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

SQL>  alter function test_pin compile  <= 1번 세션에서 함수 실행 중 일 때
SQL> /

함수가 변경되었습니다.


3번 세션
2번 세션에서 alter function test_pin compile 구문 실행 후 실행 함.

SQL> select event
  2        ,wait_time
  3        ,seconds_in_wait
  4        ,state
  5   from v$session_wait where sid = '155'
SQL> /

EVENT                            WAIT_TIME    SECONDS_IN_WAIT  STATE         
------------------------    --------------- -----------------  ---------       
library cache pin                        0                 16   WAITING     

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.