8. I/O 효율화 원리

조회 수 5384 추천 수 0 2010.07.28 21:16:31
휘휘 *.168.123.1


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


  • 하드웨어적인 방법

    • RAW 디바이스

    • 비동기 I/O사용, 

    • 스트라이핑 방식을 달리하여 사용

    • 고대역폭 인터커넥터 사용

  • 애플리케이션 튜닝방법

    • 필요한 최소 블록만 읽도록 쿼리작성

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

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


(1) 필요한 최소 블록만 읽도록 쿼리 작성

  • 쿼리 작성시 불필요한 자료를 모두 가져오도록 작성하지 않아야한다.


(2) 최적의 옵티마이징 팩터 제공


  • 전략적인 인텍스 구성
  • DBMS가 제공하는 다양한 기능 활용
    • 파티션
    • 클러스터
    • IOT
    • MV
    • FBI
    • 분석함수등
  • 옵티미이저모드 설정




SQL> create table t
 2  as
 3  select * from all_objects
 4  order by dbms_random.value;

Table created.

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

Index created.

SQL> begin
 2  dbms_stats.gather_table_stats
 3     (ownname        => USER
 4  ,tabname   => 'T'
 5  ,estimate_percent => 100
 6  ,block_sample      => true
 7  ,method_opt        => 'for all columns size auto'
 8  );
 9  end;
10  /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly exp
SQL> alter session set optimizer_mode='ALL_ROWS';

Session altered.


SQL>  select * from t
 2  where owner='SYS'
 3* order by created
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 961378228

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   766 | 65110 |    46   (5)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |   766 | 65110 |    46   (5)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T    |   766 | 65110 |    45   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("OWNER"='SYS')

SQL>

  • 정렬된 결과 집합 전체를 Fetch하여 최적화를 수행
  • 인텍스를 사용하지않고 table을 full scan후 정렬하는 방식으로 동작
  • 다량의 데이터를 인덱스를 경유해 random 액세스하는 부하를 최소화


SQL>
SQL> alter session set optimizer_mode ='FIRST_ROWS';

Session altered.

SQL> select * from t
 2  where owner='SYS'
 3  order by created;

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   766 | 65110 |   710   (1)| 00:00:09 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   766 | 65110 |   710   (1)| 00:00:09 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |   766 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OWNER"='SYS')
      filter("OWNER"='SYS')

SQL>

  • 전체 결과 집합에서 처음 일부만 Fetch
  • 쿼리 결과를 끝까지 Fetch한다면 Full Scan보다 더 느리게 작동할수도 있음




  • 통계정보
    • dbms_stats.gather_table_stats 
      • 테이블에대한 object통계수집
    • dbms_stats.gather_system_stats
      • 시스템통계수집
        • cpu속도
        • 평균적인 single block 읽기속도
        • 평균적인 Multiblock 읽기 숙도
        • 평균적인 multiblock i/o개수


(3) 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도
  • 애플리케이셔 특성에 따라서 힌트를 적극적으로 활용하여 실행경로를 고정시킬경우도 생길수 있으며
  • 힌트 사용시에느 최적의 실행계획으로 처리되는지 반드시 확인해야함