블록단위 IO (by csahn79) [2010.05.01]
블록단위 I/O
- 모든 DBMS는 블록 단위로 I/O가 발생한다.
- 블록 안에 있는 데이터 중에 한 건을 읽어도 속한 전체 블록을 읽는다.
- 액세스 방법
- Sequential 액세스 : 하나의 블록을 읽을 때 그 안에 저장돼 있는 모든 레코드를 순차적으로 읽는 방법
- Random 액세스 : 레코드 하나을 읽으려고 블록을 통째로 액세스 하는 방법
- 아래의 쿼리는 서버에서 발생하는 I/O 측면에서 일량이 동일하다.
SQL> select ename from emp where sal >= 2000;
SQL> select * from emp where sal >= 2000;
- SQL 성능을 좌우하는 가장 중요한 지표는 액세스 하는 블록의 개수이다.
- 블록 단위 I/O는 버퍼 캐쉬와 데이터파일 I/O 모두에 적용된다.
- 메모리 버퍼 캐쉬에서 블록을 읽고 쓸 때
- 데이터파일에 저장된 데이터 블록을 직접 읽거나 쓸 때(Direct Path I/O)
- 데이터파일에서 DB 버퍼 캐쉬로 블록을 적재할 때 : Single Block Read or Multiblock Read 방식 사용
- 버퍼 캐쉬에서 변경된 블록을 다시 데이터 파일에 저장할 때 : Dirty 버퍼를 주기적으로 데이터파일에 기록하는 것을 말하며, DBWR 프로세스에 의해 수행된다. 성능향상을 위해 한 번에 여러 블록씩 처리
Sequential VS. Random 액세스
- {+}Sequential 액세스{+}{}는 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식이다. 인덱스 리프 블록에 위치한 모든 레코드는 {+}포인터{+}{}를 따라 논리적으로 연결되어 있다. 즉, 이 포인터를 따라 스캔하는 방식을 Sequential 액세스이라 한다. 위 그림에서 (5)번이 여기에 해당한다. 테이블 레코드 간에는 포인터로 연결되어 있지 않지만 테이블을 스캔할 때 물리적으로 저장된 순서대로 읽어 나가므로 이것 또한 Sequential 액세스 방식이다. 오라클은 내부적으로 Sequential 액세스를 향상시키기 위하여 Mulitiblock I/O, 인덱스 Prefetch 같은 기능을 사용한다.
{+}포인터란?{+}{} 포인터는 어떤 곳의 메모리 주소를 말한다. 즉, 메모리의 주소를 알고 있는 것이다. 어떤 변수나 함수 등의 주소를 가리키는 개념을 포인터라고 한다.
- 포인터는 주소를 저장하는 변수(메모리)를 의미한다.(단, 그 변수가 주소를 가지고 있어야 한다.)
- 주소를 저장하고 있기 때문에 언제든지 그 주소의 메모리에 방문할 수 있고 접근할 수 있다.
- 그래서 포인터는 '주소를 가리킨다'고 말하는 것이다. '주소를 참조한다'고도 한다.
void main( )
{
int n = 10;
int * pn = &n;
printf("%d\n", n);
printf("%d\n", *pn);
}
==Output==
10
10
- {+}Random 액세스{+}{}는 레코드간 논리적, 물리적인 순서를 따르지 않고, 한 건을 읽기 위해 한 블록씩 접근하는 방식이다. 그림에서 ①,②,③,④,⑤,⑥이 여기에 해당한다. ①,②,③번 액세스는 인덱스 깊이에 따라 1~3 블록 정도 읽는 것이므로 대개 성능에 영향을 미치지 않고, 주로 ④,⑤번 액세스가 성능 저하를 일으킨다. NL 조인에서 Inner 테이블 액세스를 위해 사용되는 인덱스는 ①,②,③번까지도 성능에 지대한 영향을 미칠 수 있다. Random 액세스 성능 향상을 위하여 오라클은 내부적으로 버퍼 Pinning, 테이블 Prefetch 같은 기능을 사용한다.
- 블록 단위 I/O를 하더라도 한번 액세스할 때 Sequential 방식으로 그 안에 저장된 모든 레코드를 읽는다면 비효율이 없다고 할 수 없다. 반면, 하나의 레코드를 읽으려고 한 블록씩 읽는다면 매우 비효율적이다.
- 총 읽은 레코드에서 결과 집합으로 선택되는 비중을 {+}선택도(Selectivity){+}{}라고 한다. 결국, Sequential 액세스 효율은 선택도에 의해 결정된다. 즉, {+}같은 결과 건수를 내면서 얼마나 적은 레코드를 읽느냐로 효율성을 판단할 수 있다.{+}{}
- I/O 튜닝의 핵심 원리는 다음과 같다.
- Sequential 액세스의 선택도를 높인다.
- Random 액세스 발생량을 줄인다.
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(*)
----------
49993
SQL>select count(*) from t
2 where owner like 'SYS%';
COUNT(*)
----------
24707
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=691 pr=0 pw=0 time=15591 us)
24707 TABLE ACCESS FULL T (cr=691 pr=0 pw=0 time=74159 us)
- 24707개 레코드를 선택하기 위해 49993개 레코드를 스캔 했으므로 선택도는 49%로 이다. 읽은 블록의 개수는 691개 이다.
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=691 pr=0 pw=0 time=4676 us)
1 TABLE ACCESS FULL T (cr=691 pr=0 pw=0 time=4654 us)
- 1개의 레코드를 선택하려고 49993개 레코드를 스캔 했으므로 선택도는 0.002%이다. 선택도가 매우 낮으므로 테이블 Full Scan 비효율이 크다. 읽은 블록은 691개 똑같다.
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=77 pr=76 pw=0 time=73964 us)
1 INDEX RANGE SCAN T_IDX (cr=77 pr=76 pw=0 time=73939 us)(object id 54391)
- 참조하는 컬럼이 모두 인덱스에 있으므로 인덱스만 스캔하고 결과를 낼 수 있다. 인덱스를 스캔하면서, 77개의 블록을 읽고 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(*)
----------
14641
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=79 pr=0 pw=0 time=9708 us)
14641 CONCATENATION (cr=79 pr=0 pw=0 time=204995 us)
12905 INDEX RANGE SCAN T_IDX (cr=67 pr=0 pw=0 time=25834 us)(object id 54391)
1736 INDEX RANGE SCAN T_IDX (cr=12 pr=0 pw=0 time=5227 us)(object id 54391)
- 1/14641* 100 = 0.007%의 선택도 이다. 테이블 뿐만 아니라 인덱스를 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 TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=32 us)
1 INDEX RANGE SCAN T_IDX (cr=2 pr=0 pw=0 time=21 us)(object id 54393)
- Index를 변경하여 I/O가 줄어 들었다. 두 개의 CR 블록 읽기가 발생했다. 인덱스 루트 블록과 하나의 리프 블록만 읽었기 때문이다.
- 선택도가 100%이므로 가장 효율적인 방식으로 Sequential 액세스를 수행하였다.
Random 액세스 발생량 줄이기
SQL>drop index t_idx ;
인덱스가 삭제되었습니다.
SQL> create index t_idx on t(owner);
인덱스가 생성되었습니다.
SQL> select object_id from t
2 where owner ='SYS'
3 and object_name = 'ALL_OBJECTS';
OBJECT_ID
----------
2377
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=739 pr=0 pw=0 time=39522 us)
22934 INDEX RANGE SCAN T_IDX (cr=51 pr=0 pw=0 time=12072 us)(object id 54404)
- 인덱스로부터 22934건을 출력했으므로 테이블을 그 횟수만큼 방문하게 된다. 그 과정에서 688(=739-51)개 블록을 Random 엑세스했다. 내부적으로 블록을 22934번 방문했지만 Random 엑세스 횟수가 688번 머무는 것은 Pinning 때문이다.
SQL>drop index t_idx ;
인덱스가 삭제되었습니다.
SQL>create index t_idx on t(owner, object_name);
인덱스가 생성되었습니다.
SQL>select object_id from t
2 where owner ='SYS'
3 and object_name = 'ALL_OBJECTS';
OBJECT_ID
----------
2377
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=57 us)
1 INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=49 us)(object id 54404)
- object_name을 필터링할 수 있도록 object_name을 추가한다. 인덱스로부터 1건을 출력했으므로 테이블을 1번 방문한다. 실제 발생한 테이블 Random 엑세스는 1(=4-3)이다. 같은 쿼리라도 인덱스 구성이 바뀌자 Random 엑세스가 대폭 감소한 것이다.