메뉴 건너뛰기

bysql.net

7. Consistent vs. Current 모드 읽기

2010.05.31 07:17

휘휘 조회 수:10537

(1) Consistent 모드 읽기와 Current 모드 읽기의 차이점

  • Consistent 모드 (gets in consistent mode)
: SCN 확인 과정을 거치며 쿼리가 시작된 시점을 기준으로 일관성 있는 상태로 블록을 액세스
  • SQl trace : query
  • Auto trace: consistent gets

 select 문에서 읽은 블록의 대부분이 여기에 해당, current 블록을 읽더라도 consistent 모드라며 query 항목 집계

  • Current 모드 (gets in current mode) 
: SQL 시작시점이 아니라 데이터에 접근하는 순간의 최종값을 읽으려고 블록을 액세스
  • SQL trace: current 
  • Auto trace: db block gets

주 발생시점
  •  DML 문 수행시
  •  select for update 수행시
  •  8i full 스캔을 포함하는 select ( full scan 할  extent map 정보를 읽기 위해 세그먼트 헤더에 접근할때 현시점 정보가 필요)
  •  디스크 소트가 필요할 정도의 대량의 데이터 정렬시


SQL> select * from emp where empno=7788;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=7788)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        824  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed






(2) Consistent 모드로 갱신 할때 생기는 현상


<상황 1>

<tx1>

<tx2>
update emp set sal=sal+100
where empno= 7788;
t1


t2
update emp set sal=sal+200
where empno=7788;
commit;
t3


t4
commit;





Consistent 모드 읽기 중심으로 생각하면 최종결과는 1,200이 되며 이것은 TX1의 결과가 사라지는 LOST UPDADE가 발생


oracle- 실 수행결과 ( 주) Consistent 모드만 사용된 경우가  아님)

TX1
TX2

SQL> select empno,sal from emp where empno=7788;

     EMPNO        SAL
---------- ----------
      7788       1000




SQL> update emp set sal=sal+100 where empno=7788;

1 row updated.



Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
        332  redo size
        669  bytes sent via SQL*Net to client
        578  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit;

Commit complete.

SQL>















































SQL> update emp set sal=sal+200 where empno=7788;

1 row updated.



Statistics
----------------------------------------------------------
          1  recursive calls
          2  db block gets
          2  consistent gets
          0  physical reads
        356  redo size
        667  bytes sent via SQL*Net to client
        578  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit;

Commit complete.

SQL> select empno,sal from emp where empno=7788;

     EMPNO        SAL
---------- ----------
      7788       1300





(3) Current 모드로 갱신할때 생기는 현상


<tx1>

<tx2>
update emp set sal=2000
where empno=7788
and sal=1000;
t1


t2
update emp set sal=3000
where empno=7788
and sal=2000;
commit;
t3


t4
commit;

  • current 모드로 동작한다면 tx2가 t1이 commit 로 기다렸다가 update 를 진행하게 되므로
    최종결과는 3000이 됨

자 료중 no는  1~100,000까지 있음

<tx1>

<tx2>
update t set no=no+1
where no>50000;
t1


t2
insert into t values(100001,100001);

t3
commit;
commit;
t4


index 사용시 update 도중에 insert 가 일어난다면 50001 rjsdl rodtlsehlsek
full table scan이라면 update시점과 insert시점에 사용된 블록에 따라 달라진다.



(4) Consistent 모드로 읽고, Current 모드로 갱신할때 생기는 현상


<tx1>

<tx2>
update emp set sal=sal+100
where empno=7788
and sal=1000;
t1


t2
update emp set sal=sal+200
where empno=7788
and sal=1000;
commit;
t3


t4
commit;

tx2가 읽히는 시점에 아직 tx1이 commit되지 않았으므로 tx2의 조건에 따라 갱신이이루어져야함
but 수행결과는 타 dbms(모두 current모드로 동작)와 같이 tx2의 update는 샐패

<tx1>
<tx2>
SQL> update emp set sal=sal+100
where empno=7788
and sal=1000; 

1 row updated.




Statistics
----------------------------------------------------------
        145  recursive calls
          1  db block gets
         31  consistent gets
          0  physical reads
        356  redo size
        678  bytes sent via SQL*Net to client
        591  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit;

Commit complete.

SQL>
























SQL> update emp set sal=sal+200
where empno=7788
and sal=1000; 

0 rows updated.


Statistics
----------------------------------------------------------
          1  recursive calls
          2  db block gets
          8  consistent gets
          0  physical reads
         64  redo size
        676  bytes sent via SQL*Net to client
        591  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> commit;

Commit complete.







(5) Consistent 모드로 갱신대상을 식별하고, Current 모드로 갱신


for c in
    (
        select rowid rid from emp                    ----> 단계1) consistent
        where empno = 7788 and sal = 1000
    )
loop
        update emp set sal=sal+200                ----> 단계2) Current
        where empno = 7788
        and    sal=1000
        and rowid = c.rid;
end loop;



  • 단계1)
    - 수정 삭제할 대상 레코드의 rowid를 Consistent모드로 검색
  • 단계2)
    - Rowid가 가리크는 레코드를 로우lock 설정후 Current 모드로 실제 update/delete 수행
    current모드로 다시한번 조건을 필터링


예
 < 상황2>

<TX1>

<TX2>
update emp set sal = 2000
where empno=7788
and sal = 1000;
t1


t2
 update emp set sal=3000
where empno = 7788
and sal=2000;
commit;
t3


t4
commit;

<TX1>
<TX2>
SQL> select empno,sal from emp where empno=7788;

     EMPNO        SAL
---------- ----------
      7788       1000


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        465  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> update emp set sal=2000 where empno=7788 and sal=1000;

1 row updated.



Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
          2  consistent gets
          0  physical reads
        356  redo size
        670  bytes sent via SQL*Net to client
        588  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit;

Commit complete.

SQL>





















































SQL> update emp set sal=3000 where empno=7788 and sal=2000;

0 rows updated.

SQL>



  • t2 가 시작되는 시점에  t1에 의해 실행된  update 쿼리가 아직 commit 되지않았 으므로
    sal=1000으로 인식되어 tx2의 update 진행되지 않음

  • but) SQL Server : sal값이 3000으로 변경 성공 , Oracle : 2000


< 상황3>
<tx1>

<tx2>
update t set no=no+1
where no>50000;
t1


t2
insert into t values(100001,100001);

t3
commit;
commit;
t4


sample table 생성

 create table t as select rownum as no,rownum as data from dual connect by level <= 100000;

 create index t1# on t ( no);


<TX1>
<TX2>
SQL> update t set no=no+1
2 where no>50000;
 



























50000 rows updated.




Statistics
----------------------------------------------------------
        471  recursive calls
     255508  db block gets
        743  consistent gets
        110  physical reads
   36405572  redo size
        677  bytes sent via SQL*Net to client
        570  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      50000  rows processed







SQL> commit;






SQL> insert into t values(100001,100001);

1 row created.


Statistics
----------------------------------------------------------
          1  recursive calls
         32  db block gets
          5  consistent gets
          3  physical reads
          0  redo size
        675  bytes sent via SQL*Net to client
        570  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit;

Commit complete.

SQL>





(6) 오라클에서 일관성 없게 값을 갱신하는 사례



case1

update 계좌2
set 총잔고 = 계좌2.잔고 +
        (select 잔고 from 계좌 where 계좌번호=계좌2.계좌번호)
where 계좌번호=7788;


  • 스칼라 서브쿼리는 대부분 Consistent 모드로 읽기 수행

  • 계 좌2.잔고 는 Current모드로 읽고, 계좌1.잔고는 Consistent 모드로 읽음
  • update중 계좌1에 변경이 발생해도 update,delete 문이 시작되는 시작점(변경전) 값을 사용한다.



TX1>
<TX2>


SQL> select acc1.amt, acc2.amt, acc2.tamt, acc1.amt+acc2.amt tamt2
 from acc1,acc2
  2  where acc1.accno=7788
  3  and acc2.accno=acc1.accno;

       AMT        AMT       TAMT      TAMT2
---------- ---------- ---------- ----------
      1000       1000       2000       2000


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ACC2"."ACCNO"=7788)
   5 - access("ACC1"."ACCNO"=7788)


Statistics
----------------------------------------------------------
        418  recursive calls
          0  db block gets
         74  consistent gets
          0  physical reads
          0  redo size
        577  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> update acc1 set amt=amt+100 where accno=7788;

1 row updated.



Statistics
----------------------------------------------------------
          3  recursive calls
          3  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        681  bytes sent via SQL*Net to client
        579  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> update acc2 set amt=amt+200 where accno=7788;

1 row updated.



Statistics
----------------------------------------------------------
          3  recursive calls
          1  db block gets
          2  consistent gets
          0  physical reads
        292  redo size
        680  bytes sent via SQL*Net to client
        579  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit;

Commit complete.

SQL> select acc1.amt, acc2.amt, acc2.tamt, acc1.amt+acc2.amt tamt2
 from acc1,acc2
2   where acc1.accno=7788
3   and acc2.accno=acc1.accno;

       AMT        AMT       TAMT      TAMT2
---------- ---------- ---------- ----------
      1100       1200       2200       2300


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ACC2"."ACCNO"=7788)
   5 - access("ACC1"."ACCNO"=7788)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        577  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

































SQL> update acc2 set tamt=acc2.amt +
 (select amt from acc1 where accno=acc2.accno)
  2  where accno=7788;

1 row updated.


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ACCNO"=7788)
   4 - access("ACCNO"=:B1)


Statistics
----------------------------------------------------------
          1  recursive calls
          2  db block gets
          5  consistent gets
          0  physical reads
        332  redo size
        679  bytes sent via SQL*Net to client
        632  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit;

Commit complete.

SQL>





  • acc2.amt를 읽을 Current 모드에서 읽지만 acc1.amt 를 읽을때 consistent 모드에서 읽었기때문에 2200 과 2300의 차이가 발생



case2

update 계좌2
set 총잔고=(select 계좌2.잔고 + 잔고 from 계좌1
                where 계좌번호 = 계좌2.계좌번호)
where 계좌번호 = 7788;

  • 계좌2.잔고가 Current모드이므로 서브쿼리내에서 참조하기위해 서브쿼리자체도 Current모드로 동작하여
  • 수행중 변경이 발생한다면 새로운 변경된값을 기준으로 작업이 진행됨


TX1
TX2
SQL> select acc1.amt, acc2.amt, acc2.tamt, acc1.amt+acc2.amt tamt2
from acc1,acc2
  2  where acc1.accno=7788
  3  and acc2.accno=acc1.accno;

       AMT        AMT       TAMT      TAMT2
---------- ---------- ---------- ----------
      1000       1000       2000       2000


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ACC2"."ACCNO"=7788)
   5 - access("ACC1"."ACCNO"=7788)


Statistics
----------------------------------------------------------
        418  recursive calls
          0  db block gets
         74  consistent gets
          0  physical reads
          0  redo size
        577  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> update acc1 set amt=amt+100 where accno=7788;

1 row updated.


Statistics
----------------------------------------------------------
        568  recursive calls
          3  db block gets
        112  consistent gets
          0  physical reads
          0  redo size
        681  bytes sent via SQL*Net to client
        579  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> update acc2 set amt=amt+200 where accno=7788;

1 row updated.

Statistics
----------------------------------------------------------
        152  recursive calls
          1  db block gets
         28  consistent gets
          0  physical reads
        356  redo size
        681  bytes sent via SQL*Net to client
        579  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit;

Commit complete.

SQL>select acc1.amt, acc2.amt, acc2.tamt, acc1.amt+acc2.amt tamt2
from acc1,acc2
where acc1.accno=7788
and acc2.accno=acc1.accno;


       AMT        AMT       TAMT      TAMT2
---------- ---------- ---------- ----------
      1100       1200       2300       2300



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        577  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>



































SQL> update acc2 set tamt=
(select acc2.amt+amt from acc1 where accno=acc2.accno)
where accno=7788; 

1 row updated.



Statistics
----------------------------------------------------------
          1  recursive calls
          6  db block gets
         14  consistent gets
          0  physical reads
        780  redo size
        679  bytes sent via SQL*Net to client
        630  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> commit;

Commit complete.

SQL>




  • acc1.amt 와 acc2.amt2 모두 current 모드로 읽었기 때문에 일관성 있게 갱신 (2300)






이 게시물을
목록
목록
쓰기
태그