오라클을 포함한 모든 DBMS에서 I/O는 블록단위로 이루어진다.

이는, 하나의 레코드의 한 컬럼만 읽으려 할때도 해당 블록 전체를 읽어야 됨을 의미한다.

하나의 블록에는 여러 레코드의 정보가 들어가 있다.

다음은, 블록의 이미지이다.

SQL 성능을 좌우하는 가장 중요한 성능지표는 액세스하는 블록의 개수이다.

옵티마이져가 풀스캔을 할지 인덱스 테이블 액세스를 할지를 판단하는 기준도, 레코드 수가 아닌 블록 개수이다.

(1)Sequential vs. Random 액세스

Sequential 액세스

• 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식 (⑤)
  
• 인덱스 리프의 포인터를 따라 스캔한는 것 (인덱스 리프의 포인터는 논리적으로 연결되있다.)
• 성능향상을 위해 내부적으로 Multiblock I/O 및 Index Prefetch 기능이 사용

Random 액세스

• 레코드간 논리/물리적 순서를 따르지 않고 한 건을 읽기위해 한 블록씩 접근하는 방식(①、②、③、④、⑥)
  
• 성능향상을 위해 내부적으로 버퍼 Pinnig 및 Table Prefetch 기능이 사용

블록 단위 I/O를 하더라도 Sequential 방식으로 그 안에 저장된 모든 레코드를 읽는다면 비효율이 없다고 할 수 있다. 반면, 하나의 레코드를 읽기위해 한 블록씩 읽는다면 매우 비효율적이다.

그렇다하여, Sequential 방식은 효율이 더 좋다는 이야기는 아니다.

선택도가 낮을 경우, 즉, 100개를 읽고 그중 1개를 취한다면 Random 액세스보다 나을게 없다.

결국, Sequential 방식은 같은 결과수 라면 얼마나 적은 레코드를 읽었냐에 따라 효율이 높아진다.

I/O튜닝의 핵심 원리

1. Sequential 액세스의 선택도를 높인다.
2. Random 액세스의 발생량을 줄인다.
   

(2)Sequential 액세스의 선택도 높이기

테스트 테이블을 만든다. (49,906건의 데이터가 들어간다.)

create table t as
select * from all_objects
order by dbms_random.value;

다음은 선택도 49%의 쿼리다. ( 24613/49906 )

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

Rows  Row Source Operation
----- --------------------------------------------------------------------
1     SORT AGGREGATE (cr=691 pr=0 pw=0 time=13037 us)
24613 TABLE ACCESS FULL T (cr=691 pr=0 pw=0 time=98473 us)

읽은 블록 수는 691개이다.

다음은 선택도 0.002%의 쿼리다. ( 1/49906 )

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

Rows  Row Source Operation
----- --------------------------------------------------------------------
1     SORT AGGREGATE (cr=691 pr=0 pw=0 time=7191 us)
1     TABLE ACCESS FULL T (cr=691 pr=0 pw=0 time=7150 us)

읽은 블록 수는 여전히 691개이다.

선택도가 낮은 경우는 인덱스를 이용하는게 효과적이다.

create index t_idx on t (owner, object_name);

select /*+ index(t t_idx) */ count(*) from t
where owner like 'SYS%'
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 55337)

읽은 블록 수는 76개로 현저히 줄었다.

인덱스는 테이블과 달리 정렬된 순서를 유지하므로 일정범위를 읽다가 멈출 수 있다는 점이 다르다.

인덱스 스캔의 효율은 조건절에 사용된 컬럼과 연산자 형태, 인덱스 구성에 의해 영향을 받는다.

다음과 같이 인덱스 컬럼순서를 바꾸고 테스트를 해보자.

drop index t_idx;
create index t_idx on t (object_name, owner);

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

Rows  Row Source Operation
----- --------------------------------------------------------------------
0     STATEMENT
1       SORT AGGREGATE (cr=2 pr=0 pw=0 time=44 us)
1        INDEX RANGE SCAN T_IDX (cr=2 pr=0 pw=0 time=23 us)(object id 55338)

읽은 블록 수는 2개로 엄청나게 줄었다.

가장 좋은 효율의 Sequential 액세스를 수행한것이 된다.

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

인덱스에 속하지 않는 컬럼을 참조하도록 쿼리를 변경하여 테이블 액세스가 발생하도록 한다.

create index t_idx on t(owner);

select object_id from t
where owner = 'SYS'
and object_name = 'ALL_OBJECTS';

Rows  Row Source Operation
----- --------------------------------------------------------------------
0     STATEMENT
1       TABLE ACCESS BY INDEX ROWID T (cr=739 pr=0 pw=0 time=38822 us)
22934    INDEX RANGE SCAN T_IDX (cr=51 pr=0 pw=0 time=115672 us)(object id 55339)

위 결과를 보면, 내부적으로 블록을 22934번 방문했지만 Random액세스 횟수는 688(739 - 51)번에 머문다.

이는 클러스터링 팩터가 좋아 버퍼 Pinning에 의한 블록 I/O가 감소했기 때문이다.

클러스터링 팩터

군집성 지표. 즉, 데이터가 모여있는 정도를 나타내는 지표이다.

데이터베이스에서는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도를 의미한다.
"인덱스 클러스터링 팩터가 좋다."고 하면 인덱스 정렬 순서와 테이블 정렬 순서가 서로 비슷하다는 것을 말한다.

이 지표는 쿼리 수행 시 테이블에 대한 블록 I/O를 가늠해 볼 수 있는 중요한 수치이다.

예) select * from table_a where id in (1, 2, 3) 의 경우를 살펴보자. (페이지≒블록)

• 클러스터링 팩터가 나쁜경우 (최소한 3개의 블록에 I/O가 발생.)
  

 

• 클러스터링 팩터가 좋은 경우 (최소한 1개의 블록에 I/O가 발생.)
  

이번에도 인덱스에 컬럼을 추가해보자.

create index t_idx on t(owner, object_name);

select object_id from t
where owner = 'SYS'
and object_name = 'ALL_OBJECTS';

Rows  Row Source Operation
----- --------------------------------------------------------------------
0     STATEMENT
1       TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=67 us)
1        INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=51 us)(object id 55340)

Random액세스 횟수가 엄청나게 감소한것을 볼 수 있다.

이처럼, 인덱스 구성을 바꾸는 것으로 I/O감소에 효과를 볼 수 있다.