• 디스크를 경우한 데이터 입출력은 디스크의 액세스 암(Arm)이 움직이면서 헤드를 통해 데이터를 읽고 쓰기 때문에 느림
• 메모리를 통한 입출력은 전기적 신호에 불과하기 때문에 디스크를 통한 I/O에 디해 비교할수 없을정도로 빠름
• 모든 DBMS는 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾고 ,없을경우에는 디스크에서 읽어 버퍼 캐시로 적재 후 읽기/쓰기 작업을 수행
  
  
  
• 메모리는 물리적으로 한정된 자원이므로 디스크 I/O를 최소화 하고 버퍼 캐시 효율을 높이는 것이 DB I/O 튜닝의 목표
  

• 버퍼 캐시 효율ㅇ르 측정하는 지표로써 전체 읽은 블록중 메모리 버퍼 캐시에서 찾은 비율
• \* BCHR = (버퍼캐싱서 곧바로 찾은 블록 수 / 총 읽은 블록 수) * 100\*
  
  BCHR은 주로 전체적인 관점에서 측정하지만, 개별 SQL에 대해서도 구해 볼 수 있으며, 이 비율이 낮은 것이
  
  SQL 성능을 떨어뜨리는 주원인임
  

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

• Disk :디스크를 경유한 블록 수
• 버퍼캐시에서 읽은 블록 수 : Query + Current
• 총 읽은 블록 수 = 822
• 버퍼 캐시에서 곧바로 찾은 블록 수 = 822 - 18 = 804
• BCHR = (822 - 18) /822 = 97.8%
• I/O 효율화 튜닝의 핵심 원리 : 논리적인 블록 요청 횟수를 줄이고, 물리적으로 디스크에 읽어야 할 블록 수를 줄이는 것
  

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

• RAC같은 클러스터링 환경에선 인스턴스 간에 캐시도니 블록을 공유하므로 메모리 I/O성능에도 네트워크 속도가 지대한 영향을 미치게 됨
• 같은양의 디스크 I/O가 발생하더라도 I/O 대기시간이 크게 차이 나는것은 디스크 경합때문일 수도 있지만,
  
  OS에서 지원하는 파일시스템 버퍼캐시와 SAN 캐시 때문일 수도 있다.(SAN캐시는 크다고 문제가 되지 않지만, 파일시스템 버퍼캐시는 최소화해여
  
  데이터베이스 자체 캐시영역에 좀 더 큰 공간을 할당한는 것이 더욱 효과적임)
  
  참고 : http://wiki.gurubee.net/pages/viewpage.action?pageId=26742882
• 네트워크 문제든, 파일시스템 문제든, I/O성능에 관한 가장 확실하고 그논적인 해결책은 논리적인 블록요청 횟수를 최소화 하는 것

• 읽은 총 건수 중에서 결과집합으로 선택되는 비중을 높여야 함(얼마나 적은 레코드를 읽느냐)
  
  {code:sql}-- 테스트용 테이블 생성SQL> create table t 2 as 3select*fromall_objects 4 orderbydbms_random.value; -- 테스트용 테이블 데이터 건수 : 49,906 SQL>selectcount()fromt; COUNT()

  ---

  ---

  49906

\\ {code:sql}selectcount(*)fromtwhereowner 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)

• 전체 레코드 49,906건
• 선택 레코드 24,613건 (49 %)
• 읽은 블록수 691 블록
• Table Full Scan 치고는 나쁘지 않음
  
  {code:sql}selectcount(*)fromtwhereowner 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)

- 전체 레코드 49,906건
- 선택 레코드 1건 (0.002 %)
- 읽은 블록수 691 블록
- Table Full Scan 비효율 발생
\\ {code:sql}create index t_idx on t(owner, object_name);select/*+ index(t t_idx) */ count(*)fromtwhereowner 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 액세스 방식으로 스캔할 때도 비효율 발생
• 조건절에 사용된 컬럼과 연산자 형태, 인덱스 구성에 의해 효율성이 결정됨
  
  {code:sql}create index t_idx on t(object_name,owner );select/*+ index(t t_idx) / count()fromtwhereowner 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 액세스 수행

• Random 액세스 발생량을 낮추는 방법
  
  {code:sql}drop index t_idx; create index t_idx on t(owner);selectobject_idfromtwhereowner = 'SYS' and object_name = 'ALL_OBJECTS' Rows Row Source Operation

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  – 1 TABLE ACCESSBYINDEX 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)

- 인덱스로부터 만족하는 22,934건을 읽어 그 횟수만큼 테이블을 Random 액세스수행하여 최종적으로 1건의 결과 추출
- 최종 선택된 것에 비해 너무 많은 Random 액세스 발생
\\ {code:sql}drop index t_idx; create index t_idx on t(owner, object_name);selectobject_idfromtwhereowner = 'SYS' and object_name = 'ALL_OBJECTS' Rows Row Source Operation ---- ------------------------------ 1 TABLE ACCESSBYINDEX 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)

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

Single Block I/O 방식

create table t asselect*fromall_objects; alter table t add constraint t_pk primary key(object_id);select/*+ index(t) */ count(*)fromtwhereobject_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 messagefromclient 2 0.05 0.05

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

MultiBlock I/O 방식

-- 디스크 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

• 64개 블록을 디스크에서 읽었는데 I/O Call이 9번 발생
• Oracle 10g부터는 Index Range Scan 또는 Index Full Scan일때도 Multiblock I/O 방식으로 읽는 경우가 있음(테이블엑세스없이 인덱스만으로 처리할때)

• 비효율적인 액세스를 없애고 필요한 최소 블록만 액세스
  
  {code:sql}selecta.카드번호 , 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')groupby카드번호 ) b , ( -- 전월거래실적select카드번호, sum(거래금액) 거래금액from일별카드거래내역where거래일자 between to_char(add_months(sysdate,-1),'yyyymm') || '01' and to_char(last_day(add_months(sysdate,-1)),'yyyymmdd')groupby카드번호 ) c , ( -- 연중거래실적select카드번호, sum(거래금액) 거래금액from일별카드거래내역where거래일자 between to_char(add_months(sysdate,-12),'yyyymmdd') and to_char(sysdate-1,'yyyymmdd')groupby카드번호 ) dwhereb.카드번호 () = a.카드번호 and c.카드번호 () = a.카드번호 and d.카드번호 (+) = a.카드번호

\\ {code:sql}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')groupby카드번호havingsum( case when 거래일자 = to_char(sysdate-1,'yyyymmdd') then 거래금액 end ) > 0

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

• 전략적인 익덱스 구성
  
  
  \-전략적인 엔덱스 구성은 가장 기본적인 옵티마이징 팩터
• DBMS가 제공하는 기능 활용
  
  
  \-인덱스 외에도 DBMS가 제공하는 다양한 기능을 적극적으로 활용
  
  
  \-인덱스,파티션,클러스터, 윈도우 함수 등을 적극 활용하여 옵티마이저가 최적의 선택을 할 수 있도록 한다.
• 옵티마이저 모드 설정
  
  
  \-옵티마이저모드(전체처리속도, 최초등담속도)와 그 외 옵티마이저 행동에 영향을 미치는 일부 파라미터를 변경해 주는것이 도움이 될수 있다
• 통계정보
  
  
  \-옵티마이저에게 정확한 정보를제공
  
  

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


예시)

[예제] Oracle 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' [예제] SQL Server select * from dept d with (index(dept_loc_idx)), emp e where e.deptno = d.deptno and d.loc = 'CHICAGO' option (force order, loop join)

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