1st_Library Cache Lock & Pin (by niceboyous) [2012.12.26]
라이브러리 캐시 Lock & Pin
h3.1) 라이브러리 캐시 Lock
- 라이브러리 캐쉬 오브젝트 (LCO)의 핸들을 보호
보호하는 3가지 방법
- Shared 모드 : 읽기 작업 시
- Exclusive 모드 : 생성 또는 변경 작업 시
- Null 모드 : Lock을 장시간 유지하려 할 때 시용. 실제 대기를 발생시키지는 않으며， 오브젝트간 의존성 (dependency)을 관리하는 데 시용
라이브러리 캐시 Lock 모드간 호환성
h5.예제)라이브러리 캐쉬 Lock 설정
(s1)
create table t ( id number, name char (10) );
테이블이 생성되었습니다.
insert INTO t
select rownum, lpad (rownum, 10, '0' ) from dual
connect by level <= 1000000;
1000000 개의 행이 만들어졌습니다.
alter table t modify name char (24);
(s2) s1 ING....
select sid from v$mystat where rownum = 1;
SID
----------
19712
select count(*) from t;
(s3)
SELECT * FROM v$session_wait WHERE sid=19712;
SID SEQ# EVENT WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- -------------------------------- ----------- ---------------------- --------------- -----------
19712 166 library cache lock 3875070507 4 Concurrency 0 33 WAITING
- 위의 예제를 보면 S1에서 ALTER TABLE를 수행시 Exclusive모드로 Lock을 잡고 100만건의 로우를 변경하고 있는 중에 S2에서 Shared 모드로 조회를 수행하려 했지만 Exclusive 모드와 Shared 모드는 서로 호환되지 않아 후에 수행이 된 S2에서 라이브러리 캐쉬 락으로 인해 대기이벤트가 발생하고 있는것을 확인할수 있다.
h5.라이브러리 캐시 Lock : NULL 모드
Lock을장시간유지하려 할때 사용하며， 커서， 프로시저， 함수， 패키지처럼 실행 가능한 오브젝트에는 Lock을 Null 모드로 설정할 수 있다.
특히， 커서는 항상 Null 모드만을 사용된다.
'라이브러리 캐시 Lock' 을 Breakable Parse Lock' 이라고 언급하고 있는데 이는 NULL 모드의 Parse 1ρck은 대기없이 언제든 해제 될수 있기 때문에 붙여진 이름이다.
h3.특징
- Shared Pool에 캐싱된 실행가능 LCO는 자신이 참조하는 각 스키마 오브젝트에 대해 하나의 Parse Lock을 보유
1.오브젝트 정보가 변경되면 Parse 1ρck을 해제함으로써 그것을 참조하는 실행가능 LCO를 무효화 시킨다.
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은 LCO의 실제 내용이 담긴 힙 을 보호한다.
h3.Pin획득
- LCO의 실제 내용을 읽고，변경하고，실행할 수 있다.
- 파싱/컴파일하거나 정보를 새로 로드할 때.
- Pin을 획득하기 위해서는 라이브러리 캐시 Lock를 획득해야함
라이브러리 캐시 Miss가 발생하는 경우에 나타나는 현상
경우 1)
- Misses in library cache during parse : 0
- Misses in library cache during execute : 1
_parse에서는 라이브러리 캐시 Miss가 0 인데 Execute에서는 1로 나타나고 있다._
현상 : Parse 단계에서 커서 LCO 핸들을 찾았는데 실행 시점에 커서를 오픈하려고 LCO 힙을 확인해 보니
캐시에서 밀려나고 없어 실행단계에서 하드파싱을 하는 현상
이유 : LCO 힙을 영구적으로 Pin 해 둘 수는 없으므로 언제든 이런 현상을 만날 수 있다.
LCO는 LRU 알고리즘과 같은 관리구조를 가지기 때문에
경우 2)
- Misses in library cache during parse : 1
- Misses in library cache during execute : 1
_파싱과 실행 단계 모두에서 라이브러 리 캐시 Miss가 발생하고 있다._
현상 : 실행계획이 내부적으로는 커서 LCO 힙과는 별도의 힙 영역에 저장되기 때문에 생기는 현상(필자의 생각)
이유 : 새로운 SQL문이 수행이 되면서 SQL 커서를 찾지 못해 하드파싱이 되어 실행계획을 만들고 보니 라이브러리 캐쉬 영역에 동일한
실행계획을 가진 LCO를 확인하고 LCO에 해당 실행계획에 대한 포인터만 입력하고 공유되어 있는 LCO 힙을 들여다 보니 힙의 내용이
캐시에서 밀려나고 없어 실행단계에서 다시 하드파싱을 하는 현상
*이러한 현상이 자주 일어난다면 메모리크리가 작거나 잦은 하드 파싱으로 인해 커서 힙영역이 캐시에서 밀려나는 일이 자주 발생하는 경우를 의미함*
h3.3) 라이브러리 캐시 냐ck과 Pin , 두 개의 직렬화 장치를 따로 두는 이유
- 각 LCO 타입 별로 작업유형에 따라 요구되는 Lock 과 Pin 모드 요약
라이브러리 캐시 Lock과 Pin 메커니즘
- 해당 Lco(get_dname)가 사용중이지 않을때
상황 시나리오
1. dept 테이블에 변경작업이 수행중
2. get_dname 함수에 설정된 Parse Lock 해제
3. dependency 체인을 따라 select문 커서에 대한 Parse Lock까지 해제
4. get_dname 함수를 Pin하고 있는 프로세스가 없으므로 이후에 이 함수를 컴파일하려는 세션은 이상없이 작업이 수행된다.
- 해당 Lco(get_dname)가 사용중 일때
상황 시나리오
1. cursor의 select문이 수행중
2. select문이 수행중으로 인해 shared모드로 해당 select 문과 function의 힙에 Pin이 잡혀있음
3. dept 테이블에 변경작업이 수행
4. select 문과 Function의 핸들에 걸린 락이 해제(라이브러리 캐시 lock:null모드이기 때문에 대기없이 바로 해제됨)
5. 다른세션에서 펑션 컴파일 수행(Exclucive모드)
6. 핸들에 걸린 Parse Lock는 해제가 되었으나 cursor의 select문이 수행중으로 Function과 select문에 Shared 모드로 Pin이
설정되어 있기때문에 library cache pin 대기 이벤트 발생
수행예제
*S1)*
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 - S1에서 함수를 수행
SQL> /
*S2)*
SQL> select sid from v$mystat where rownum = 1
SQL> /
SID
----------
19712
SQL> alter table dept modify deptno number(3) <= 1번 세션에서 함수 ing...
SQL> /
테이블이 변경되었습니다.
SQL> alter function test_pin compile <= 1번 세션에서 함수 ing...
SQL> /
*S3)*
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 = '19712'
SQL> /
EVENT WAIT_TIME SECONDS_IN_WAIT STATE
------------------------ --------------- ----------------- ---------
library cache pin 0 16 WAITING