태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Write Consistency?

오라클 2009.12.14 16:09
얼마전 특정 DML이 지나치게 일량이 많은 문제에 대한 문의가 있었습니다. 비록 그 문제에 대한 원인은 아니었지만, 덕분에 Write Consistency 문제를 한번 논의해야겠다는 필요성을 느꼈습니다.

간단한 테스트를 통해서 논의해보겠습니다. 우선 다음과 같이 테이블을 하나 만듭니다.

UKJA@ukja1021> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

UKJA@ukja1021> create table t1
  2  as
  3  select level as c1, rpad('x',1000) as c2
  4  from dual
  5  connect by level <= 10000
  6  ;

Table created.
테이블 T1에 대해 트리거를 만들어서 로우 한건이 업데이트가 될 때마다 pkg_temp.g_update_cnt 값을 1씩 증가시킵니다.
UKJA@ukja1021> create or replace package pkg_temp
  2  is
  3  	g_update_cnt number;
  4  end;
  5  /

Package created.

UKJA@ukja1021> -- create trigger
UKJA@ukja1021> create or replace trigger trg1
  2  after update on t1
  3  for each row
  4  begin
  5  	pkg_temp.g_update_cnt := pkg_temp.g_update_cnt + 1;
  6  end;
  7  /
다음과 같이 10,000건을 업데이트하면 pkg_temp.g_update_cnt 값은 10,000이 됩니다.
UKJA@ukja1021> exec pkg_temp.g_update_cnt := 0;

PL/SQL procedure successfully completed.

UKJA@ukja1021> 
UKJA@ukja1021> update t1 set c2 = rpad('y',1000)
  2  where c1 = c1
  3  ;

10000 rows updated.

UKJA@ukja1021> commit;

Commit complete.

UKJA@ukja1021> 
UKJA@ukja1021> exec dbms_output.put_line('update cnt = ' || pkg_temp.g_update_cnt);
update cnt = 10000

PL/SQL procedure successfully completed.
이제 다음과 같은 방법으로 테스트를 변경합니다.
  1. 세션 A에서 10,000건을 업데이트 시작합니다.
  2. 세션 A가 아직 마지막 로우를 업데이트하기 전에 세션 B에서 마지막 로우의 값을 바꾸고 Commit을 수행합니다.
  3. 세션 A가 업데이트를 마친 후 pkg_temp.g_update_cnt 값을 확인해봅니다.
세션 A에서 10,000건을 업데이트 시작합니다.
UKJA@ukja1021> exec pkg_temp.g_update_cnt := 0;

PL/SQL procedure successfully completed.

UKJA@ukja1021> 
UKJA@ukja1021> update t1 set c2 = rpad('y',1000)
  2  where c1 = c1
  3  ;
....
세션 A가 아직 마지막 로우를 업데이트하기 전에 세션 B에서 마지막 로우의 값을 바꾸고 Commit을 수행합니다.
UKJA@ukja1021> update t1 set c1 = c1+1 where c1 = 10000;

1 row updated.

UKJA@ukja1021> commit;

Commit complete.
세션 A가 업데이트를 마친 후 pkg_temp.g_update_cnt 값을 확인해봅니다. 놀랍게도 pgk_temp.g_update_cnt의 값은 19,999건입니다!
...
10000 rows updated.

UKJA@ukja1021> commit;

Commit complete.

UKJA@ukja1021> exec dbms_output.put_line('update cnt = ' || pkg_temp.g_update_cnt);
update cnt = 19999

PL/SQL procedure successfully completed.
이말은 10,000건이 아닌 그 두배에 해당하는 20,000건 정도가 업데이트되었다는 것을 의미합니다. 이것을 흔히 Restart 메커니즘이라고 부릅니다.
  1. 세션 A가 마지막 로우를 변경하기 위해서 일단 Consistent Read 모드로 해당 블록을 읽어들입니다.
  2. 세션 A는 쿼리가 시작된 후에 해당 로우가 세션 B에서 변경되고 Commit된 것을 확인합니다.
  3. UPDATE 문의 where c1 = c1 조건에서 Consistency 체크가 이루어집니다. 세션 A는 마지막 로우는 UPDATE가 시작된 후에 변경되었기 때문에 해당 조건이 만족하는지 만족하지않는지를 확인하는 것이 불가능하다고 판단합니다.
  4. 이런 경우 세션 A는 지금까지의 변경을 롤백하고 UPDATE 문을 다시 실행합니다. 이것을 흔히 Restart 메커니즘이라고 부릅니다. 따라서 9,999 + 10,000 = 19,999건의 로우가 변경된 것으로 관찰된 것입니다.
Restart의 부작용은 SQL*Trace의 결과에서도 잘 나타납니다. Restart가 발생한 경우 똑같이 10,000 개의 로우를 Update하지만 훨씬 많은 일을 해야합니다. 롤백을 하고 다시 실행해야하기 때문입니다.
-- Restart가 이루어지지 않은 경우
update t1 set c2 = rpad('y',1000)
where c1 = c1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          7          0           0
Execute      1      0.34       0.52          0       1443      12992       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.35       0.53          0       1450      12992       10000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  T1 (cr=1487 pr=0 pw=0 time=530716 us)
  10000   TABLE ACCESS FULL T1 (cr=1432 pr=0 pw=0 time=74012 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log buffer space                                5        0.01          0.05
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

-- Restart가 이루어진 경우
update t1 set c2 = rpad('y',1000)
where c1 = c1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      1.25       6.29          0       4323      67568       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.25       6.29          0       4323      67568       10000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  UPDATE  T1 (cr=4411 pr=0 pw=0 time=5643605 us)
  30000   TABLE ACCESS FULL T1 (cr=4297 pr=0 pw=0 time=180259 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  log file switch completion                      4        0.99          2.28
  log file switch (checkpoint incomplete)         2        0.38          0.44
  log buffer space                               18        0.68          1.98
  enq: TX - row lock contention                   1        0.07          0.07
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************
이러한 Restart 메커니즘을 흔히 오라클은 Read Consistency 뿐만 아니라 Write Consistency를 가지고 있다고 하기도 합다. 더 정확하게 말하면 오라클의 Read Consistency 메커니즘이 DML에 미치는 특수한 영향이라고 불러도 좋겠습니다.
저작자 표시
신고
Trackback 0 : Comment 1
  1. 아삽 2010.12.22 14:06 신고 Modify/Delete Reply

    참 좋은 내용이네요.
    큰 테이블 대량 업데이트시 간섭 트랜잭션들이 많을수록 일량이 엄청나게 증가할 것 같네요.
    이 경우 가능하다면 테이블에 배타적 락을 걸고 수행하는 게 좋을 것 같구요.

    이와 관련한 힌트가 11gr2 에서 새로 나왔다는데 retry_on_row_change 라고 합니다.
    http://rwijk.blogspot.com/2009/10/three-new-hints.html 에서 보았습니다.
    그런데 용도는 아직 잘 모르겠더라구요. 참고하시기 바랍니다.^^

    언제나 좋은 정보 참 감사합니다.

Write a comment

티스토리 툴바