메뉴 건너뛰기

bysql.net

7. Consistent vs. Current 모드 읽기

2010.05.31 07:17

휘휘 조회 수:10536

(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)






이 게시물을
목록
목록
번호 제목 글쓴이 날짜 조회 수
67 Front Page file 운영자 2010.05.17 154864
66 1 장. 오라클 아키텍처 운영자 2010.05.20 17839
65 1. 기본 아키텍처 [1] file 휘휘 2010.05.23 19898
64 3. 버퍼 Lock [1] 휘휘 2010.05.24 15225
63 2. DB 버퍼 캐시 file 휘휘 2010.05.24 21916
62 4. Redo file 휘휘 2010.05.24 11314
61 9. Snapshot too old balto 2010.05.30 8099
60 10. 대기 이벤트 balto 2010.05.30 8010
» 7. Consistent vs. Current 모드 읽기 휘휘 2010.05.31 10536
58 8. 블록 클린아웃 휘휘 2010.05.31 12280
57 11. Shared Pool file 실천하자 2010.05.31 18510
56 5. Undo file 토시리 2010.05.31 18645
55 1. 트랜잭션 동시성 제어 실천하자 2010.05.31 8631
54 6. 문장수준 읽기 일관성 file 토시리 2010.06.01 10432
53 2장. 트랜잭션과 Lock 운영자 2010.06.01 6894
52 1. Explain Plan 실천하자 2010.06.06 14661
51 2. AutoTrace 실천하자 2010.06.06 8597
50 3장. 오라클 성능 관리 운영자 2010.06.06 6693
49 3. SQL 트레이스 file balto 2010.06.06 21175
48 4. DBMS_XPLAN 패키지 balto 2010.06.06 10461
쓰기
태그