1. 06. I/O 효율화 원리
   1. 01. 블록 단위 I/O
   2. (1) Sequential vs. Random 액세스
   3. (2) Sequential 액세스 선택도 높이기
   4. (3) Random 액세스 발생량 줄이기
   5. 참조 문서

06. I/O 효율화 원리

• 라이브러리 캐시 최적화
• 데이터베이스 Call 최소화
• I/O 효율화 및 버퍼캐시 최적화

• 위의 세 가지 다 중요하지만 그 중 가장 중요한 것을 꼽으라면 세번째라고 말할 수 있다.
• 앞쪽 두가지 튜닝 요소는, 핵심 원리만 이해하고 몇 가지 튜닝 기법만 잘 숙지하면 누구나 쉽게 적용할 수 있는 것들이다.
• 그러나 I/O 효율화를 달성하기는 쉽지 않은데다 장기간 훈련이 필요하다.
• I/O 효율화 튜닝을 잘하려면 인덱스 원리, 조인 원리, 옵티마이저 원리에 대한 이해가 필수적이다.
• 그리고 이를 바탕으로 실전에서 고급 SQL 활용을 통해 문제 해결 경험을 많이 쌓아야만 한다.

01. 블록 단위 I/O

• 오라클을 포함한 모든 DBMS에서 I/O는 블록(다른 DBMS에서는 주로 페이지(page)라는 용어를 사용) 단위로 이루어진다.
• 블록 단위로 I/O를 한다는 것은, 하나의 레코드에서 하나의 컬럼만을 읽으려 할 때도 레코드가 속한 블록 전체를 읽게 됨을 뜻한다.

Sequential 액세스

• 하나의 블록을 액세스해서 그 안에 저장돼 있는 모든 레코드를 순차적으로 읽어들이는 것.
• 무거운 디스크 I/O를 수반하더라도 비효율은 없다.

Random 액세스

• 레코드 하나를 읽으려고 블록을 통째로 액세스 하는 것.
• 메모리 버퍼에서 읽더라도 비효율이 존재함.

• 블록 단위 I/O 원리 때문에 아래 세 쿼리를 처리할 때 서버에서 발생하는 I/O 측면에서의 일량은 같다.
• 이것은 실행계획이 같을 때이고, 실행 계획이 같더라도 네트워크를 통해 클라이언트에 전송되는 일량에는 차이가 남.

SELECT 별 사이즈 차이

-- 쿼리 1)
SELECT ENAME FROM EMP WHERE SAL >= 2000;

Optimizer mode: ALL_ROWS

Rows     Row Source Operation
-------  ---------------------------------------------------
      6  TABLE ACCESS FULL EMP (cr=4 pr=0 pw=0 time=0 us cost=3 size=120 card=6)

-- 쿼리 2)
SELECT * FROM EMP WHERE SAL >= 2000;

Rows     Row Source Operation
-------  ---------------------------------------------------
      6  TABLE ACCESS FULL EMP (cr=4 pr=0 pw=0 time=0 us cost=3 size=522 card=6)

-- 쿼리 3)
SELECT EMPNO, ENAME FROM EMP WHERE SAL >= 2000;

Rows     Row Source Operation
-------  ---------------------------------------------------
      6  TABLE ACCESS FULL EMP (cr=4 pr=0 pw=0 time=0 us cost=3 size=198 card=6)

• 컬럼 단위 I/O를 지원하는 DMBS도 있어 DW 분야에서 각광을 받고 있는데, 아래처럼 일부 컬럼만 읽어 집계할 때 I/O 측면에서 성능을 극대화한다.

select deptno, avg(sal) from emp group by deptno ;

• SQL 성능을 좌우하는 가장 중요한 성능지표는 액세스하는 블록 개수이며, 옵티마이저의 판단에 가장 큰 영향을 미치는 것도 액세스해야 할 블록 개수다.
• 예를 들어, 옵티마이저가 인덱스를 이용해 테이블을 액세스할지 아니면 Full Table Scan 할지를 결정하는 데 있어 가장 중요한 판단 기준은 읽어야 할 레코드 수가 아니라 읽어야 하는 블록 개수다.

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

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

• 참고로, 오라클 딕셔너리 정보를 저장하는 딕셔너리 캐시는 로우 단위로 I/O를 수행한다.
• 딕셔너리 캐시를 '로우 캐시'라고도 부르는 이유가 여기에 있다.
• 오라클에서 허용하는 블록 크기는 2k, 4k, 8k, 16k, 32k이다. 데이터베이스를 생성할 때 db_block_size를 지정하며, 다른 크기의 블록을 동시에 사용하려면 각각 별도의 테이블스페이스와 버퍼 Pool을 구성해 주어야 한다.
• db_block_size는 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 액세스 선택도 높이기

--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
--------------------------------------------------------------------------------

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

Table created.

SUNSHINY@ORACLE11> select count(*) from t;

  COUNT(*)
----------
     70456

-- T 테이블에는 70,456건의 레코드가 저장돼 있음.

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

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.02       0.02          0          5          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch       10      0.10       0.10          0       5045          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       20      0.12       0.12          0       5050          0           5

Misses in library cache during parse: 3
Optimizer mode: ALL_ROWS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1009 pr=0 pw=0 time=0 us)
  34143   TABLE ACCESS FULL T (cr=1009 pr=0 pw=0 time=19436 us cost=282 size=566151 card=33303)

• 위 쿼리는 34,143개 레코드를 선택하려고 70,456개 레코드를 스캔했으므로 선택도는 48%다.
• (선택도 : 34,143 / 70,456 X 100 = 48.460%)
• Full Scan 선택도가 이 정도면 나쁘지 않아 보인다. 읽은 블록 수는 1009개였다.

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

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0       1009          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0       1011          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1009 pr=0 pw=0 time=0 us)
      1   TABLE ACCESS FULL T (cr=1009 pr=0 pw=0 time=0 us cost=282 size=1156 card=34)

• 위 쿼리는 1개 레코드를 선택하려고 70,456개 레코드를 스캔했으므로 선택도는 0.001%다.
• 선택도가 매우 낮으므로 테이블 Full Scan 비효율이 크다.
• 여기서도 읽은 블록 수는 똑같이 1009개다.
• 이처럼 테이블을 스캔하면서 읽은 레코드 중 대부분 필터링되고 일부만 선택된다면 아래처럼 인덱스를 이용하는게 효과적이다.

SUNSHINY@ORACLE11> create index t_idx on t (owner, object_name);

Index created.

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

  COUNT(*)
----------
         1

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

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00        102        100          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00        102        102          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=100 pr=102 pw=0 time=0 us)
      1   INDEX RANGE SCAN T_IDX (cr=100 pr=102 pw=0 time=0 us cost=212 size=1156 card=34)(object id 84124)

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

-- 1개 레코드를 얻으려고 실제 스캔한 레코드 수.
SUNSHINY@ORACLE11> 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(*)
----------
     18855

• 1 / 18,855 X 100 = 0.005%의 선택도다.
• 테이블뿐만 아니라 인덱스를 Sequential 액세스 방식으로 스캔할 때도 비효율이 있는 것을 알 수 있다.
• 인덱스는 테이블과 달리 정렬된 순서를 유지하므로 일정 범위를 읽다가 멈출 수 있다는 점만 다르다.
• 인덱스 스캔의 효율은 조건절에 사용된 컬럼과 연산자 형태, 인덱스 구성에 의해 영향을 받는다.

-- 인덱스 컬럼 순서를 변경하고 같은 쿼리를 수행.
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'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0          6          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
      1   INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=0 us cost=3 size=34 card=1)(object id 84125)

• 세 개의 CR 블록 읽기가 발생했다.
• 인덱스 루트 블록과 하나의 리프 블록만 읽었기 때문이다.
• 한 건을 얻으려고 스캔한 건수도 한 건(정확히 얘기하면, one-plus 스캔까지 두 건)일 것이다.
• 선택도가 100%이므로 가장 효율적인 방식으로 Sequential 액세스를 수행했다.

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

• Random 액세스 발생량을 낮추는 방법.
• 인덱스에 속하지 않는 컬럼(object_id)을 참조하도록 쿼리를 변경함으로써 테이블 액세스가 발생하도록 할 것이다.

drop index t_idx;

create index t_idx on t(owner);

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=1072 pr=0 pw=0 time=0 us cost=39 size=47 card=1)
  30677   INDEX RANGE SCAN T_IDX (cr=67 pr=0 pw=0 time=11120 us cost=6 size=0 card=337)(object id 84126)

• 왼쪽에 보이는 Rows는 각 수행 단계에서 출력(Flow-Out)된 로우 수를 의미한다.
• 인덱스로부터 30,677건을 출력했으므로 테이블을 그 횟수만큼 방문하게 된다.
• 그 과정에서 1005(=1072-67)개 블록을 Random 액세스했다.
• 내부적으로 블록을 30,677번 방문했지만 Random 액세스 횟수가 1005번에 머무는 것은 1장에서 설명한 버퍼 Pinning 효과 때문이다.
• 클러스터링 팩터가 좋을수록 버퍼 Pinning에 의한 블록 I/O 감소 효과는 더 커진다.

• 최종 한 건을 선택하려고 너무 많은 Random 액세스가 발생했다.
• object_name을 필터링하려고 테이블을 많이 방문한 것이므로 인덱스 액세스 단계에서 필터링할 수 있도록 object_name을 추가해보자.

drop index t_idx;

create index t_idx on t(owner, object_name);

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

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          5          0           1

Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 85  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=5 pr=0 pw=0 time=0 us cost=5 size=47 card=1)
      1   INDEX RANGE SCAN T_IDX (cr=4 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 84128)

• 인덱스로부터 1건을 출력했으므로 테이블을 1번 방문한다.
• 실제 발생한 테이블 Random 액세스도 1(=5-4)번이다.
• 같은 쿼리를 수행했는데 인덱스 구성이 바뀌자 테이블 Random 액세스가 대폭 감소한 것이다.

참조 문서

• 이 자료는 (오라클 성능 고도화 원리와해법 I)을 참고 하여 작성했습니다.