Lock Types
- 오라클은 일반적으로 아래와 같이 3가지 락 타입을 가지고 있다.
- DML locks : 데이터를 동시에 변경할 수 있는 메커니즘을 제공한다.
- DDL locks : 객체 구조의 정의를 보호한다.
- Internal locks and latches : 내부 자료구조를 보호하기 위해 내부 락(internal lock)과 래치를 사용한다. 래치는 오라클이 사용하는 경량의 저수준의 직렬 장치이며, 기능에서는 락과 비슷하다.
DML Locks
TX (Transaction) Locks
- TX 락은 트랜젝션이 첫 번째 변경을 시작할 때 획득하며, 커밋 또는 롤백을 수행할 때까지 유지된다.
- 다른 세션이 트랜잭션을 완료할 때까지 기다릴 수 있는 큐잉 메커니즘으로 사용된다.
- 락을 거는 과정은 다음과 같다.
- 락을 걸고 싶은 로우의 주소를 찾는다.
- 락 관리자에서 줄을 선다.
- 리스트에 락을 건다.
- 리스트를 검색하여 다른 누군가 해당 로우에 락을 걸었는지 조회한다.
- 여러분이 락을 걸었다는 사실을 입증하기 위해 리스트에 새 엔트리를 만든다.
- 리스트에 락을 푼다.
- 나중에 커밋할 때 다음과 같은 절차를 계속 해야 한다.
- 다시 락 관리자에 줄을 선다.
- 락 리스트에 락을 건다.
- 리스트를 검색하여 설정한 락을 해제한다.
- 리스트에 락을 푼다.
- 락을 획득하고 해제하는 과정은 다음과 같다.
- 락을 걸고 싶은 로우의 주소를 찾는다.
- 해당 로우로 간다.
- 그 자리에 있느 로울에 락을 건다.
- 세 개의 V$ 테이블을 이용하여 락에 대한 정보를 알 수 있다.
- V$TRANSACTION : 활동 중인 모든 트랜젝션에 대한 엔트리를 담고 있다.
- V$SESSION : 로그인한 세션을 보여준다.
- V$LOCK : 락을 기다리는 세션 뿐만 아니라 보유 중인 모든 enqueue 락에 대한 엔트리를 포함하고 있다.
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.
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
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을 사용할 것이다.
- 시스템의 현 상태를 수집하기 위해 statspack 스냅샷을 실행한다.
- 동일한 프로그램을 N번 실행한다. 모든 프로그램이 같은 테이블에 삽입하면서 발생할 수 있는 경합을 피하기 위해 각 프로그램이 자신만이 보유한 데이터베이스 테이블에 삽입하도록 한다.
- 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.
- 다음 절차대로 테스트를 진행한다.
- statpack.snap을 실행한다.
- 즉시 자바 루틴을 N번 시작한다. 여기서 N은 1부터 10(1명에서 10명의 동시 사용자를 의미)까지 변한다.
- N번 실행이 완료하기를 기다린다.
- statpack.snap을 실행한다.
- 마지막 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을 쓴다는 것은 대가가 크다.
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 정도로 덜 사용한다.
- 경량이라는 점을 제외하고는 다른 면에서는 기능이 더 떨어진다.
- 뮤텍스는 좀 더 가벼운 직렬화 장치이다. 어쩌면 래치보다 확장성이 뛰어나지만 여전히 직렬화 장치에 불과하다.