오라클 성능 고도화 원리와 해법 I (2012년)
목록
Library Cache Lock & Pin 0 0 99,999+

by 구루비스터디 Library Cache [2018.03.20]

  1. 라이브러리 캐시 LOCK & Pin
    1. Lock모드간 상호 호환성(compatibility)
    2. Exclusive , Shared 모드 예시
    3. Null 모드 예시
  2. 라이브러리 캐시 Pin
    1. 1.Parse단계에서 커서 LCO핸들 찾음, 실행 시점에 커서를 오픈하려고 LCO힙을 확인해 보니 캐시에서 밀려나고 없어 실행단계에서 하드파싱을 하는 경우
    2. 2.SQL커서가 같은 실행계획을 공유하는 경우( Hash Value와 Address는 다르더라도 Plan Hash Value는 동일)
  3. (3)라이브러리 캐시 Lock과 Pin, 두 개의 직렬화 장치를 따로 두는 이유
    1. 1.미사용중(Pin 설정 없음)
    2. 2.사용중(Pin 설정)


라이브러리 캐시 LOCK & Pin

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


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


Lock모드간 상호 호환성(compatibility)
Lock 모드NullSharedExclusive
NullOOO
SharedOOX
ExclusiveOXX


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을 보유함.
  • 참조하는 오브젝트가 변경되거나 Drop되면 그 오브젝트를 참조하는 실행가능 오브젝트는 무효화(invalidate)됨
  • 무효화 이후 첫 번째 수행할 때 다시 하드 파싱되거나 재컴파일됨

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


라이브러리 캐시 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
코어 오라클 데이터베이스 스터디 모임 에서 2012년에 오라클 성능 고도화 원리와 해법 I 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3126

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입