[1] 블록단위 I/O

• 모든 DBMS는 블록 단위로 I/O가 발생한다.
• 블록 안에 있는 데이터 중에 한 건을 읽어도 속한 전체 블록을 읽는다.

(1) 액세스 방법

• Sequential 액세스 : 하나의 블록을 읽을 때 그 안에 저장돼 있는 모든 레코드를 순차적으로 읽는 방법이다.

1. 아래의 쿼리는 서버에서 발생하는 I/O 측면에서 일량이 동일하다.

SQL> select ename from emp where sal >= 2000;
SQL> select * from emp where sal >= 2000;

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

3. 블록 단위 I/O는 버퍼 캐쉬와 데이터파일 I/O 모두에 적용된다.

• 메모리 버퍼 캐쉬에서 블록을 읽고 쓸 때
• 데이터파일에 저장된 데이터 블록을 직접 읽거나 쓸 때(Direct Path I/O)
• 데이터파일에서 DB 버퍼 캐쉬로 블록을 적재할 때
  • Single Block Read 또는 Multiblock Read 방식을 사용한다.
• 버퍼 캐쉬에서 변경된 블록을 다시 데이터 파일에 저장할 때

[2] Sequential VS. Random 액세스

(1) Sequential 액세스

• 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식이다.
• 인덱스 리프 블록에 위치한 모든 레코드는 포인터를 따라 논리적으로 연결되어 있어 이 포인터를 따라 스캔하는 방식이다.
• 테이블 레코드 간에는 포인터로 연결되어 있지 않지만 테이블을 스캔할 때 물리적으로 저장된 순서대로 읽어 나가므로 이것 또한 Sequential 액세스 방식이다.
• 오라클은 내부적으로 Sequential 액세스를 향상시키기 위하여 Mulitiblock I/O, 인덱스 Prefetch 같은 기능을 사용한다.
• Sequential 액세스 효율은 선택도에 의해 결정된다.
  • 같은 결과 건수를 내면서 얼마나 적은 레코드를 읽느냐로 효율성을 판단한다.

    포인터란?

    • 어떤 곳의 메모리 주소
    • 어떤 변수나 함수 등의 주소를 가리키는 개념이다.
    • 주소를 저장하는 변수(메모리)를 의미(단, 그 변수가 주소를 가지고 있어야 한다.)

(2) Random 액세스

• 레코드간 논리적, 물리적인 순서를 따르지 않고, 한 건을 읽기 위해 한 블록씩 접근하는 방식이다.
• 인덱스는 Random 액세스이다. 평균적으로 인덱스 깊이에 따라 1~3 블록 정도 읽는다.
• Random 액세스 성능 향상을 위하여 오라클은 내부적으로 버퍼 Pinning, 테이블 Prefetch 같은 기능을 사용한다.

(3) 효율성

• 블록 단위 I/O를 하더라도 한번 액세스할 때 Sequential 방식으로 그 안에 저장된 모든 레코드를 읽는다면 비효율이 없지만 , 하나의 레코드를 읽으려고 한 블록씩 읽는다면 매우 비효율적이다.

(4) 선택도(Selectivity)

• 총 읽은 레코드에서 결과 집합으로 선택되는 비중이다.

(3) I/O 튜닝의 핵심 원리

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

[3] Sequential 액세스의 선택도 높이기

[예시1] 풀스캔

SQL>select count(*) from t;
==========
COUNT(*)
----------
49993
==========

SQL>select count(*) from t 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개 이다.

[예시2] 풀스캔

SQL>select count(*) from where owner like 'SYS%' 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개 똑같다.

[예시3] 인덱스 스캔

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

SQL>select /*+ index(t t_idx) */ count(*) from t
where owner like 'SYS%' 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 like 'SYS%' 조건과
  • object_name = 'ALL_OBJECTS' 조건에 의해 결정된다.

[예시4] 인덱스 스캔

SQL> select /*+ index(t t_idx) */ count(*) from t
where owner like 'SYS%'
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 액세스 방식으로 스캔할 때도 비효율이 있음
• 인덱스 스캔의 효율은 조건절에 사용된 컬럼과 연산자 형태, 인덱스 구성에 의해 영향을 받는다.

[예시5] 인덱스 스캔

SQL>create index t_idx on t (object_name, owner);
SQL>select /*+ index(t t_idx) */ count(*) from t where owner like 'SYS%' 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 액세스를 수행하였다.

[4] Random 액세스 발생량 줄이기

[예시1]

SQL> create index t_idx on t(owner);
SQL> select object_id from t where owner ='SYS' 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 때문이다.

[예시2]

SQL>create index t_idx on t(owner, object_name);
SQL>select object_id from t where owner ='SYS' 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 엑세스가 대폭 감소한다.