부록 정리 자료 - 길용현

[길용현]

01. 라이브러리 캐시 Lock & Pin

 (1) 라이브러리 캐시 Lock

  - 라이브러리 캐시 Lock 은 라이브러리 캐시 오브젝트에 대한 핸들을 보호하며, 아래 세 가지 모드를 사용한다.

   ⊙ Shared 모드 : 읽기 작업 시

   ⊙ Exclusive 모드 : 생성 또는 변경 작업 시

   ⊙ Null 모드 : Lock을 장시간 유지하려 할 때 사용. 실제 대기를 발생시키지는 않으며, 오브젝트간 의존성을 관리하는 데 사용.

※ Lock 모드간 상호 호환성 ※

Lock 모드	Null	Shared	Exclusive
Null	O	O	O
Shared	O	O	X
Exclusive	O	X	X

  - Stored Object(테이블, 인덱스, 뷰, 트리거, 함수/프로시저, 패키지 등)는 상황에 따라 이 세 가지 중 하나를 Lock 모드로 사용.

-- Library Cache LCO 핸들 Lock test

-- session 1

drop table t;

create table t (id number, name char(10));

insert into t

select rownum, lpad(rownum, 10, '0') from dual

connect by level <= 1000000;

commit;

alter table t modify name char(20);

-- session 2

select count(*) from t;

-- session 3

select *

from v$session_wait

where sid = 132;

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

  - Null 모드는 Lock을 장시간 유지하려 할 때 사용하며, 커서, 프로시저, 함수, 패키지 처럼 실행 가능한 오브젝트에는 Lock 을 Null

    모드로 설정할 수 있다.

    특히, 커서는 항상 Null 모드만을 사용된다.

    오라클 메뉴얼에는 '라이브러리 캐시 Lock'을 Breakable Parse Lock 이라고 언급하고 있는 데, 이는 Null 모드의 Parse Lock 은

    대기없이 언제든 해제 될 수 있기 때문이다.

    스키마 오브젝트가 변경되거나 Drop 되면 그 오브젝트를 참조하는 실행가능 LCO는 무효화 되어야 한다.

    그럼으로써 이후 첫 번째 수행할 때 다시 하드 파싱되거나 재컴파일 되도록 하는 것이다.

    이를 위해 Shared Pool에 캐싱된 실행가능 LCO는 자신이 참조하는 각 스키마 오브젝트에 대해 하나의 Parse Lock을 보유한다.

    오브젝트 정보가 변경되면 Parse Lock을 해제 함으로써 그것을 참조하는 실행가능 LCO를 무효화 시킨다.

  - 예를 들어, alter table EMP 문장이 수행되면 위 그림의 ① 번과 ③ 번 Parse Lock을 해제함으로써 이 오브젝트를 참조하던 두 

    SQL 커서는 무효화된다.

    이처럼 Null 모드의 Parse Lock은, 그것을 해제함으로써 해당 실행가능 LCO를 사용하려는 이 후 세션에게 그 LCO 가 더 이상

    유효하지 않음을 알리는 메커니즘으로 사용되며, 대기 없이 언제든 해제할 수 있도록 하려고 Null 모드를 사용하는 것이다.

    Parse Lock은 SQL 또는 PL/SQL 문이 처음 수행되면서 파싱되는 시점에 얻어지고 해당 SQL 문을 위한 shared SQL area 가

    Shared Pool 에 남아있는 한 계속 유지된다.

 (2) 라이브러리 캐시 Pin

  - 라이브러리 캐시 Pin은 LCO의 실제 내용이 담긴 힙을 보호한다.

    라이브러리 캐시 Pin을 얻은 프로세스만이 해당 LCO의 실제 내용을 읽고, 변경하고, 실행할 수 있다.

    파싱/컴파일하거나 정보를 새로 로드할 때도 Pin을 얻어야 한다.

    그리고 라이브러리 캐시 힙을 Pin 하려면 먼저 라이브러리 캐시 Lock을 얻어야 한다.

    세션에 의해 얻어진 Null Lock이 해제된 상태라면 Pin 오퍼레이션은 실패하게 된다.

    그리고 Lock을 얻고 Pin 하려는 순간, 힙에 저장된 내용이 캐시에서 밀려나고 없다면 다시 적재해야만 한다.

  - Parse 단계에서 SQL 커서를 찾지 못해 하드파싱을 하게 되면 SQL 트레이스에서 아래와 같은 항목을 발견할 수 있다.

    Misses in library cache during parse: 1

    가끔 라이브러리 캐시 Miss가 아래처럼 Execute 단계에 나타나는 경우가 있다.

    Misses in library cache during parse: 0

   Misses in library cache during execute: 1

    Parse 단계에서 커서 LCO 핸들을 찾았는데, 실행 시점에 커서를 오픈하려고 LCO 힙을 확인해 보니 캐시에서 밀려나고 없어 

    실행단계에서 하드파싱을 하는 경우이다.

    Misses in library cache during parse: 1

   Misses in library cache during execute: 1

    Parse 와 Execute 단계 둘다 Miss 가 일어나는 경우는, 실행계획이 내부적으로는 커서 LCO 힙과는 별도의 힙 영역에 저장되기

    때문에 생기는 현상이다.

select e.ename, d.dname

from emp e, dept d

where d.deptno = e.deptno;

select e.ename, d.dname, d.loc

from emp e, dept d

where d.deptno = e.deptno;

select SQL_TEXT,

       SQL_ID,

       HASH_VALUE,

       ADDRESS,

       PLAN_HASH_VALUE

from v$sql

where sql_text like 'select e.ename%';

※ SQL_ID, HASH_VALUE, ADDRESS 는 다르더라도 PLAN_HASH_VALUE 가 같으므로, 두 개의 SQL 커서가 하나의 실행계획

    을 공유하고 있는 것을 확인할 수 있다.

-- 라이브러리 LCO Hit Ratio

select round((pins-reloads)/pins * 100, 2) "LCO HitRatio"

from v$librarycache

where namespace = 'SQL AREA';

LCO HitRatio

------------

       99.87

  - Pin 모드로는 Share, Exclusive 두 가지가 있고, Stored 오브젝트와 Transient 오브젝트 둘 다 이 두가지 모드 중 하나를 사용

    한다.

    읽기 작업을 위해서라면 항상 Shared 모드로 Pin 한다.

    LCO를 변경할 때는 에러와 보안 체크를 위해서 먼저 Shared 모드로 Pin 한다.

    그러고 나서 실제 변경작업을 위해서는 Exclusive 모드로 다시 Pin을 설정한다.

    개념적으로 Pin은 힙에 설정되지만 Pin 소유자와 대기자 목록도 내부적으로는 LCO 핸들에서 관리된다.

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

  - SQL 커서를 하드파싱할 때, SQL이 '참조하는 오브젝트'에 대해서는 읽기 작업이므로 Shared 모드 Lock 과 Shared 모드 Pin을

    설정한다.

    LCO 핸들은 영구적인 Fixed Array 영역에 할당되는 반면 LCO 자체는 동적으로 관리되는 힙 영역에 할당되는데, 힙에 할당된

    전체 또는 일부 청크(Chunk)가 언제든 유실될 가능성이 있기 때문이다.

    만약, 테이블 정보를 읽으려고 LCO 핸들에서 Lock 과 Pin을 모두 Shared 모드로 설정하고 힙을 찾아갔는데, 힙 정보가 사라지고

    없다면 Pin을 Exclusive 모드로 재설정한 후에 테이블 정보를 다시 로드하게 된다.

    함수/프로시저도 마찬가지다.

    예를 들어, SQL을 하드파싱할 때 거기서 참조하는 함수에 대해서는 Shared 모드로 Lock을 얻는다.

    실행할 때는 함수에 대한 Lock과 Pin을 각각 Null과 Shared모드로 설정하는데, 실제 힙을 찾아갔을 때 힙 정보가 사라지고 없다면

    Pin을 Exclusive 모드로 재설정한 후 동적으로 컴파일한다.

 - LCO 정보가 변경되면 해당 LCO를 참조하고 있는 다른 실행가능 LCO의 Parse Lock을 연달아 모두 해제한다.

    현재 get_dname 함수를 사용 중인 세션이 없는 상황이다.

    DEPT 테이블에 변경이 가해지면 get_dname 함수에 설정된 Parse Lock은 해제된다.

    dependency 체인을 따라 select 문 커서에 대한 Parse Lock 까지 해제된다.

    get_dname 함수를 Pin하고 있는 프로세스가 없으므로 이후에 이 함수를 컴파일 하려는 세션은 정상적으로 작업을 진행할 수

    있다.

 - 좌측 상단에 있는 select 문이 수행 중이므로 해당 커서 힙에 Shared 모드로 Pin을 설정한다.

    그리고 그 select 문이 사용 중인 get_dname 함수에 대해서도 Call이 진행되는 동안은 Shared 모드로 Pin을 유지한다.

    DEPT 테이블에 변경이 가해지면 get_dname 함수는 현재 Pin이 설정된 상태임에도 Parse Lock은 '지체 없이' 해제 된다.

    dependency 체인을 따라 select 문 커서에 대한 Parse Lock도 해제된다.

    Parse Lock은 해제됐지만 select 커서가 수행되는 동안 get_dname 함수에 대한 Pin은 계속 유지되므로 Exclusive 모드로 

    get_dname 을 컴파일하려는 또 다른 세션이 있다면 library cache pin 대기 이벤트를 만나게 된다.

    select 문이 수행 중인데도 dependency 체인을 따라 종속성을 갖는 DEPT 테이블에 변경을 가할 수 있는 것은, select 커서가

    get_dname 함수를 PIN하고 있지만 그 순간 DEPT 테이블 정보를 담고 있는 LCO를 PIN하지는 않기 때문이다.

    DEPT 테이블 LCO는 함수를 컴파일 하는 동안에만 짧게 PIN을 얻었다고 곧바로 해제한다.

    LCO가 Pin되면 그 오브젝트가 참조하고 있는 모든 오브젝트에도 차례로 Pin이 적용된다고 설명된 문서들이 있는데, 그렇지

    않다는 것을 보여주고 있다.

    함수를 통하지 않고 SQL 문이 직접 참조하는 테이블에 대해서도 SQL문 수행 도중 컬럼을 추가하거나 삭제할 수 있다.

    하나의 LCO에 대해 Lock 과 Pin, 두 개의 직렬화 장치를 따로 두는 이유가 바로 이것이다.

   즉, 실제 LCO 컨텐츠의 정합성은 Pin을 통해 보장받고, 그것과 별도로 Lock을 둠으로써 동시성을 높이도록 하는 것이다.

02. Cursor_Sharing

-- crusor_sharing 파라미터 FORCE 로 설정했을 경우

> create table emp

   as

   select * from scott.emp;

> create index emp_empno_idx on emp(empno);

-- 통계정보 생성

> analyze table emp compute statistics for table for all indexes for columns empno size 1;

> show parameter cursor_sharing

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cursor_sharing                       string      EXACT

> alter session set cursor_sharing = FORCE;

declare

    l_condition varchar2(20);

    l_ename emp.ename%type;

begin

    for c in (select empno from emp)

    loop

        l_condition := 'empno = ' || c.empno;

        execute immediate 'select ename '||'from emp where '||l_condition

        into l_ename;

        dbms_output.put_line(l_condition||' : '||l_ename);

    end loop;

end;

/

empno = 7369 : SMITH

empno = 7499 : ALLEN

empno = 7521 : WARD

empno = 7566 : JONES

empno = 7654 : MARTIN

empno = 7698 : BLAKE

empno = 7782 : CLARK

empno = 7788 : SCOTT

empno = 7839 : KING

empno = 7844 : TURNER

empno = 7876 : ADAMS

empno = 7900 : JAMES

empno = 7902 : FORD

empno = 7934 : MILLER

> select sql_id,

             sql_text,

             version_count

   from v$sqlarea

   where sql_text like 'select ename%';

SQL_ID        SQL_TEXT                                           VERSION_COUNT

------------- -------------------------------------------------- -------------

7v2nh2sv9hf80 select ename from emp where empno = :"SYS_B_0"                 1

> select sql_id,

             child_number chld_no,

             plan_hash_value plan_hash,

             loads,

             parse_calls,

             executions,

             fetches

    from v$sql

    where sql_text like 'select ename%';

SQL_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES

------------- ---------- ---------- ---------- ----------- ---------- ----------

7v2nh2sv9hf80          0  576302360          1          28         28         28

※ 위 결과는 pl/sql을 두 번 실행해서 나온 결과임(원래는 parse_call, executions, fetches 가 14번 일어남)

※ 세션 레벨에서 cursor_sharing 파라미터를 FORCE로 바꾸었기 때문에 바인드 변수를 사용했을 때처럼 하나의 공유 커서가

    반복 사용되었다.

    이 파라미터의 기본값은 EXACT 이고, 이때는 SQL문이 100% 같을 때만 커서를 공유할 수 있다.

    FORCE로 바꾸면, 다른 문자열은 모두 같고 일부 Literal 값만 다를 때 그 값들을 시스템이 자동 생성한 바인드 변수로 

    대체함으로써 공유 가능한 SQL 커서를 만들고 그것을 사용한다.

    FORCE로 설정하면 어떤 값으로 실행하든 항상 같은 실행계획을 사용한다.

    EXACT일 때보다 라이브러리 캐시 부하는 상당히 줄겠지만 컬럼 히스토그램을 사용하지 못하는 문제 때문에 성능이 더

    나빠질 가능성이 있다.

    이를테면, 어떤 값은 분포가 90%이고 다른 값들은 대개 1%정도 분포를 보일 때 FORCE를 사용하면 항상 고정된 실행계획을

    사용함으로써 EXACT 일 때보다 개별 쿼리 성능은 더 나빠질 수 있다.

    이 단점을 회피할 목적으로 값을 SIMILAR로 설정할 수 있는데, 그러면 실행되는 Literal 값에 따라 별도의 커서를 생성함으로

    써 다른 실행계획을 사용할 수 있게 된다.

-- crusor_sharing 파라미터 SIMILAR 로 설정했을 경우

> alter system flush shared_pool;

> alter session set cursor_sharing = SIMILAR;

declare

    l_condition varchar2(20);

    l_ename emp.ename%type;

begin

    for c in (select empno from emp)

    loop

        l_condition := 'empno = ' || c.empno;

        execute immediate 'select ename '||'from emp where '||l_condition

        into l_ename;

        dbms_output.put_line(l_condition||' : '||l_ename);

    end loop;

end;

/

empno = 7369 : SMITH

empno = 7499 : ALLEN

empno = 7521 : WARD

empno = 7566 : JONES

empno = 7654 : MARTIN

empno = 7698 : BLAKE

empno = 7782 : CLARK

empno = 7788 : SCOTT

empno = 7839 : KING

empno = 7844 : TURNER

empno = 7876 : ADAMS

empno = 7900 : JAMES

empno = 7902 : FORD

empno = 7934 : MILLER

> select sql_id,

             sql_text,

             version_count

   from v$sqlarea

   where sql_text like 'select ename%';

SQL_ID        SQL_TEXT                                           VERSION_COUNT

------------- -------------------------------------------------- -------------

7v2nh2sv9hf80 select ename from emp where empno = :"SYS_B_0"                 1

> select sql_id,

             child_number chld_no,

             plan_hash_value plan_hash,

             loads,

             parse_calls,

             executions,

             fetches

    from v$sql

    where sql_text like 'select ename%';

SQL_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES

------------- ---------- ---------- ---------- ----------- ---------- ----------

7v2nh2sv9hf80          0  576302360          1          14         14         14

※ 컬럼 히스토그램이 없기 때문에 하나의 공유 커서를 사용했다.

-- 컬럼 히스토그램 생성 후, 다시 테스트

> insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)

   select empno, ename, job, mgr, hiredate, sal, comm, deptno

   from emp, (select * from dual connect by level <= 9999)

   where emp.empno = 7788;

> commit;

> analyze table emp compute statistics

   for table for all indexes for columns empno size 8;

declare

    l_condition varchar2(20);

    l_ename emp.ename%type;

begin

    for c in (select empno from emp)

    loop

        l_condition := 'empno = ' || c.empno;

        execute immediate 'select ename '||'from emp where '||l_condition

        into l_ename;

        dbms_output.put_line(l_condition||' : '||l_ename);

    end loop;

end;

/

empno = 7369 : SMITH

empno = 7499 : ALLEN

empno = 7521 : WARD

empno = 7566 : JONES

empno = 7654 : MARTIN

empno = 7698 : BLAKE

empno = 7782 : CLARK

empno = 7788 : SCOTT

empno = 7839 : KING

empno = 7844 : TURNER

empno = 7876 : ADAMS

empno = 7900 : JAMES

empno = 7902 : FORD

empno = 7934 : MILLER

> select sql_id,

             sql_text,

             version_count

   from v$sqlarea

   where sql_text like 'select ename%';

SQL_ID        SQL_TEXT                                           VERSION_COUNT

------------- -------------------------------------------------- -------------

7v2nh2sv9hf80 select ename from emp where empno = :"SYS_B_0"                14

> select sql_id,

             child_number chld_no,

             plan_hash_value plan_hash,

             loads,

             parse_calls,

             executions,

             fetches

    from v$sql

    where sql_text like 'select ename%';

SQL_ID           CHLD_NO  PLAN_HASH      LOADS PARSE_CALLS EXECUTIONS    FETCHES

------------- ---------- ---------- ---------- ----------- ---------- ----------

7v2nh2sv9hf80          0  576302360          1           1          1          1

7v2nh2sv9hf80          1  576302360          1           1          1          1

7v2nh2sv9hf80          2  576302360          1           1          1          1

7v2nh2sv9hf80          3  576302360          1           1          1          1

7v2nh2sv9hf80          4  576302360          1           1          1          1

7v2nh2sv9hf80          5  576302360          1           1          1          1

7v2nh2sv9hf80          6  576302360          1           1          1          1

7v2nh2sv9hf80          7  576302360          1           1          1          1

7v2nh2sv9hf80          8  576302360          1           1          1          1

7v2nh2sv9hf80          9  576302360          1           1          1          1

7v2nh2sv9hf80         10  576302360          1           1          1          1

7v2nh2sv9hf80         11  576302360          1           1          1          1

7v2nh2sv9hf80         12  576302360          1           1          1          1

7v2nh2sv9hf80         13 3956160932          1           1          1          1

※ SIMILAR로 설정하면 히스토그램이 없을 때 FORCE와 똑같이 작동한다.

  그러므로 값 분포가 균등한 컬럼은 히스토그램 없이 통계를 만들어 커서를 공유할 수 있게 하고, 값 분포가 균등하지

  않은 컬럼에 대해서는 히스토그램을 만들어 옵티마이저가 좋은 실행계획을 만들도록 돕는다면 유용할 것이다.

  하지만 후자는 라이브러리 캐시 효율에는 도움이 되지 않는다.

-- 히스토그램을 생성해 둔 컬럼에 값의 종류가 많을 경우

> drop table t purge;

> create table t

  nologging

  as

  select rownum no from all_objects

  where rownum <= 10000;

> create index t_idx on t(no);

-- 컬럼 히스토그램을 생성하지 않은 경우

> analyze table t compute statistics

  for table for all indexes for all columns size 1;

> alter system flush shared_pool;

> alter session set cursor_sharing = SIMILAR;

> set timing on

declare

    l_cnt number;

 begin

    for i in 1..10000

    loop

        execute immediate 'select /* similar */ count(*) from t where no = '|| i

        into l_cnt;

    end loop;

 end;

 /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.52

-- 컬럼 히스토그램을 생성한 경우

> analyze table t compute statistics

   for table for all indexes for all columns size 100;

> alter system flush shared_pool;

declare

   l_cnt number;

begin

   for i in 1..10000

   loop

       execute immediate 'select /* similar */ count(*) from t where no = '|| i

       into l_cnt;

   end loop;

end;

/

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:03.83

-- cursor_sharing 파라미터를 EXACT 로 설정했을 경우

> alter session set cursor_sharing = EXACT;

declare

    l_cnt number;

 begin

    for i in 1..10000

    loop

        execute immediate 'select /* similar */ count(*) from t where no = '|| i

        into l_cnt;

    end loop;

 end;

 /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:03.47

-- 바인드 변수를 사용했을 경우

declare

    l_cnt number;

begin

    for i in 1..10000

    loop

        execute immediate 'select /* bind */ count(*) from t where no = :no'

        into l_cnt

        using i;

    end loop;

end;

/

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:00.26

03. Deterministic 함수 사용 시 주의사항

 - Deterministic 키워드는, 함수의 입력 값이 같다면 출력 값도 항상 같음을 선언하려는 데에 목적이 있다.

    그런데도 본래 의미를 무시하고 캐싱 효과를 얻을 목적으로 함부로 Deterministic 함수를 선언하면 안 된다.

    성능도 중요하지만 잘못 사용했을 때 데이터 일관성을 해칠 수 있음을 간과해선 안 되기 때문이다.

create or replace function lookup(l_input number) return varchar2

DETERMINISTIC

as

l_output LookupTable.value%TYPE;

begin

select value into l_output from LookupTable where key = l_input;

return l_output;

end;

/

※ 위 함수는 Deterministic 으로 선언해서는 안된다.

    동일 입력 값에 대해 동일 출력 값을 보장할 수 없기 때문이다.

 ※ 함부로 Deterministic 으로 선언했을 때 생기는 부작용

create or replace function lookup(l_input number) return varchar2

DETERMINISTIC

as

l_output LookupTable.value%TYPE;

begin

select value into l_output from LookupTable where key = l_input;

return l_output;

end;

/

SQL> create table LookupTable(key number, value varchar2(100));

SQL> delete from LookupTable;

SQL> insert into LookupTable values(1, 'YAMAHA');

SQL> commit;

SQL> create table t

         as

         select 1 no from dual;

SQL> select no, lookup(no) value from t;

NO VALUE

-------- ------------

       1       YAMAHA        

SQL > create index t_idx on t(lookup(no));

SQL > select no, lookup(no) value, lookup(1) value2

          from t

          where lookup(no) = 'YAMAHA';

      NO VALUE      VALUE2

-------- ---------- ----------

       1 YAMAHA     YAMAHA

SQL > update LookupTable set value = 'YAMAYA2' where key = 1;

          commit;

SQL > select no, lookup(no) value, lookup(1) value2

          from t

          where lookup(no) = 'YAMAHA';

        NO VALUE      VALUE2

---------- ---------- ----------

         1 YAMAHA     YAMAYA2

※ 위 테스트는 함수의 실제 내용이 Deterministic이 아닌데 Deterministic으로 선언했을 때 일관성이 깨지는 사례를 보이고 

    있다. 

    실무적으로 사용되는 사용자정의 함수 대부분은 위처럼 쿼리문을 포함하는 형태이므로 오라클의 읽기 일관성 모델 특성상

    Deterministic 함수일 수 없다.

    FBI를 만드는 경우 외에도, update 문으로 반정규화 컬럼을 갱신하거나 insert 문으로 가공 테이블에 레코드를 삽입할 때도

    중간에 값이 변경되면 일관성 없는 상태로 값들이 영구 저장될 수 있다.

    따라서 캐싱 효과를 얻으려고 함부로 Deterministic으로 선언하는 것은 위험한 일임을 주지하기 바란다.

[길용현]

으~드뎌 1권 끝!!!