부록 Library Cache Lock & Pin (by leechh1004) [2010.05.08]
h1.(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을 보유함.
- 참조하는 오브젝트가 변경되거나 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
h1.(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
(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 ,p1text || '->' || p1 || ' , ' || p2text || '->' || p2 ||' , '||p3text||'->' ||p3 param
6* from v$session_wait where sid = '155'
SQL> /
EVENT WAIT_TIME SECONDS_IN_WAIT STATE PARAM
------------------------ --------------- ----------------- --------- -----------------------------------------------------------------------
library cache pin 0 16 WAITING handle address->1781245152 , pin address->1758579652 , 100*mode+namespace->301