by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

14S_락 타입




Lock Types

  • 오라클은 일반적으로 아래와 같이 3가지 락 타입을 가지고 있다.
    • DML locks : 데이터를 동시에 변경할 수 있는 메커니즘을 제공한다.
    • DDL locks : 객체 구조의 정의를 보호한다.
    • Internal locks and latches : 내부 자료구조를 보호하기 위해 내부 락(internal lock)과 래치를 사용한다. 래치는 오라클이 사용하는 경량의 저수준의 직렬 장치이며, 기능에서는 락과 비슷하다.

DML Locks

TX (Transaction) Locks

  • TX 락은 트랜젝션이 첫 번째 변경을 시작할 때 획득하며, 커밋 또는 롤백을 수행할 때까지 유지된다.
  • 다른 세션이 트랜잭션을 완료할 때까지 기다릴 수 있는 큐잉 메커니즘으로 사용된다.
  • 락을 거는 과정은 다음과 같다.
    1. 락을 걸고 싶은 로우의 주소를 찾는다.
    2. 락 관리자에서 줄을 선다.
    3. 리스트에 락을 건다.
    4. 리스트를 검색하여 다른 누군가 해당 로우에 락을 걸었는지 조회한다.
    5. 여러분이 락을 걸었다는 사실을 입증하기 위해 리스트에 새 엔트리를 만든다.
    6. 리스트에 락을 푼다.
  • 나중에 커밋할 때 다음과 같은 절차를 계속 해야 한다.
    1. 다시 락 관리자에 줄을 선다.
    2. 락 리스트에 락을 건다.
    3. 리스트를 검색하여 설정한 락을 해제한다.
    4. 리스트에 락을 푼다.
  • 락을 획득하고 해제하는 과정은 다음과 같다.
    1. 락을 걸고 싶은 로우의 주소를 찾는다.
    2. 해당 로우로 간다.
    3. 그 자리에 있느 로울에 락을 건다.
  • 세 개의 V$ 테이블을 이용하여 락에 대한 정보를 알 수 있다.
    • V$TRANSACTION : 활동 중인 모든 트랜젝션에 대한 엔트리를 담고 있다.
    • V$SESSION : 로그인한 세션을 보여준다.
    • V$LOCK : 락을 기다리는 세션 뿐만 아니라 보유 중인 모든 enqueue 락에 대한 엔트리를 포함하고 있다.
  • EMP와 DEPT 테이블의 복제본을 만든다.
    SQL> create table dept_1 
    2    as select * from dept;
    
    Table created.
    
    SQL> create table empt_1 
    2    as select * from emp;
    
    Table created.
    
    SQL> alter table dept_1 
    2    add constraint dept_1_pk
    3    primary key(deptno);
    
    Table altered.
    
    SQL> alter table emp_1 
    2    add constraint emp_1_pk
    3    primary key(deptno);
    
    Table altered.
    
    SQL> alter table emp_1
    2    add constraint emp_1_fk_dept
    3    foreign key(deptno)
    4    references dept_1(deptno);
    
    Table altered.
    
    SQL>  create index emp_1_deptno_idx
    2     on emp_1(deptno);
    
    Index created.
    
  • 이제 트랜젝션을 시작한다.
    SQL>  update dept_1
    2     set dname = initcap(dname);
    
    4 rows updated.
    
  • 시스템의 상태를 조회한다.
    SQL> select username,
    v$lock.sid,
    trunc(id1/power(2,16)) rbs,
    bitand(id1,to_number('ffff','xxxx'))+0 slot,
    id2 seq,
    lmode,
    request
    from v$lock, v$session
    where v$lock.type = 'TX'
    and v$lock.sid = v$session.sid
    and v$session.username = USER;
    
    USERNAME               SID        RBS       SLOT        SEQ     LMODE    REQUEST
    --------------- ---------- ---------- ---------- ---------- ---------- ----------
    UBACK                 1088          3         14     457984         6          0
    
    SQL> select XIDUSN, XIDSLOT, XIDSQN
      2  from v$transaction;
    
        XIDUSN    XIDSLOT     XIDSQN
    ---------- ---------- ----------
             3         14     457984
    
  • 다음과 같은 점을 발견할 수 있다.
    • V$LOCK 테이블의 LMODE는 6이고 request는 0이다. LMODE가 6은 배타 락이고 request가 0은 락을 요청하지는 않았지만 가지고 있다는 것을 의미한다.
    • 한 개의 로우만 있다. V$LOCK 테이블은 락 테이블이라기 보다는 큐잉 테이블에 가깝다.
    • ID1과 ID2 컬럼을 취해서 몇 가지 조작을 수행했다. 오라클은 세 가지의 16비트 숫자를 저장할 필요가 있지만 두 개의 컬럼만 사용했다. 그래서 첫 번째 컬럼 ID1은 두 가지 숫자를 가지고 있다. trunc(id1/power(2,16)) rbs에서는 2^16 값으로 나누고 bitand(id1,to_number('ffff','xxxx'))+0 slot에서는 상위 비트를 마스크(mask)한다. 그래서 한 개의 숫자(ID1)에 숨어 있는 두 개의 숫자를 다시 얻을 수 있다.
    • RBS, SLOT 그리고 SEQ 값은 V$TRANSACTION 정보와 일치한다. 이들이 트랜잭션 ID다.
  • 다른 세션에서 다음과 같이 실행한다.
    SQL> update emp_1 set ename = upper(ename);
    
    14 rows updated.
    
    SQL> update dept_1 set deptno = deptno-10;
    
    4 rows updated.
    
  • V$ 쿼리를 다시 실행한다.
    SQL> select username,
    v$lock.sid,
    trunc(id1/power(2,16)) rbs,
    bitand(id1,to_number('ffff','xxxx'))+0 slot,
    id2 seq,
    lmode,
    request
    from v$lock, v$session
    where v$lock.type = 'TX'
    and v$lock.sid = v$session.sid
    and v$session.username = USER;  
    
    USERNAME           SID        RBS       SLOT        SEQ      LMODE    REQUEST
    ----------- ---------- ---------- ---------- ---------- ---------- ----------
    UBACK             1043          3         11     457999          0          6
    
    UBACK             1043          8         38     451952          6          0
    
    UBACK             1088          3         11     457999          6          0
    
    SQL> select XIDUSN, XIDSLOT, XIDSQN
      2  from v$transaction;
    
        XIDUSN    XIDSLOT     XIDSQN
    ---------- ---------- ----------
             8         38     451952
             3         11     457999
    
  • 트랜잭션 ID가 (3,11,457999)가 새로운 트랜잭션을 시작했다는 것이다.
  • SID=7인 새 세션과 관련해서 이번에는 V$LOCK에 2개의 로우가 존재한다.
  • 한 로우는 세션이 소유한(LMODE가 6)인 락을 보여주고 또한 배탁 락에 대한 요청을 의미하는 REQUEST거 6인 로우도 보여준다.
  • RBS/SLOT/SEQ 값은 락을 보유하고 있는 트랜잭션 ID이다.
  • SID 703인 트랜잭션은 SID가 7인 트랜잭션을 블로킹하고 있다.
  • 좀 더 자세히 보려면 아래와 같이 하면 된다.
    SQL> select
    (select username from v$session where sid=a.sid) blocker,
    a.sid,
    ' is blocking ',
    (select username from v$session where sid=b.sid) blockee,
    b.sid
    from v$lock a, v$lock b
    where a.block = 1
    and b.request > 0
    and a.id1 = b.id1
    and a.id2 = b.id2; 
    
    BLOCKER                               SID 'ISBLOCKING'  BLOCKEE                               SID
    ------------------------------ ---------- ------------- ------------------------------ ----------
    UBACK                                1088  is blocking  UBACK                                1043
    
  • SID가 1088인 트랜잭션을 커밋하고 쿼리를 다시 실행하면 락을 요청한 로우가 사라진 것을 발견할 수 있다.
    SQL> select username,
    v$lock.sid,
    trunc(id1/power(2,16)) rbs,
    bitand(id1,to_number('ffff','xxxx'))+0 slot,
    id2 seq,
    lmode,
    request
    from v$lock, v$session
    where v$lock.type = 'TX'
    and v$lock.sid = v$session.sid
    and v$session.username = USER;  
    
    USERNAME             SID        RBS       SLOT        SEQ      LMODE    REQUEST
    ------------- ---------- ---------- ---------- ---------- ---------- ----------
    UBACK               1043          8         38     451952          6          0
    
    SQL> select XIDUSN, XIDSLOT, XIDSQN
      2  from v$transaction;
    
        XIDUSN    XIDSLOT     XIDSQN
    ---------- ---------- ----------
             8         38     451952
    
  • 락을 포기하는 순간 요청 로우는 사라졌다. 큐잉 메카니즘이기 때문이다.
    SQL> create table t 
    2    ( x int primary key,
    3      y varchar2(4000)
    4    ) 
    5    /
    
    Table created.
    
    SQL> insert into t (x, y)
    2    select rownum, rpad('*', 148, '*')
    3    from dual
    4    connect by level <= 46
    46 rows created.
    
    SQL> select length(y),
    2    dbms_rowid.rowid_block_number(rowid) blk,
    3    count(*), min(x), max(x)
    4    from t
    5    group by length(y), dbms_rowid.rowid_block_number(rowid);
    
    LENGTH(Y)    BLK COUNT(*)   MIN(X)  MAX(X)
    --------- ------ -------- -------- -------
          148   4599       46        1      46
    
  • 테이블 T는 동일한 블록에 46개의 로우를 담고 있다.
    SQL> create or replace procedure do_update(p_n in number)
    2    as
    3    pragma autonomous_transaction;
    4    l_rec t%rowtype;
    5    resource_busy exception;
    6    pragma exception_init(resource_busy, -54);
    7    begin
    8    select *
    9    into l_rec
    10   from t
    11   where x = p_n
    12   for update NOWAI
    14   do_update( p_n+1);
    15   commit;
    16   exception
    17   where resource_busy
    18   then
    19   dbms_output.put_line('locked out trying to select row '|| p_n);
    20   commit;
    21   when no_data_found
    22   then
    23   dbms_output.put_line('we finished - no problems ;);
    24   commit;
    25   end;
    26   /
    
    procedure created.
    
  • 라인 14는 반복적으로 락을 걸 새로운 기본키 값을 입력으로 해서 do_update 프로시져를 재귀적으로 호출한다.
  • 프로시져를 실행한다.
    SQL> exec do_update(1);
    
    locked out trying to select row 38
    PL/SQL procedure successfully completed
    
  • 출력 결과는 37로우까지 락을 걸 수 있었지만 38번째 로우를 위한 트랜잭션 슬롯이 소진되었음을 보여준다.
  • 주어진 블록에 대해 최대 37 트랜잭션이 동시에 블록을 액세스 할 수 있다.
  • 조금 더 작은 문자열을 가지고 예제를 다시 해본다.
    SQL> truncate table t;
    
    Table truncated.
    
    SQL> insert into t (x,y)
    2    select rownum, rpad('*', 147, '*')
    3    from dual
    4    connect by level <= 46;
    
    46 rows created
    
    SQL> select length(y)
    2    dbms_rowid.rowid_block_number(rowid) blk,
    3    count(*), min(x), max(x)
    4    from t
    5    group by length(y), dbms_rowid.rowid_block_number(rowid);
    
    LENGTH(Y)    BLK COUNT(*)   MIN(X)  MAX(X)
    --------- ------ -------- -------- -------
          147   4663       46        1      46
    
    SQL> exec do_update(1);
    
    we finish - no problem
    PL/SQL procedure successfully completed.
    
  • 이번에는 한 바이트 차이로 성공적으로 완료했다. 이 경우는 블록에 추가적인 46바이트의 공간으로 인하여 적어도 9개의 트랜잭션이 추가로 동일한 블록을 액세스할 수 있었다.

TM (DML Enqueue) Locks

  • TM 락은 내용을 변경하는 동안 테이블 구조를 변경하지 않도록 보장하는데 사용된다.
  • 이것은 다른 사용자가 테이블을 ALTER 또는 DROP 명령을 수행하지 못하도록 한다.
    |Session1|
    |SQL> select * from dept_1 for update nowait;
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    SQL> update dept_1 set deptno = deptno-10;
    4 rows updated.| 
    
    Session2|
    SQL> drop table dept_1;
    drop table dept_1
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified|
    
Note 오라클 11g release 2 이후에서는 DDL 대기 시간을 조절하기 위해서 DDL_LOCK_TIMEOUT을 설정할 수 있다. 이것은 일반적으로 ALTER SESSION 명령어를 통해 이루어진다. 예를 들면 DROP 테이블 명령을 내리기 전에 ALTER SESSION SET DDL_LOCK_TIMEOUT = 60; 을 먼저 수행할 수 있다. 서버로 보낸 DROP TABLE 명령어는 오류를 리턴하기 전에 60초 동안 대기한다.
  • 다음은 TM 락이 V$LOCK 테이블에서 어떻게 나타나는지 보여준다.
    SQL> create table t1 ( x int );
    
    Table created.
    
    SQL> create table t2 ( x int );
    
    Table created.
    
    SQL> insert into t1 values ( 1 );
    
    1 row created.
    
    SQL> insert into t2 values ( 1 );
    
    1 row created.
    
    SQL> select (select username                                                        
    from v$session                                                                      
    where sid = v$lock.sid) username,                                                   
    sid,                                                                                
    id1,                                                                                
    id2,                                                                                
    lmode,                                                                              
    request, block, v$lock.type                                                         
    from v$lock                                                                         
    where sid = (select sid                                                             
    from v$mystat                                                                       
    where rownum=1)                                                                     
    /                                                                                   
                                                                                        
    USERNAME               SID        ID1        ID2      LMODE    REQUEST      BLOCK TY
    --------------- ---------- ---------- ---------- ---------- ---------- ---------- --
    UBACK                 1088     217429          0          3          0          0 TM
                                                                                        
    UBACK                 1088     217430          0          3          0          0 TM
                                                                                        
    UBACK                 1088     655400     457568          6          0          0 TX
    
    SQL> select object_name, object_id
      2  from user_objects
      3  where object_name in ('T1','T2');
    
    OBJECT_NAME    OBJECT_ID
    -------------- ----------
    T1             217429
    
    T2             217430
    
  • 트랜잭션당 한 개의 TX 락을 얻을 수 있는 반면, 변경하는 객체 수만큼 TM 락을 얻을 수 있다.
  • TM 락의 ID1 컬럼은 DML 락이 걸린 객체의 ID라서 락이 걸려 있는 객체를 찾기 쉽다.

DDL Locks

  • DDL 락은 DDL 작업 동안 다른 세션에 의한 변경으로부터 객체를 보호하기 위해 자동으로 객체에 걸린다.

*아래와 같은 순서로 DDL은 실행된다.

Begin
Commit;
DDL-STATEMENT
Commit;
Exception
When others then rollback;
End;
  • 세 가지의 DDL 락이 존재한다.
    • 배타 DDL 락 : 다른 세션이 DDL 락 또는 TM(DDL) 락을 획득하지 못하게 한다. DDL 작업 동안 쿼리는 가능하지만 어떤 방법으로 테이블은 변경할 수 없다.
    • 공유 DDL 락 : 다른 세션에 의해 변경되는 것에 대비하여 참조하는 객체 구조를 보호하지만 데이터에 대한 변경은 허용된다.
    • Breakable Parse 락 : shared pool에 캐시된 실행계획처럼 객체 간의 의존 관계를 등록한다.
  • 아래의 문장을 수행하는 동안 테이블 T는 변경 대상에서 제외된다.
    SQL> Alter table t add new_column date;
    
    Table altered.
    
  • 오라클에서 몇몇 DDL 작업은 DDL 락 없이 일어날 수 있다.
    SQL> create index t_idx on t(x) ONLINE;
    
    Index created.
    
  • ONLINE 키워드는 실제로 인덱스를 만드는 방법을 변경한다.
  • 데이터 변경을 막는 배타 DDL 락을 획득하는 대신에 오라클은 테이블에 로우 레벨(모드 2)의 TM 락만 획득한다.
  • 다른 DDL은 막을 수 있지만 DML은 정상적으로 수행된다.
  • 어느 정도 크기를 갖는 테이블을 생성하여 ONLINE 작업을 살펴보자.
    SQL> create table t as select * from all_objects;
    
    Table created.
    
    SQL> select object_id from user_objects where object_name = 'T';
    
    OBJECT_ID
    ---------
        89791
    
  • 테이블 T에 대하여 인덱스를 생성하라.
    SQL> create index t_idx on t(owner, object_type, object_name) ONLINE;
    
    Index created.
    
  • 동시에 또 다른 세션에서 테이블 T에 걸린 락을 보기 위해 아래 쿼리를 실행한다.
    SQL> select (select username
      2  from v$session
      3  where sid = v$lock.sid) username,
      4  sid, id1, id2, lmode, request, block, v$lock, type
      5  from v$lock
      6  where id1 = 89791
      7  /
    
    USERNAME    SID    ID1    ID2    LOME   REQUEST  BLOCK TY
    ----------- ---- ----- ------ ------- --------- ------ --
    OPS$TKYTE   702  89791      0       3         0      0 DL
    OPS$TKYTE   702  89791      0       3         0      0 DL
    OPS$TKYTE   702  89791      0       4         0      0 OD
    OPS$TKYTE   702  89791      0       2         0      0 TM
    
  • 테이블 T를 대상으로 4개의 락을 볼 수 있다.
  • DL 락 2개는 direct load 락이다.
    • direct load 락은 인덱스 생성이 일어나는 동안 대상 테이블에 direct path load를 막는다.
  • 아래의 명령어를 처리하는 동안에 EMP와 DEPT에 대해 공유 락을 건다. 테이블의 내용을 변경할 수는 있으나 테이블의 구조를 변경할 수는 없다.
    SQL> Create view MyView
      2  as
      3  select emp.* 
      4  from emp, dept
      5  where emp.deptno = dept.deptno; 
    View created.
    
  • 마지막 타입의 DDL 락은 breakable parse 락이다. 세션이 문장을 파싱할 때 그 문장에 의해 참조되는 모든 객체를 대상으로 parse 락을 건다. 만약 참조되는 객체가 어떤 식으로던 drop 되거나 변경되면 파싱되어 캐쉬된 문장을 shared pool에서 무효화시키기 위해서 이 락을 취한다.
  • DDL 락의 정보를 보는 데 필요한 뷰는 DBA_DDL_LOCK이다.
    SQL> select session_id sid, owner, name, type,
      2  mode_held held, mode_requested request
      3  from dba_ddl_locks;
    
    SID     OWNER   NAME                TYPE                    HELD    REQUES
    ----    ------  ------------------  --------------------    ----    ------
    1012    EXBACK  EXBACK              18                      Null    None  
    1088    SYS     DBMS_CRYPTO         Body                    Null    None  
    1063    SYS     SCN_TO_TIMESTAMP    Table/Procedure/Type    Null    None  
    1088    SYS     SCN_TO_TIMESTAMP    Table/Procedure/Type    Null    None  
    1096    SYS     DBMS_PRVT_TRACE     Body                    Null    None  
    1088    SYS     UTL_RAW             Body                    Null    None 
    
  • 주의할 점은 OWNER는 락의 소유자가 아니라 락이 걸린 객체의 소유자를 나타낸다.
  • 살아있는 breakable parse 락을 보기 위해서 우선 저장 프로시저 P를 생성하고 실행한다.
    SQL> create or replace procedure p as begin null; end;
      2  /
    
    Procedure created.
    
    SQL> exec p;
    
    PL/SQL procedure successfully completed.
    
  • P에 대한 내용을 DBA_DDL_LOCKS 뷰에서 볼 수 있다.
    SQL> select session_id sid, owner, name, type,                                 
      2  mode_held held, mode_requested request                                    
      3  from dba_ddl_locks;                                                       
                                                                              
    SID     OWNER   NAME                TYPE                    HELD    REQUES
    ----    ------  ------------------  --------------------    ----    ------
    1012    EXBACK  EXBACK              18                      Null    None  
    1088    SYS     DBMS_CRYPTO         Body                    Null    None  
    1063    SYS     SCN_TO_TIMESTAMP    Table/Procedure/Type    Null    None  
    1088    SYS     SCN_TO_TIMESTAMP    Table/Procedure/Type    Null    None  
    1096    SYS     DBMS_PRVT_TRACE     Body                    Null    None  
    1088    SYS     UTL_RAW             Body                    Null    None  
    1088    UBACK   P                   Table/Procedure/Type    Null    None
    
  • 프로시져를 재컴파일하고 뷰를 다시 검색한다.
    SQL> alter procedure p compile;
    
    Procedure altered.
    
    SQL> select session_id sid, owner, name, type,
      2  mode_held held, mode_requested request
      3  from dba_ddl_locks;
    
    SID     OWNER   NAME                TYPE                    HELD    REQUES
    ----    ------  ------------------  --------------------    ----    ------
    1012    EXBACK  EXBACK              18                      Null    None  
    1088    SYS     DBMS_CRYPTO         Body                    Null    None  
    1063    SYS     SCN_TO_TIMESTAMP    Table/Procedure/Type    Null    None  
    1088    SYS     SCN_TO_TIMESTAMP    Table/Procedure/Type    Null    None  
    1096    SYS     DBMS_PRVT_TRACE     Body                    Null    None  
    1088    SYS     UTL_RAW             Body                    Null    None
    
  • parse 락이 사라진 것을 볼 수 있다. 해제되었기 때문이다.

Latches

  • 래치는 공유된 데이터 구조, 객체, 그리고 파일에 대한 다수 사용자 액세스를 조정하는데 사용되는 경량의 직렬화 장치이다.
  • shared pool에서 데이터베이스 블록 버퍼 캐시 또는 라이브어리 캐시처럼 특정 메모리 구조를 보호하는데 사용된다.

래치 spining

  • 래치는 락의 일종이고 락은 직렬화 장치며 직렬화 장치는 확장성을 저해한다는 점이다.
  • SQL 문을 파싱하는 것은 단순한 행위일지라도 shared pool에서 라이브어리 캐시와 관련된 데이터 구조에 대하여 수백 또는 수천 개의 래치를 획득하고 해제한다.
  • 래치를 기다리는 일은 값비싼 작업일 수 있다.
  • 프로세스는 래치를 즉시 획득할 수 없다면 프로세스 대기열로 가서 CPU를 포기하고 나중에 CPU 스케줄링 시 자기 차례가 돌아왔을 때 다시 시도하기보다는 CPU 상에 머물면서 지속적으로 시도한다.
  • 스핀하면서 꾸준하게 래치를 얻으로고 한 후에도 래치를 획득하는데 실패하면 그때는 프로세스가 sleep 하거나 자신을 CPU로부터 떼어내서 다른 작업을 수행할 것이다.
  • 단일 세션이 오랜 시간 동안 래치를 보유해서라기보다는 많은 세션이 동시에 래치를 원하고 또한 각 세션이 짧은 시간 동안 래치를 보유하기 때문에 발생하는 문제이다.
  • 래치를 얻기 위한 psuedo 코드는 아래와 같다.
        Loop
          for i in 1 .. 2000
          loop
                try to get latch
                if got latch, return
                if i = 1 then misses = misses+1
          end loop
          INCREMENT WAIT COUNT
          sleep
          Add WAIT TIME
       End loop;
    
  • 위 로직은 래치 획득을 시도하다가 실패하면 miss 카운트를 증가시킨다.
  • 일단 프로세스가 실패하면 계속하여 래치 획득을 시도하면서 정해진 횟수만큼 루프를 돌 것이다.
  • 이 중에 한 번이라도 획득 시도가 성공하면 리턴해서 계속해서 작업을 처리할 것이다.
  • 만약에 모두 실패하면 그 래치에 대한 sleep 카운트를 증가시킨 후 짧은 시간 동안 sleep할 것이다.
  • 상당히 많은 CPU를 사용하게 된다. CPU가 많이 소요되고 있어 시스템은 매우 분주한 듯이 보이지만 그리 많은 일을 하지는 않는다.

공유 자원을 래칭하는 비용 측정하기

  • 예제를 이용하여 shared pool을 래칭(래치를 획득하고 해제하는 행위)하는 비용을 알아보자.
  • 다중 사용자 환경에서 이들 프로그램을 평가하기 위해 측정값을 수집하기 위해 statspack을 사용할 것이다.
    1. 시스템의 현 상태를 수집하기 위해 statspack 스냅샷을 실행한다.
    2. 동일한 프로그램을 N번 실행한다. 모든 프로그램이 같은 테이블에 삽입하면서 발생할 수 있는 경합을 피하기 위해 각 프로그램이 자신만이 보유한 데이터베이스 테이블에 삽입하도록 한다.
    3. N번째 프로그램이 종료하는 즉시 또 다른 스냅샷을 확보한다.
Note 위 분석을 수행하는데 AWR(Automatic Workload Repository)을 사용하지 않는 이유가 있다. 누구나 statspack에 엑세스할 수 있기 때문이다. 물론 DBA가 statspack을 설치해야 하지만, 오라클 사용자는 모두 statspack에 접근 가능하다. 그래서 모든 사용자가 결과를 재현할 수 있도록 하고 싶기 때문이다.
  • 이 테스트는 하이퍼쓰레딩 기능이 활성화된 듀얼 CPU 장비에서 수행했다.
  • 한 명의 사용자가 삽입을 처리하기 위해 1개의 CPU를 사용한다면 두 명일 때는 2개의 CPU를 사용할 것이라 짐작할 수 있다.
  • SQL 문을 파싱하기 위해 공유 메모리 구조인 shared pool을 래칭할 필요가 있고, 다른 사람이 shared pool을 읽는 동안 변경할 수 없으므로 shared pool이 변경되는 중에는 읽을 수도 없다.
테스트 설정하기
  • 테스트를 위해서는 경합이 필요하다.
  • 경합을 측정하기 위해 다수의 사용자로 진행할 것이다.
  • 사용자당 한 테이블씩 10개의 테이블을 생성하고 테이블명은 T1에서 T10으로 한다.
    SQL> begin
      2  for i in .. 10
      3  loop
      4     for x in (select * from user_tables where table_name = 'T'||i )
      5     loop
      6        execute immediate 'drop table ' || x.table_name;
      7     end loop;
      8     execute immediate 'create table t' || i || '(x int)';
      9  end loop;
     10 end;
     11 /
    
    PL/SQL procedure successfully completed.    
    
  • 다음 절차대로 테스트를 진행한다.
    1. statpack.snap을 실행한다.
    2. 즉시 자바 루틴을 N번 시작한다. 여기서 N은 1부터 10(1명에서 10명의 동시 사용자를 의미)까지 변한다.
    3. N번 실행이 완료하기를 기다린다.
    4. statpack.snap을 실행한다.
    5. 마지막 2개의 statpackID에 대하여 statpack 리포트를 생성한다.
  • 다음의 실행에서 제공되는 숫자는 이 절차를 따라 수집되었다.
바인드 변수 없음
  • 첫 번째 인스턴스에서 자바 프로그램은 바인드 변수를 사용하지 않고, 데이터를 삽입하기 위해 문자열 연결을 사용할 것이다.
    import java.sql.*;
    public class instest
    {
    static public void main(String args[]) throws Exception
    {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Connection
    conn = DriverManager.getConnection
    ("jdbc:oracle:thin:@dellpe:1521:ora10gr1",
    "scott","tiger");
    conn.setAutoCommit( false );
    Statement stmt = conn.createStatement();
    for( int i = 0; i < 25000; i++ )
    {
    stmt.execute
    ("insert into "+ args[0] +
    " (x) values(" + i + ")" );
    }
    conn.commit();
    conn.close();
    }
    }
    
  • 단일 사용자 모드로 테스트를 실행했으며, statpack 리포트는 아래의 정보를 보여준다.
      Elapsed: 0.52 (mins)
    
    Cache Sizes (end)
    ~~~~~~~~~~~~~~~~~
                    Buffer Cache: 768M Std Block Size: 8K
                Shared Pool Size: 244M Log Buffer: 1,024K
    Load Profile
    ~~~~~~~~~~~~             Per Second Per Transaction
                        --------------- ---------------
    ...
                         Parses: 810.58 12,564.00
                    Hard parses: 807.16 12,511.00
    ....
    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~ % Total
    Event                                               Waits    Time (s) Call Time
    -------------------------------------------- ------------ ----------- ---------
    CPU time                                                           26 55.15
    class slave wait                                        2          10 21.33
    Queue Monitor Task Wait                                 2          10 21.33
    log file parallel write                                48           1 1.35
    control file parallel write                            14           0 .51
    
  • 참고로 SGA 구성 정보를 포함했으며 관련 통계는 아래와 같다.
    • Elapsed time of approximately 30 seconds
    • 807 hard parses per second
    • 26 CPU seconds used
  • 아래는 2개의 프로그램 실행하면 하드파싱이 1600회 뛸 것이고 CPU 시간은 아마도 52CPU 초로 2배 정도 뛸 것이다.
          Elapsed: 0.78 (mins)
    Load Profile
    ~~~~~~~~~~~~                         Per Second Per Transaction
                                    --------------- ---------------
                                   Parses: 1,066.62       16,710.33
                              Hard parses: 1,064.28       16,673.67
    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~ % Total
    Event                                               Waits    Time (s) Call Time
    -------------------------------------------- ------------ ----------- ---------
    CPU time                                                           74 97.53
    log file parallel write                                53           1 1.27
    latch: shared pool                                    406           1 .66
    control file parallel write                            21           0 .45
    log file sync                                           6           0 .04
    
  • CPU 시간은 세 배로 뛰었다. 오라클의 래치 구현 때문이다.
  • 멀티 CPU 장비에서 래치를 즉시 얻을 수 없을 때는 스핀한다. 스핀 행위가 CPU를 소비한다.
  • 프로세스 1은 shared pool에서 래치를 획득하기 위한 시도를 많이 한 후에야 프로세스 2가 그 래치를 보유하고 있다는 것을 알게 된다.
  • 프로세스 1이나 프로세스 2 모두 래치를 얻기 위해 스핀하는데 많은 CPU를 소비한다.
  • 실제 일은 얼마 하지도 못하고 대부분의 처리 시간을 자원을 가용할 때까지 기다리며 보냈다.
  • statspack 리포트 페이지를 'Latch Sleep Breakdown' 페이지까지 넘기면 다음 사실을 알 수 있다.
    Latch Name            Requests     Misses    Sleeps Sleeps 1->3+
    ---------------- ------------- ----------- -------- ------------
    shared pool          1,126,006     229,537      406 229135/398/4/0
    library cache        1,108,039      45,582        7 45575/7/0/0
    
  • 406이란 숫자가 Sleeps 컬럼에 나타난지 알아야 한다.
  • Latch Sleep Breakdown 리포트는 래치를 얻기 위해 스핀 루프에서 시도한 횟수와 실패한 횟수를 보여준다.
  • 226,537 misses는 Top5에서 나타나지 않는다.
  • 2개의 단위 작업을 수행하기 위해서 3개의 단위 CPU가 필요했다.
  • 이는 전적으로 공유 자원, 즉 shared pool을 필요로 하고 있다는 사실에 기인한다. 이것이 래칭의 본질이다.
  • 추가적으로 스핀에 위해서 시스템이 얼마나 많은 CPU 시간을 결정하는 것은 가능하지 않다.
  • 두 명의 사용자가 테스트를 하면서 아는 것이라고는 CPU를 74초 사용했다는 것과 shared pool에 대한 래치를 획득할 기회를 229,537번 놓쳤다는 것이다.
  • 기회를 놓칠 때마다 래치를 얻기 위한 시도를 계속하면서 몇 번이나 스핀했는지는 모르기 때문에 스핀하는데 CPU 시간을 얼마나 소비했고 처리하는데 얼마나 소비한지에 대해서는 측정할 방법이 없다.
  • 이 테스트에서 단일 사용자의 예를 비교해보면 우리는 자원을 기다리는데 래치가 스핀하는데 22 CPU초를 소비한 것을 알 수 있다.
바인드 변수 사용
  • 동일한 테스트지만 현저하게 적은 래치를 이용하는 프로그램을 사용할 것이다.
  • 바인드 변수를 사용하도록 코딩할 것이다.
    import java.sql.*;
    public class instest
    {
    static public void main(String args[]) throws Exception
    {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Connection
    conn = DriverManager.getConnection
    ("jdbc:oracle:thin:@dellpe:1521:ora10gr1",
    "scott","tiger");
    conn.setAutoCommit( false );
    PreparedStatement pstmt =
    conn.prepareStatement
    ("insert into "+ args[0] + " (x) values(?)" );
    for( int i = 0; i < 25000; i++ )
    {
    pstmt.setInt( 1, i );
    pstmt.executeUpdate();
    }
    conn.commit();
    conn.close();
    }
    }
    
  • 단일 리포트는 다음과 같다.
         Elapsed: 0.12 (mins)
    Load Profile
    ~~~~~~~~~~~~                Per Second Per Transaction
                           --------------- ---------------
    ...
                Parses:               8.43           29.50
           Hard parses:               0.14           0.50
    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~                                                      % Total
    Event                                               Waits    Time (s) Call Time
    -------------------------------------------- ------------ ----------- ---------
    CPU time                                                            4      86.86
    log file parallel write                                49           0      10.51
    control file parallel write                             4           0       2.26
    log file sync                                           4           0        .23
    control file sequential read                            542         0        .14
    
  • 위에 비교는 획기적이다.
  • 26CPU 초가 4 CPU초로 줄었다.
  • 807 하드 파싱도 0.14로 줄었다.
  • elapsed 시간도 45초에서 8초로 줄었다.
  • 바인드 변수를 사용할지 않았을 때 CPU 시간을 5/6 사용했다.
  • 이것은 전적으로 래치와 관련있지는 않다. 바인드 변수 없이 발생하는 CPU 시간은 SQL 최적화와 파싱하는데 사용된다.
  • SQL 파싱은 CPU를 집중적으로 사용하지만 정말 유용하지 않은 일에 5/6을 쓴다는 것은 대가가 크다.
  • 사용자 2명으로 테스트 한다.
    Elapsed: 0.20 (mins)
    Load Profile
    ~~~~~~~~~~~~                       Per Second Per Transaction
                                  --------------- ---------------
                 Parses:                     6.58           26.33
            Hard parses:                     0.17            0.67
    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~                                                      % Total
    Event                                               Waits    Time (s) Call Time
    -------------------------------------------- ------------ ----------- ---------
    CPU time                                                           11     89.11
    log file parallel write                                48           1      9.70
    control file parallel write                             4           0       .88
    log file sync                                           5           0       .23
    log buffer space                                        2           0       .05
    
  • CPU 시간은 단일 사용자일 때보다 약 2~2.5배 증가했다.
    Note 반올림 때문에 CPU 시간 4초는 실제로 3.5와 4.49 사이에 그리고 11은 10.5와 11.49 사이의 길이이다.
  • 더 나아가서 바인드 변수를 이용하여 두 명의 사용자가 사용한 CPU 양은 바인드 변수를 쓰지 않고 한 명의 사용자가 사용한 CPU 보다 적다.
  • statspack 리포트에서 래치 절을 보면 공유 폴과 라이브어리 캐시에 대한 경합이 너무 작아서 리포팅할 가치조차 없다는 것을 알 수 있다.
  • 더 깊이 들어가면 shared pool에 대한 래치는 50.367번 그리고 바인드 변수없는 두 명의 사용자 테스트에서는 1,000,000번 이상 요구된 것을 발견할 수 있다.
성능/확장성 비교
  • 표6.1은 사용자 수를 증가시키면서 래칭 결과 뿐만 아니라 각 구현마다 CPU 사용량을 요약하고 있다.

뮤텍스

  • 뮤텍스는 래치와 비슷한 직렬화 장치이며 상호 배타를 나타낸다.
  • 오라클 10g 릴리즈 1에서 도입되어 전통적인 래치 대신 사용되고 있다.
  • 래치에 비해서 훨씬 경량이다.
  • 뮤텍스 구현을 위한 명령어는 래치 구현에 비해 대략 1/5 정도의 코드로 가능하고 구현에 필요한 메모리도 1/7 정도로 덜 사용한다.
  • 경량이라는 점을 제외하고는 다른 면에서는 기능이 더 떨어진다.
  • 뮤텍스는 좀 더 가벼운 직렬화 장치이다. 어쩌면 래치보다 확장성이 뛰어나지만 여전히 직렬화 장치에 불과하다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.