2. 변경이 잦은 테이블에 예상보다 많은 I/O가 발생하는 이유
1. "낙관적 락킹"과 매우 흡사하게 LAST_UPDATED 컬럼을 유지하기 위해 트리거를 사용함.
2. 데이터 웨어하우스 테이블에 초기 데이터를 적재하기 직전, 소스 시스템에서 SYSDATE를 쿼리함으로써 바로 그 순간이 몇 시인지를 어딘가에 저장해 둠.
3. 그런 다음 트랜잭션 시스템으로부터 모든 로우를 끌고와서, 데이터 웨어하우스에 초기 적재를 실시함(예로 09시)
4. 소스 시스템 변경분을 데이터 웨어하우스에 반여하기 위해, 다시 바로 그 순간이 몇시인지를 저장해 둠(예로 10시)
그리고 오전 9시 이후로 변경된 레코드를 모두 끌고와서 기존 데이터와 merge함.
SELECT NVL(MIN(TO_DATE(START_TIME, 'MM/DD/RR HH24:MI:SS')), SYSDATE)
FROM V$TRANSACTION
;
NVL(MIN(TO_D
------------
30-OCT-12
1 row selected.
;
-- 1. (Session_1) SERIALIZABLE 트랜잭션으로 데이터 생성 후 I/O 확인
-- 1-1) 데이터 생성
DROP TABLE T;
CREATE TABLE T(X INT);
INSERT INTO T VALUES (1);
-- 2. 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T');
-- 3. SERIALIZABLE 트랜잭션으로 변경
ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
Session altered.
;
SET AUTOTRACE ON STATISTICS
SELECT * FROM T;
X
----------
1
1 row selected.
Elapsed: 00:00:00.06
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
221 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
;
-- 2. (Session_2) 데이터를 UPDATE 하여 블록에 변경사항을 가함
BEGIN
FOR i IN 1..10000 LOOP
UPDATE T
SET X = X + 1;
COMMIT;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
;
-- 3. (Session_1) 다시 Session_1으로 돌아와 I/O를 확인하면, SERIALIZABLE 트랜잭션이므로 변경 이전의 데이터를 읽기 위해 언두를 다량 읽음
SELECT * FROM T;
X
----------
1
1 row selected.
Elapsed: 00:00:00.15
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10010 consistent gets -- 언두 블록이 15 -> 10,010으로 늘어났다!
0 physical reads
0 redo size
220 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
;
-- 4. (Sessio_1) 여기서 다시 쿼리를 수행하면 이전 수준으로 돌아옴
-- 그 이유는 오라클은 같은 블록에 대한 여러 버전을 버퍼 캐시에 동시에 저장할 수 있기 때문에,
-- 해당 블록을 과거로 되돌리고 나면, 그 버전이 캐시에 남아 이후에 계속 실행되는 쿼리에서 바로 액세스 할 수 있음.
SELECT * FROM T;
X
----------
1
1 row selected.
Elapsed: 00:00:00.07
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
220 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
;
SELECT FILE#,
BLOCK#,
COUNT(*)
FROM V$BH
GROUP BY FILE#,
BLOCK#
HAVING COUNT(*) > 3
ORDER BY 3
;
FILE# BLOCK# COUNT(*)
---------- ---------- ----------
4810 84506 4
22 14869 4
2132 2100473 4
2271 178976 4
2356 1936398 4
2040 1671414 4
4799 85864 4
2049 715171 4
4807 98222 4
2324 1934053 4
2043 2591599 4
2213 1960435 4
2302 65337 4
4809 84625 4
2036 895154 4
3262 2399985 4
2307 2091625 4
2323 1063966 4
4798 2513 4
2285 1154416 4
2051 2511297 4
2284 1808751 4
2022 2468806 4
22 14872 4
2176 2033241 4
4799 2068 4
2045 2425172 4
2251 2141565 4
2105 2452238 4
2218 1923460 4
2092 2599917 4
2300 1415918 4
2187 795997 4
2017 778142 4
3007 252073 4
2126 911954 4
2269 1907492 4
2094 2511264 4
2282 1427974 4
2312 1829968 4
2197 2065599 4
2044 2006377 5
1266 2241621 5
65 2068227 5
23 167411 5
2044 2429026 5
2001 1986341 5
FILE# BLOCK# COUNT(*)
---------- ---------- ----------
2307 297117 5
2284 286132 5
2291 1692436 5
4804 84553 5
2058 886494 5
2026 2248263 5
2046 2600673 5
140 133922 5
2193 179334 5
2022 672017 5
2126 1540958 5
1698 576000 5
2305 1954005 5
4811 84978 5
2007 652576 5
2089 1704122 6
22 15685 6
1578 385890 6
2056 1988286 6
3007 252070 7
1321 668968 7
169 1291828 7
1579 382270 7
2175 1428310 7
2158 661809 7
1999 2038850 8
4808 84372 8
2075 765957 8
75 rows selected.
;