- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4949075&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
(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
(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
문서에 대하여
- 최초작성자 : [이창헌||이창헌]
- 최초작성일 : 2010년 5월 8일
- 수정작성일 : 2010년 5월 8일
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4949075&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.