블록단위 I/O

• 오라클의 I/O는 Block단위로 이루어 진다.
• 하나의 컬럼만을 읽으려고 해도 레코드가 속한 블록 전체를 읽게 됨
• 하나의 블록을 액세스해 그 안에 저장돼 있는 모든 레코드를 순차적으로 읽어 들인다면 설령 무거운 디스크 I/O를 수반하더라도 비효율은 없으며 이를 {+}Sequential 액세스{+}{}라고 한다.
• 레코드 하나를 읽으려고 블록을 통째로 액세스한다면 그것은 메모리 버퍼에서 읽더라도 비효율이 존재 하고 이를 {+}Random액세스{+}{}라고 한다.
• SQL 성능을 좌우하는 가장 중요한 성능지표는 {+}액세스하는{+} {+}블록{+} {+}개수{+}{}이다.
• 블록 단위 I/O는 버퍼 캐시와 데이터 파일 I/O모두에 적용된다.

• 메모리 버퍼 캐시에서 블록을 읽고 쓸 때
• 데이터파일에 저장된 데이터 블록을 직접 읽거나 쓸 때 (Direct Path I/O)
• 데이터파일에서 DB 버퍼 캐시로 블록을 적재할 때 : Single Block Read 또는 Multiblock Read 방식을 사용
• 버퍼 캐시에서 변경된 블록을 다시 데이터파일에 저장할 때 : Dirty 버퍼를 주기적으로 데이터파일에 기록하는 것을 말하며, DBWR 프로세스에 의해 수행된다. 성능향상을 위해 한 번에 여러 블록씩 처리한다.

• 오라클 Dictionary Cache는 {+}로우{+} {+}단위로 I/O를{+} {+}수행{+}{}한다. 이로 인해 '로우 캐시'라고 부르기도 한다.
• 오라클에서 허용하는 블록 크기는 2k, 4k, 8k, 16k, 32k, 64k이다. 데이터베이스를 생성할 때 DB_BLOCK_SIZE를 지정하며, 다른 크기의 블록을 동시에 사용하려면 각각 별도의 Tablespace 와 버퍼 Pool을 구성해 주어야 한다. System Table Space는 Default로 8K이다.

(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>drop table t;
테이블이 삭제되었습니다.

SQL>create table t
2 as
3 select * from all_objects
4 order by dbms_random.value;
테이블이 생성되었습니다.

SQL>select count(*) from t;

COUNT(*)
----------
13225


SQL>select count(*) from t
2 where owner like 'SYS%';

COUNT(*)
----------
7315
Rows Row Source Operation
-- --- ---- ----- ------ ------- ---------------------------------------------------
1 SORT AGGREGATE (cr=177 pr=0 pw=0 time=5790 us)
7315 TABLE ACCESS FULL T (cr=177 pr=0 pw=0 time=22003 us)

• 7315개 레코드를 선택하기 위해 13225개 레코드를 스캔 했으므로 선택도는 55%로 이다. 읽은 블록의 개수는 177개 이다.

SQL>select count(*) from t
2 where owner like 'SYS%'
3 and object_name = 'ALL_OBJECTS';

count(*)
----------
1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=177 pr=0 pw=0 time=1872 us)
1 TABLE ACCESS FULL T (cr=177 pr=0 pw=0 time=1850 us)

• 1개의 레코드를 선택하려고 13,225개 레코드를 스캔 했으므로 선택도는 0.007%이다. 선택도가 매우 낮으므로 테이블 Full Scan 비효율이 크다. 읽은 블록은 177똑같다.

SQL>create index t_idx on t (owner, object_name);

SQL>select /*+ index(t t_idx) */ count(*) from t
2 where owner like 'SYS%'
3 and object_name = 'ALL_OBJECTS';

count(*)
----------
1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=60 pr=0 pw=0 time=4844 us)
4352 TABLE ACCESS SAMPLE T (cr=60 pr=0 pw=0 time=13115 us)

• 참조하는 컬럼이 모두 인덱스에 있으므로 인덱스만 스캔하고 결과를 낼 수 있다. 인덱스를 스캔하면서, 60개의 블록을 읽고 1개 레코드를 얻었다.
• 인덱스 선두 컬럼이 '='조건이 아니므로 owner like 'SYS%' 조건에 해당하는 로우가 읽어야 할 대상 범위지만, 다행히 스캔 시작 지점은 owner='SYS' 조건과 object_name >= 'ALL_OBJECTS' 조건에 의해 결정된다.

SQL>select /*+ index(t t_idx) */ count(*) from t
2 where owner like 'SYS%'
3 and ((owner = 'SYS' and object_name >= 'ALL_OBJECTS' ) or (owner >'SYS'));

count(*)
----------
7213

• 1/7213 * 100 = 0.01%의 선택도 이다. 테이블 뿐만 아니라 인덱스를 Seqeuntial 액세스 방식으로 스캔할 때도 비효율이 있는 것을 알 수 있다.
• 인덱스 스캔의 효율은조건절에 사용된 컬럼과 연산자 형태, 인덱스 구성에 의해 영향을 받는다.

SQL>drop index t_idx;

인덱스가 삭제되었습니다.

SQL>create index t_idx on t (object_name, owner);

인덱스가 생성되었습니다.

SQL>select /*+ index(t t_idx) */ count(*) from t
2 where owner like 'SYS%'
3 and object_name = 'ALL_OBJECTS';

count(*)
----------
1


Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=50 us)
1 INDEX RANGE SCAN T_IDX (cr=2 pr=0 pw=0 time=27 us)(object id 74891)

• Index를 변경하여 I/O가 줄어 들었다. 두개의 CR 블록 읽기가 발생했다. 인덱스 루트 블록과 하나의 리프 블록만 읽었기 때문이다.
• 선택도가 100%이므로 가장 효율적인 방식으로 Sequential 액세스를 수행하였다.

(3) Random 액세스 발생량 줄이기

• 클러스터링 팩터가 좋을수록 버퍼 Pinning에 의한 블록 I/O감소 효과는 더 커진다.

문서에 대하여

• 최초작성자 : 미녀씨
• 최초작성일 : 2010년 01월 15일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.*