SQLP - 데이터베이스 I/O 원리

@markdown

# 데이터베이스 I/O 원리

____

## 블록단위 I/O

- 데이터 파일에서 DB 버퍼 캐시로 블록을 적재할 때

- 데이터 파일에서 블록을 직접 읽고 쓸때

- 버퍼 캐시에 변경된 블록을 다시 데이터 파일에 쓸 때 

## 메모리 I/O vs 디스크 I/O

### I/O 효율과 튜닝의 중요성

- 디스크 데이터 입출력은 디스크의 액세스 암(Arm)이 움직이면서 헤드를 통해 데이터를 읽고 쓰기 때문에 느림

- 메모리를 통한 입출력은 전기적 신호로 데이터를 읽기 때문에 빠름

- 모든 DBMS는 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾고, 없을 경우에는 디스크에서 데이터를 읽어 버퍼 캐시로 적재 한 후 읽기 작업을 수행

- <b>디스크 I/O를 최소화하고 버퍼 캐시 효율을 높이는 것이 데이터베이스 I/O 튜닝의 목표</b>


### 버퍼 캐시 히트율(Buffer Cache Hit Ratio)

- 버퍼 캐시 효율을 측정하는 지표로써 전체 읽은 블록 중 메모리 버퍼 캐시에서 찾은 비율

- BCHR = (버퍼 캐시에서 바로 찾은 블록 수 / 총 읽은 블록 수) * 100

- BCHR이 낮은 것이 SQL 성능을 떨어뜨리는 주범

<pre><code class="c" style="font-size:14px">call   count  cpu  elapsed disk query current rows 

------ ----- ----- ------- ---- ----- ------ ---- 

Parse     15  0.00    0.08    0    0      0   0 

Execute   44  0.03    0.03    0    0      0   0 

Fetch     44  0.01    0.13   18  822      0  44 

------ ----- ----- ------- ---- ----- ------ ---- 

total    103  0.04    0.25   18  822      0  44

</code></pre>

- disk : 디스크를 경유한 블록 수

- 총 읽은 블록 수 = 822

- 버퍼 캐시에서 곧바로 찾은 블록 수 = 822 - 18 = 804

- CHR = (822 - 18) / 822 = 97.8%

- 논리적인 블록요청 횟수를 줄이고, 물리적으로 디스크에서 읽어야할 블록수를 줄이는 것이 I/O 튜닝의 핵심

### 네트워크, 파일시스템 캐시가 I/O 효율에 미치는 영향

- 대용량의 데이터를 읽고 쓰는데 다양한 네트워크 기술이 사용됨에 따라 네트워크 속도도 SQL 성능에 크게 영향을 미침

- RAC 같은 클러스터링 환경에선 인스턴스 간에 캐시된 블록을 공유하므로 메모리 I/O의 성능에도 네트워크 속도가 지대한 영향을 미침

- 같은 양의 디스크 I/O가 발생하더라도 I/O 대기시간이 크게 차이 나는것은 디스크 경합 때문일수도 있지만, OS에서 지원하는 파일시스템 버퍼 캐시와 SAN 캐시 때문일 수도 있음

</br>

## Sequential I/O vs Random I/O

____


- Sequential 액세스 : 논리적/물리적 순서에 따라 차례대로 읽어 나가는 방식(⑤번 방향 처럼 모든 레코드가 논리적 포인터로 연결되어 있어, 이 포인터를 따라 스캔하는 방식)

- Random 액세스 : 레코드간 논리적, 물리적인 순서를 따르지 않고, 한 건을 읽기 위해 한 블록씩 접근하는 방식(① ② ③ ④ ⑥)

- Sequential 액세스에 의한 선택 비중을 높이고, Random 액세스 발생량을 줄이는 것이 I/O 튜닝의 핵심

<br/>

### Sequential 액세스에 의한 선택 비중 높이기

- 읽는 총 건수 중에서 결과 집합으로 선택되는 비중을 높여야함 

- 같은 결과를 얻기 위해 얼마나 적은 레코드를 읽느냐로 효율성이 판단됨

<pre><code class="sql" style="font-size:15px">-- 테스트용 테이블 생성 

SQL> create table t as 

select * from all_objects 

order by dbms_random.value; 

-- 테스트용 테이블 데이터 건수 : 49,906 

SQL> select count(*) from t;

COUNT(*) 

-------- 

49906 

</code></pre>

<pre><code class="sql" style="font-size:15px">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)

</code></pre>

- 전체 레코드 49,906건

- 선택 레코드 24,613건(49%)

- 읽은 블록수 691 블록

<pre><code class="sql" style="font-size:15px">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 

---- ------------------------------ 

   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) 

</code></pre>

- 인덱스를 사용하고도 1개의 레코드를 읽기 위해 76개 블록 액세스

- 테이블 뿐 아니라, 인덱스를 sequential 액세스 방식으로 스캔할 때도 비효율 발생

- 조건절에 사용된 컬럼과 연산자 형태, 인덱스 구성에 의해 효율성이 결정됨

<pre><code class="sql" style="font-size:15px">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 

---- ------------------------------ 

   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) 

</code></pre>

- 인덱스 순서를 변경해 루트와 리프 단 2개의 인덱스 블록만 읽어 가장 효율적인 방식으로 Sequential 액세스 수행

<br/>

### Random 액세스 발생량 줄이기

- Random 액세스 발생량을 줄이는 방법

<pre><code class="sql" style="font-size:15px">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=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)

</code></pre>

- 인덱스로부터 만족하는 22,934건을 읽어 그 횟수만큼 테이블을 Random 액세스를 수행하여 최종적으로 1건의 결과 추출

- 출력된 결과 1건에 비해 너무 많은 Random 액세스 발생(22934)

<pre><code class="sql" style="font-size:15px">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' 

Rows Row Source Operation 

---- ------------------------------ 

   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)

</code></pre>

- 인덱스에 조건절 컬럼을 추가 해줌으로써 Random 액세스 감소

<br/>

## Single Block I/O vs MultiBlock I/O

____

- Single Block I/O : 한번의 I/O Call에 하나의 데이터 블록만 읽어서 메모리에 적재하는 방식

- 인덱스를 통해 테이블을 엑세스 할때, 기본적으로 인덱스와 테이블 모두 이 방식 사용

- Multi Block I/O : I/O Call 이 필요한 시점에, 인접한 블록들을 같이 읽어 메모리에 적재하는 방식

- Table Full Scan 처럼 물리적으로 저장된 순서에 따라 읽을때 인접한 블록(하나의 익스텐트에 속한 블록)들을 같이 읽는것이 유리함

#### Single Block I/O 방식

<pre><code class="sql" style="font-size:15px">create table t 

as select * from all_objects; 

alter table t add 

constraint t_pk primary key(object_id); 

select /*+ index(t) */ count(*) 

from t 

where object_id > 0 

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.26   0.25   64   65     0      1  

----- ---- ---- ------ ---- ----- ----- ------ 

total    4 0.26   0.25   64   65     0      1   

Rows  Row Source Operation 

----- ------------------------------ 

    1 SORT AGGREGATE (cr=65 r=64 w=0 time=256400 us) 

31192  INDEX RANGE SCAN T_PK (cr=65 r=64 w=0 time=134613 us) 

Elapsed times include waiting on following events:  

 Event waited on                Times  Max. Wait Total Waited 

------------------------------- Waited --------- ------------ 

SQL*Net message to client            2      0.00         0.00 

db file sequential read             64      0.00         0.00 

SQL*Net message from client          2      0.05         0.05 

</code></pre>

- 64번의 인덱스 블록을 디스크에서 읽으면서 64번의 I/O Call이 발생

#### MultiBlock I/O 방식

<pre><code class="sql" style="font-size:15px">-- 디스크 I/O가 발생하도록 버퍼 캐시 Flushing 

alter system flush buffer_cache; 

-- Multiblock I/O 방식으로 인덱스 스캔 

select /*+ index_ffs(t) */ count(*) 

from t 

where object_id > 0 

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.26   0.26    64   69       0   1 

----- ----- ---- ------- ---- ----- ------- ----

total     4 0.26   0.26    64   69       0   1  

Rows  Row Source Operation 

----- ------------------------------ 

    1 SORT AGGREGATE (cr=69 r=64 w=0 time=267453 us) 

31192  INDEX FAST FULL SCAN T_PK (cr=69 r=64 w=0 time=143781 us) 

Elapsed times include waiting on following events: 

 Event waited on                Times Max. Wait Total Waited 

------------------------------ Waited --------- ------------ 

SQL*Net message to client           2      0.00       0.00 

db file scattered read              9      0.00       0.00 

SQL*Net message from client         2      0.35       0.36 

</code></pre>

- 64개 블록을 디스크에서 읽었는데 I/O Call이 9번 발생

- index_ffs : 인덱스를 FAST FULL SCAN 하라는 힌트로 보통 인덱스 스캔 방식인 Single Block Scan 방식을 Multi Block Scan 방식으로 유도한다. 

</br>

## I/O 효율화 원리

____

##### 논리적인 I/O 요청 횟수를 최소화 하는 것이 I/O 효율화 튜닝의 핵심 원리

- I/O 때문에 성능이 낮게 측정될 때, 하드웨어적인 방법을 통해 I/O 성능을 향상 시킬수도 있지만, SQL 튜닝을 통해 I/O 발생 횟수를 줄이는 것이 근본적이고 확실한 해결 방법

- 애플리케이션 측면의 I/O 효율화 원리

- 1.필요한 최소 블록만 읽도록 SQL 작성

- 2.최적의 옵티마이징 팩터 제공

- 3.필요하다면, 옵티마이저 힌트를 사용하여 최적의 액세스 경로를 유도

### 필요한 최소 블록만 읽도록 SQL 작성

- 비효율적인 액세스를 없애고, 필요한 최소 블록만 액세스

<pre><code class="sql" style="font-size:15px">select a.카드번호 

     , a.거래금액 전일_거래금액 

     , b.거래금액 주간_거래금액 

     , c.거래금액 전월_거래금액 

     , d.거래금액 연중_거래금액 

from ( -- 전일거래실적 

       select 카드번호, 거래금액 

       from 일별카드거래내역 

       where 거래일자 = to_char(sysdate-1,'yyyymmdd') 

       ) a 

     , ( -- 전주거래실적 

        select 카드번호, sum(거래금액) 거래금액 

        from 일별카드거래내역 

        where 거래일자 between to_char(sysdate-7,'yyyymmdd') and to_char(sysdate-1,'yyyymmdd') 

        group by 카드번호 

       ) b 

     , ( -- 전월거래실적 

        select 카드번호, sum(거래금액) 거래금액 

        from 일별카드거래내역 

        where 거래일자 between to_char(add_months(sysdate,-1),'yyyymm') || '01' and to_char(last_day(add_months(sysdate,-1)),'yyyymmdd') 

        group by 카드번호 

       ) c 

     , ( -- 연중거래실적 

        select 카드번호, sum(거래금액) 거래금액 

        from 일별카드거래내역 

        where 거래일자 between to_char(add_months(sysdate,-12),'yyyymmdd') and to_char(sysdate-1,'yyyymmdd') 

        group by 카드번호 

       ) d 

where b.카드번호 (+) = a.카드번호 

and   c.카드번호 (+) = a.카드번호 

and   d.카드번호 (+) = a.카드번호 

</code></pre>

<pre><code class="sql" style="font-size:15px">select 카드번호 

    , sum( case when 거래일자 = to_char(sysdate-1,'yyyymmdd') 

                 then 거래금액 

             end ) 전일_거래금액 

    , sum( case when 거래일자 between to_char(sysdate-7,'yyyymmdd') and to_char(sysdate-1,'yyyymmdd') 

                 then 거래금액 

             end ) 주간_거래금액 

    , sum( case when 거래일자 between to_char(add_months(sysdate,-1),'yyyymm') || '01' and to_char(last_day(add_months(sysdate,-1)),'yyyymmdd') 

                 then 거래금액 

             end ) 전월_거래금액 

     , sum( 거래금액 )연중_거래금액 

from 일별카드거래내역 

where 거래일자 between to_char(add_months(sysdate,-12),'yyyymmdd') and to_char(sysdate-1,'yyyymmdd') 

group by 카드번호 

having sum( case when 거래일자 = to_char(sysdate-1,'yyyymmdd') then 거래금액 end ) > 0 

</code></pre>

- 같은 테이블을 4번 액세스하여 처리하던 방식을 1번만 읽어 처리할 수 있도록 튜닝

<br/>

### 최적의 옵티마이징 팩터 제공

- 전략적인 인덱스 구성

- DBMS 기능 활용

- 인덱스 외에 DBMS가 제공하는 다양한 기능을 적극적으로 활용

- 인덱스, 파티션, 클러스터, 윈도우 함수 등을 적극 활용하여 옵티마이저가 최적의 선택을 할 수 있도록 한다.

- 옵티마이저 모드 설정

- 통계정보를 활용해 옵티마이저에게 정확한 정보를 제공

### 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도

- 최적의 옵티마이징 팩터를 제공했다면, 가급적 옵티마이저에게 맡기는것이 바람직하지만 옵티마이저가 생각만큼 최적의 실행계획을 수립하지 못하는 경우 사용

- 옵티마이저 힌트를 사용할 때 반드시 의도한 실행계획으로 수행되는지 확인해야 함

<pre><code class="sql" style="font-size:15px">Oracle Example

select /*+ leading(d) use_nl(e) index(d dept_loc_idx) */ * 

from emp e, dept d 

where e.deptno = d.deptno 

and d.loc = 'CHICAGO' 

</code></pre>

> SQL 전문가 가이드 : 과목3. SQL 고급 활용 및 튜닝, 1장 SQL 아키텍처 기반 튜닝 원리 - 제4절 데이터베이스 I/O 원리(p.516 ~ p.527)

> 사진 출처 : 구루비넷