성능튜닝의 3대 요소

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

• sql파싱부하해소
• 데이터베이스 call최소화
• I/O효율화

[1] 블록 단위 I/O

• 모든 DBMS에서 I/O는 블록(SQL Server 등 다른 DBMS는 페이지라는 용어를 사용) 단위로 이루어진다. - 하나의 레코드를 읽더라도 레코드가 속한 블록 전체를 읽는다.

(1) SQL 성능을 좌우하는 가장 중요한 성능지표

• 액세스하는 블록 개수
  - 옵티마이저의 판단에 가장 큰 영향을 미치는 것도 액세스해야 할 블록 개수다.

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

1. ⭐️ 데이터 파일에서 DB 버퍼 캐시로 블록을 적재할 때 (데이터 파일 => 캐시)

2. ⭐️ 데이터 파일에서 블록을 직접 읽고 쓸 때 (데이터 파일 => 직접 끌고)

3. ⭐️ 버퍼 캐시에서 블록을 읽고 쓸 때 (버퍼 캐시 => 직접 끌고)

4. ⭐️ 버퍼 캐시에서 변경된 블록을 다시 데이터 파일에 쓸 때 (버퍼 캐시 => 데이터 파일)

[2] 메모리 I/O vs. 디스크I/O

(1) I/O 효율화 튜닝의 중요성

• 메모리는 물리적으로 한정된 자원이므로, 결국 디스크 I/O를 최소화하고 버퍼 캐시 효율을 높이는 것이 데이터베이스 I/O 튜닝의 목표이다.

1. 디스크를 경유한 데이터 입출력

• 디스크의 액세스 암(Arm)이 움직이면서 헤드를 통해 데이터를 읽고 쓰기 때문에 느리다.
• 서버 프로세스는 시스템에 I/O Call을 하고 잠시 대기 상태에 놓인다.
  - 디스크 I/O 경합이 심할수록 대기 시간도 길어진다.

2. 메모리를 통한 입출력

• 전기적 신호에 불과하기 때문에 디스크를 통한 I/O에 비해 비교할 수 없을 정도로 빠르다.

3. 모든 DBMS는 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾아보고, 없을 경우에만 디스크에서 읽어 버퍼 캐시에 적재한 후 읽기/쓰기 작업을 수행한다.

(2) 버퍼 캐시 히트율

버퍼 캐시 히트율 : 전체 읽은 블록 중에서 메모리 버퍼 캐시에서 찾은 비율

1. Direct Path Read 방식 이외의 모든 블록 읽기는 버퍼 캐시를 통해 이뤄진다.

• 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾아보고, 없을 때만 디스크로부터 버퍼 캐시에 적재한 후 읽어 들인다.

2.BCHR 공식

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

BCHR이 성능지표로서 갖는 한계점

• 같은 블록을 반복적으로 액세스하는 형태의 SQL은 논리적인 I/O 요청이 비효율적으로 많이 발생함에도 불구하고 BCHR은 매우 높게 나타난다.
• 논리적인 블록 요청 횟수를 줄이고, 물리적으로 디스크에서 읽어야 할 블록 수를 줄이는 것이 I/O 효율화 튜닝의 핵심 원리이다.
• 작은 테이블을 반복 액세스하면 모든 블록이 메모리에서 찾아져 BCHR은 높겠지만 일량이 크다.
  - 여기서 블록을 찾는 과정에서 래치(Latch) 경합과 버퍼 Lock 경합까지 발생한다면 메모리 I/O 비용이 디스크 I/O 비용보다 커질 수 있다.

3. 논리적으로 읽어야 할 블록 수의 절대량이 많다면 반드시 튜닝을 통해 논리적인 블록 읽기를 최소화해야 한다.

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

SQL을 작성할 때는 다양한 I/O 튜닝 기법을 사용해서 네트워크 전송량을 줄이려고 노력하는 것이 중요하다.

[3] Sequential I/O vs. Random I/O

I/O 튜닝의 핵심 원리

• Sequential 액세스에 의한 선택 비중을 높인다.
• Random 액세스 발생량을 줄인다.

(1) I/O튜닝의 핵심 원리 두 가지

1. sequential 액세스

• 논리적/물리적 순서를 따라 차례대로 읽어 나가는 방식이다.
• 인덱스 리프 블록에 위치한 모든 레코드는 포인터를 따라 논리적으로 연결되어 있으며 이 포인터를 따라 스캔한다.
• 블록 단위 I/O를 하더라도 한번 액세스할 때 Sequential 방식으로 그 안에 저장된 모든 레코드를 읽는다면 비효율은 없다.
• 튜닝방식 : Sequential 액세스에 의한 선택 비중을 높인다.

2. Random 액세스 발생량을 줄인다.

• 레코드간 논리적, 물리적인 순서를 따르지 않고, 한 건을 읽기 위해 한 블록씩 접근하는 방식이다.
• 하나의 레코드를 읽으려고 한 블록씩 Random 액세스 한다면 매우 비효율적이다.
• 튜닝 방식 : Random 액세스 발생량을 줄인다.

(2) Sequential 액세스에 의한 선택 비중 높이기

• Sequential 액세스 효율성을 높이려면, 읽은 총 건수 중에서 결과집합으로 선택되는 비중을 높여야 한다.
• 즉, 같은 결과를 얻기 위해 얼마나 적은 레코드를 읽느냐로 효율성을 판단한다.

[예시1] Table Full Scan : 효율성이 좋을 때

• T 테이블에는 49,906건의 레코드가 저장되어있다고 가정한다.
• 레코드가 24613개로 49%가 선택되었다면(읽은 블록 수는 691개) Table Full Scan에서 나쁘지 않은 수준이다.

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)

[예시2] able Full Scan : 효율성이 나쁠 때

• T 테이블에는 49,906건의 레코드가 저장되어있다고 가정한다.
• 레코드가 1개가 선택되었다면선택 비중이 0.002% 밖에 되지 않으므로 Table Full Scan 비효율이 크다.
• 테이블을 스캔하면서 읽은 레코드 중 대부분 필터링되고 일부만 선택된다면 인덱스를 이용하는 게 효과적이다.

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) 

[예시3] 인덱스 사용

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) 

• 인덱스를 사용했지만 1개의 레코드를 읽기 위해 76개의 블록을 읽어야 했다.
• 테이블뿐만 아니라 인덱스를 Sequential 액세스 방식으로 스캔할 때도 비효율이 나타날 수 있다.

[예시4] 인덱스의 순서 변경

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) 

• 인덱스 순서를 변경하여 루트 블록과 리프 블록 단 2개의 인덱스 블록만 읽었다.
• 가장 효율적인 방식으로 Sequential 액세스를 수행했다.

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

[예시1] 인덱스에 속하지 않는 칼럼 참조로 인한 과도한 테이블 액세스

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) 

• 인덱스에 속하지 않는 칼럼(object_id)을 참조하도록 쿼리를 변경함으로써 테이블 액세스가 발생했다.
• 인덱스로부터 조건을 만족하는 22,934건을 읽어 그 횟수만큼 테이블을 Random 액세스

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) 

• 인덱스를 변경하여 테이블 Random 액세스 발생량을 1(=4-3)번으로 줄였다.

[4] Single Block I/O vs. MultiBlock I/O

(1) Single Block I/O

• 한번의 I/O Call에 하나의 데이터 블록만 읽어 메모리에 적재하는 것이다.
• 인덱스를 통해 테이블을 액세스할 때는, 기본적으로 인덱스와 테이블 블록 모두 이 방식을 사용한다.
  • 인덱스 블록간 논리적 순서(이중 연결 리스트 구조로 연결된 순서)는 데이터 파일에 저장된
    물리적인 순서와 다르기 때문이다.

(2) MultiBlock I/O

• I/O Call이 필요한 시점에, 인접한 블록들을 같이 읽어 메모리에 적재하는 것이다.
• Table Full Scan처럼 물리적으로 저장된 순서에 따라 읽을 때는 인접한 블록들을 같이 읽는 것이 유리하다.
  • ‘인접한 블록’이란, 한 익스텐트(Extent)내에 속한 블록을 말한다.
    달리 말하면, MultiBlock I/O 방식으로 읽더라도 익스텐트 범위를 넘어서까지 읽지는 않는다.

대량의 데이터를 MultiBlock I/O 방식으로 읽을 때 Single Block I/O 보다 성능상 유리한 이유

👉 I/O Call 발생 횟수를 줄여주기 때문이다.

[5] I/O 효율화 원리

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

(1) 필요한 최소 블록만 읽도록 SQL을 작성한다.

• 동일한 데이터를 중복 액세스하지 않고, 필요명령을 사용자는 최소 일량을 요구하는 형태로 논리적인 집합을 정의한다.

[예시] 튜닝 전

• 논리적인 전체 집합은 과거 1년치인데, 전일, 주간, 전월 데이터를 각각 액세스한 후 조인한 것을 볼 수 있다. 전일 데이터는 총 4번을 액세스한 셈이다.

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.카드번호 

튜닝 후

• 과거 1년치 데이터를 한번만 읽고 전일, 주간, 전월 결과를 구할 수 있다.
• 즉, 논리적인 집합 재구성을 통해 액세스해야 할 데이터 양을 최소화한다.

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 

(2) 최적의 옵티마이징 팩터를 제공한다.

• 전략적인 인덱스 구성 + 인덱스 외에도 DBMS가 제공하는 다양한 기능을 적극적으로 활용한다.
  - 인덱스, 파티션, 클러스터, 윈도우 함수 등 +
• 옵티마이저 모드 설정하기
  - 전체 처리속도 최적화
  • 최초 응답속도 최적화
  • 그 외 옵티마이저 행동에 영향을 미치는 일부 파라미터를 변경하기
• 옵티마이저에게 정확한 정보를 통계 정보를 제공한다.

(3) 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도하기

힌트 사용 예시

• dept 테이블을 leading 테이블로 두고 emp 테이블과 nl조인을 시도하도록 유도한다.

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'