by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

블록 단위 IO

Added by 이가혜, last edited by 이가혜 on 1월 15, 2010



블록단위 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'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.