- 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>
| 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 모드로 갱신할때 생기는 현상
| 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까지 있음
| 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 모드로 갱신할때 생기는 현상
| 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는 샐패
| 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>
< 상황2>
| 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; |
| 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>
| 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);
| 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;
update 계좌2
set 총잔고 = 계좌2.잔고 +
(select 잔고 from 계좌 where 계좌번호=계좌2.계좌번호)
where 계좌번호=7788;
- 스칼라 서브쿼리는 대부분 Consistent 모드로 읽기 수행
- 계 좌2.잔고 는 Current모드로 읽고, 계좌1.잔고는 Consistent 모드로 읽음
- update중 계좌1에 변경이 발생해도 update,delete 문이 시작되는 시작점(변경전) 값을 사용한다.
| TX1> | |
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의 차이가 발생
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)