01. 블록 단위 IO (by hj4867) [2012.12.10]
06. I/O 효율화 원리
- 라이브러리 캐시 최적화
- 데이터베이스 Call 최소화
- I/O 효율화 및 버퍼캐시 최적화
- 셋다 중요하지만 그 중에서도 세번째가 제일 중요!!
- 앞쪽 두가지 튜닝 요소는, 핵심 원리만 이해하고 몇 가지 튜닝 기법만 잘 숙지하면 누구나 쉽게 적용할 수 있는 것
- I/O 효율화 튜닝을 잘하려면 인덱스 원리, 조인 원리, 옵티마이저 원리에 대한 이해 가 필수
- 이를 바탕으로 실전에서 고급 SQL 활용을 통해 문제 해결 경험 요구
- I/O 효율화를 달성하기는 쉽지 않은데다 장기간 훈련 이 필요
01. 블록 단위 I/O
- 오라클을 포함한 모든 DBMS에서 I/O는 블록(다른 DBMS에서는 주로 페이지(page)라는 용어를 사용) 단위 구성
: 블록 단위로 I/O = 하나의 레코드에서 하나의 컬럼만을 읽으려 할 때도 레코드가 속한 블록 전체 를 읽게 됨을 뜻 - Sequential 액세스 : 하나의 블록을 액세스해서 그 안에 저장돼 있는 모든 레코드를 순차적으로 읽어들이는 것
무거운 디스크 I/O를 수반하더라도 비효율은 없다. - Random 액세스 : 레코드 하나를 읽으려고 블록을 통째로 액세스 하는 것
메모리 버퍼에서 읽더라도 비효율이 존재함.
- SQL 성능을 좌우하는 가장 중요한 성능지표는 액세스하는 블록 개수
- 옵티마이저의 판단에 가장 큰 영향을 미치는 것도 액세스해야 할 블록 개수
- 옵티마이저가 인덱스를 이용해 테이블을 액세스할지 아니면 Full Table Scan 할지를 결정하는 데 있어 가장 중요한 판단 기준은 읽어야 할 레코드 수가 아니라 읽어야 하는 블록 개수
블록 단위 I/O는 버퍼 캐시와 데이터파일 I/O 모두에 적용된다.
- 메모리 버퍼 캐시에서 블록을 읽고 쓸 때
- 데이터파일에 저장된 데이터 블록을 직접 읽거나 쓸 때(Direct Path I/O)
- 데이터파일에서 DB 버퍼 캐시로 블록을 적재할 때 : Single Block Read 또는 Multiblock Read 방식을 사용
- 버퍼 캐시에서 변경된 블록을 다시 데이터파일에 저장할 때 : Dirty 버퍼를 주기적으로 데이터파일에 기록하는 것을 말하며, DBWR 프로세스에 의해 수행된다. 성능향상을 위해 한 번에 여러 블록씩 처리한다.
참고
1. 오라클 딕셔너리 정보를 저장하는 딕셔너리 캐시는 로우 단위로 I/O를 수행
(딕셔너리 캐시를 '로우 캐시'라고도 부르는 이유)
2. Block = 데이터베이스를 생성할 때 db_block_size를 지정
3. 오라클에서 허용 되는 범위 = 2k, 4k, 8k, 16k, 32k, 64k (Default 8K)
4. 다른 크기의 블록을 동시에 사용하려면 각각 별도의 테이블스페이스와 버퍼 Pool을 구성
(1) Sequential vs. Random 액세스
- Sequential 액세스는 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식
인덱스 리프 블록에 위치한 모든 레코드는 포인터를 따라 논리적으로 연결돼 있고, 이 포인터를 따라 스캔하는 것을 Sequential 액세스 방식이다.
테이블 레코드 간에는 포인터로 연결되어 있지 않지만 테이블을 스캔할 때는 물리적으로 저장된 순서대로 읽어 나가므로 이것 또한 Sequential 액세스 방식이다.
Sequential 액세스 성능을 향상시키려고 오라클 내부적으로 Multiblock I/O, 인덱스 Prefetch 같은 기능을 사용한다.
- Random 액세스는 레코드간 논리적, 물리적인 순서를 따르지 않고, 한 건을 읽기 위해 한 블록씩 접근 하는 방식
①,②,③,④,⑤,⑥이 Random Acess에 해당하며, ①,②,③번 액세스는 인덱스 깊이에 따라 1~3블록 정도 읽는 것이므로 대개 성능에 영향을 미치지 않고, ,④, ⑥번 액세스가 성능 저하를 일으킨다.
NL 조인에서 Inner 테이블 액세스를 위해 사용되는 인덱스는 ①,②,③번까지도 성능에 지대한 영향을 미칠 수 있다.
Random 액세스 성능을 향상시키려고 오라클 내부적으로 버퍼 Pinning, 테이블 Prefetch 같은 기능을 사용
- 블록단위 I/O를 하더라도 한번 액세스할 때 Sequential 방식으로 그 안에 저장된 모든 레코드를 읽으면 비효율이 없다고 할 수 있으나, 하나의 레코드를 읽으려고 한 블록씩 읽는다면 매우 비효율적이다.
Sequential 액세스 효율은 Selectivity(선택도)에 의해 결정된다.
즉, 같은 결과 건수를 내면서 얼마나 적은 레코드를 읽느냐로 효율성을 판단 할 수 있다. (100개를 읽었는데 그중 99개를 버리고 1개를 취한다면 Random 액세스 보다 나을게 없다)
- I/O 튜닝의 핵심 원리
- Sequential 액세스의 선택도를 높인다.
- Random 액세스 발생량을 줄인다.
(2) Sequential 액세스 선택도 높이기
SQL> CREATE TABLE t2
2 AS
3 SELECT * FROM all_objects
4 ORDER BY dbms_random.value;
Table created.
SQL> SELECT COUNT(*) FROM t2;
COUNT(*)
----------
49906
-- T2 테이블에는 49,906건의 레코드가 저장돼 있음.
SQL> SELECT COUNT(*) FROM t2
2 WHERE owner LIKE 'SYS%';
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=691 pr=0 pw=0 time=13037 us)
24613 TABLE ACCESS FULL T2 (cr=691 pr=24456 pw=0 time=98473 us)
위 쿼리는 24,613개 레코드를 선택하려고 49,906개 레코드를 스캔했으므로 선택도는 49%다.
(선택도 : 24,613 / 49,906 X 100 = 49%)
Full Scan 선택도가 이 정도면 나쁘지 않아 보인다. 읽은 블록 수는 691개였다.
SQL> SELECT COUNT(*) FROM t2
2 WHERE owner LIKE 'SYS%'
3 AND object_name = 'ALL_OBJECTS';
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=691 pr=0 pw=0 time=7191 us)
1 TABLE ACCESS FULL T2 (cr=691 pr=0 pw=0 time=7150 us)
위 쿼리는 1개 레코드를 선택하려고 49,906개 레코드를 스캔했으므로 선택도는 0.002%다.
선택도가 매우 낮으므로 테이블 Full Scan 비효율이 크다.
여기서도 읽은 블록 수는 똑같이 691개다.
이처럼 테이블을 스캔하면서 읽은 레코드 중 대부분 필터링되고 일부만 선택된다면 아래처럼 인덱스를 이용하는게 효과적이다.
SQL> CREATE INDEX t_idx ON t2 (owner, object_name);
Index created.
SQL> SELECT /*+ index(t t_idx) */ COUNT(*) FROM t2
2 WHERE owner LIKE 'SYS%'
3 AND object_name = 'ALL_OBJECTS';
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE (cr=76 pr=0 pw=0 time=7009 us)
1 INDEX RANGE SCAN T_IDX (cr=76 pr=0 pw=0 time=6972 us)(Object ID 89340571)
- 위 쿼리에서 참조하는 컬럼이 모두 인덱스에 있으므로 인덱스만 스캔하고 결과 리턴
- 인덱스를 스캔하면서 76개 블록을 읽고 1개 레코드를 출력
- 인덱스 선두 컬럼이 '='조건이 아니므로 owner like 'SYS%' 조건에 해당하는 로우가 읽어야 할 대상 범위지만
다행히 스캔 시작 지점은 owner='SYS' 조건과 object_name >= 'ALL_OBJECTS' 조건에 의해 결정
- 1개 레코드를 얻으려고 실제 스캔한 레코드 수 확인 Script
SQL> SELECT /*+ index(t t_idx) */ COUNT(*) FROM t2
2 WHERE owner like 'SYS%'
3 AND ((owner = 'SYS' and object_name >= 'ALL_OBJECTS' ) or (owner >'SYS'));
COUNT(*)
----------
14587
- 선택도 : 1 / 14,587 X 100 = 0.007%
- 테이블뿐만 아니라 인덱스를 Sequential 액세스 방식으로 스캔할 때도 비효율 이 있는 것
- 인덱스는 테이블과 달리 정렬된 순서를 유지하므로 일정 범위를 읽다가 멈출 수 있다는 점 만 다름
- 인덱스 스캔의 효율은 조건절에 사용된 컬럼과 연산자 형태, 인덱스 구성 에 의해 영향을 받음
-- 인덱스 컬럼 순서를 변경하고 같은 쿼리를 수행.
SQL> DROP INDEX t_idx;
Index droped.
SQL> CREATE INDEX t_idx ON t2 (object_name, owner);
Index created.
SQL> SELECT /*+ index(t t_idx) */ COUNT(*) FROM t2
2 WHERE owner LIKE 'SYS%'
3 AND object_name = 'ALL_OBJECTS';
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=67 us)
1 INDEX RANGE SCAN T_IDX (cr=2 pr=0 pw=0 time=51 us)(Object ID 89340584)
- 인덱스의 루트 블록, 하나의 리프 블록만 읽어서 3개의 CR 블록 읽음
- 한 건을 얻으려고 스캔한 건수도 한 건(정확히 얘기하면, one-plus 스캔까지 두 건)
- 선택도 : 1 / 1 X 100 = 100%이므로 가장 효율적인 방식으로 Sequential 액세스를 수행
(3) Random 액세스 발생량 줄이기
Random 액세스 발생량을 낮추는 방법.
인덱스에 속하지 않는 컬럼(object_name)을 참조하도록 쿼리를 변경함으로써 테이블 액세스가 발생하도록 할 것이다.
SQL> DROP INDEX t_idx;
SQL> CREATE INDEX t_idx ON t2 (owner);
SQL> SELECT object_id FROM t2
2 WHERE owner = 'SYS'
3 AND object_name = 'ALL_OBJECTS';
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.005 0 227 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 2.000 8.980 16110 16151 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 2.010 8.986 16110 16378 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 TABLE ACCESS BY INDEX ROWID T2 (cr=16151 pr=16110 pw=0 time=3709697 us)
26263 INDEX RANGE SCAN T_IDX (cr=59 pr=57 pw=0 time=79997 us)(Object ID 89343114)
- 왼쪽에 보이는 Rows는 각 수행 단계에서 출력(Flow-Out)된 로우 수
- 인덱스로부터 26,263건을 출력했으므로 테이블을 그 횟수만큼 방문
- 그 과정에서 16,092(=16,151-59)개 블록을 Random 액세스 발생
- 내부적으로 블록을 26,263번 방문했지만 Random 액세스 횟수가 16,092번에 발생한 이유는 버퍼 Pinning 효과
- 클러스터링 팩터가 좋을수록 버퍼 Pinning에 의한 블록 I/O 감소 효과는 더 커짐
최종 한 건을 선택하려고 너무 많은 Random 액세스가 발생
object_name을 필터링하려고 테이블을 많이 방문한 것이므로 인덱스 액세스 단계에서 필터링할 수 있도록 object_name을 추가해보자.
SQL> DROP INDEX t_idx;
SQL> CREATE INDEX t_idx ON t2 (owner, object_name);
SQL> SELECT object_id FROM t2
2 WHERE owner = 'SYS'
3 AND object_name = 'ALL_OBJECTS';
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.010 0.013 0 230 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.000 0.002 1 5 0 1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.010 0.015 1 235 0 1
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 TABLE ACCESS BY INDEX ROWID T2 (cr=5 pr=1 pw=0 time=1878 us)
1 INDEX RANGE SCAN T_IDX (cr=4 pr=0 pw=0 time=30 us)(Object ID 89340592)
- 인덱스로부터 1건을 출력했으므로 테이블을 1번 방문
- 실제 발생한 테이블 Random 액세스도 1(=5-4)번이다.
- 같은 쿼리를 수행했는데 인덱스 구성이 바뀌자 테이블 Random 액세스가 대폭 감소