by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

IO 효율화 원리




I/O 효율화 원리

하드웨어적 방법을 통해 I/O의 성능을 높이는 것도 좋지만, 애플리케이션 측면에서 논리적인 I/O의 요청 횟수를 최소화하는 것이 I/O효율화 튜닝의 핵심이다.

  • 애플리케이션 측면에서의 I/O효율화 원리
    • 필요한 최소 블록만 읽도록 쿼리작성한다.
    • 최적의 옵티마이저 팩터를 제공한다
    • 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도한다.
(1) 필요한 최소 블록만 읽도록 쿼리작성
  • 쿼리 옵티마이저가 나름대로 쿼리를 최적화한다.
  • 하지만 SQL명령을 던지는 사용자 스스로 최소 일량을 요구하는 형태로 논리적 집합을 정의하고, 효율적인 쿼리를 작성하는 것이 무엇보다 중요하다.
    (예1) 게시판 쿼리
    SELECT *
    FROM (
    SELECT ROWNUM NO, 등록일자, 번호, 제목, 회원명, 게시판유형명, 질문유형명, 아이콘, 댓글개수
    FROM (
    SELECT A.등록일자, A.번호, A.제목, B.회원명, C.게시판유형명, D.질문유형명, FUNC_ICON(D.질문유형코드) 아이콘, (SELECT..) 댓글개수
    FROM 게시판 A, 회원 B, 게시판유형 C, 질문유형 D
    WHERE A.게시판유형 = :TYPE
    AND B.회원번호 = A.작성자번호
    AND C.게시판유형 = A.게시판유형
    AND D.질문유형 = A.질문유형
    ORDER BY A.등록일자 DESC, A.질문유형, A.번호
    )
    WHERE ROWNUM <= 30
    )
    WHERE NO BETWEEN 21 AND 30
    
    
    SELECT /*+ ORDERED USE_NL(B) USE_NL(C) USE_NL(D) */
    A.등록일자, A.번호, A.제목, B.회원명, C.게시판유형명, D.질문유형명, FUNC_ICON(D.질문유형코드) 아이콘, (SELECT..) 댓글개수
    FROM (
    SELECT A.*, ROWNUM NO ROWNUM NO
    FROM (
    SELECT 등록일자, 번호, 제목, 작성자번호, 게시판유형, 질문유형
    FROM 게시판 
    WHERE 게시판유형 = :TYPE
    AND 작성자번호 IS NOT NULL
    AND 게시판유형 IS NOT NULL
    AND 질문유형 IS NOT NULL
    ORDER BY 등록일자 DESC, 질문유형, 번호
    ) A
    WHERE ROWNUM <= 30
    )A 회원 B, 게시판유형 C, 질문유형 D
    WHERE A.NO BETWEEN 21 AND 30
    AND B.회원번호 = A.작성자번호
    AND C.게시판유형 = A.게시판유형
    AND D.질문유형 = A.질문유형
    
  1. 최종 결과 집합에 대해서만 함수를 호출하고 스칼라 서브쿼리를 수행하도록 변경
  2. 결과집합을 확정 짓고서, 그 이후에 조인

(예2) 거래현황 집계 쿼리(p484)

  • 테이블 한번만 읽고서도 원하는 결과를 도출할 수 있도록 변경
  • 아우터 조인 부분을 SUM CASE문으로 변경하여 구현
(2) 최적의 옵티마이저 팩터 제공

사용자 의도대로 블록 액세스를 최소화하면서, 효율적인 쿼리 프로세싱을 할 수 있도록 최적의 옵티마이저 팩터를 제공해 주어야 한다.

  • 전략적 인덱스 구성
  • DBMS가 제공하는 다양한 기능 활용 : 파티션, 클러스터, IOT, MV, FBI, 분석함수 등 DBMS가 제공하는 기능들을 적극 활용한다.
  • 옵티마이저 모드 설정 : 목적에 맞는 옵티마이저 모드 설정(all_rows, first_rows)
    1. 테이블 t 생성
    2. owner, created에 대해 t_idx 인덱스 생성
    3. t 테이블에 대한 통계정보 수집
    4. alter session set optimizer_mode = 'ALL_ROWS';
    
    SQL> select * from t
    where owner = 'SYS'
    order by created;
    
    ---------------------------------------------------------------------
    Id    Operation             Name    Rows   Bytes   Cost(%CPU)
    ---------------------------------------------------------------------
    0    SELECT STATEMENT               1921   174K    202(2)
    1     SORT ORDER BY                 1921   174K    202(2)
    2      TABLE ACCESS FULL    T       1921   174K    202(2)
    ---------------------------------------------------------------------
    => 정렬된 결과 집합 전체를 Fetch할 것이므로 거기에 따라 최적화를 수행하도록 '목적'을 밝힘
        옵티마이저는 owner, creted 순으로 정렬된 인덱스가 있더라도 그것을 사용하지 않고 테이블을 풀스캔한후에 정렬하는 방식을 택함
        테이블 랜덤 액세스의 부하를 최소화
    
    5. alter session set optimzer_mode = 'FIRST_ROWS';
    
    SQL> select * from t
    where owner = 'SYS'
    order by created;
    
    ---------------------------------------------------------------------
    Id    Operation                     Name    Rows   Bytes   Cost(%CPU)
    ---------------------------------------------------------------------
    0    SELECT STATEMENT                       1921   174K    1870(1)
    1     TABLE ACCESS BY INDEX ROWID   T       1921   174K    1870(1)
    2      INDEX RANGE SCAN             T_IDX   1921           8(0)
    ---------------------------------------------------------------------
    => 전체 결과 집합에서 처음 일부 레코드만 Fetch하다 멈출 것임을 옵티마이저에게 밝힘
        옵티마이저는 랜덤 액세스가 많지 않을 것으로 믿고 정렬 부하를 없애는 방식을 택함
        owner, created 순으로 정렬된 인덱스를 이용하면 정렬 작업을 따로 수행하지 않아도 됨
        이 상태로 쿼리 결과를 끝까지 Fetch한다면 풀스캔으로 처리할때보다 더 느려지고 시스템 리소스도 낭비하는 결과를 초래하므로 옵티마이저 모드 설정은 매우 중요
    
  • 통계정보의 중요성 : dbms_stats.gather_table_stats 프로시저를 이용해, 통계정보를 수집한다.
(3) 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도
  • 가급적 옵티마이저 판단에 맡기는 것이 바람직하나, 옵티마이저가 생각만큼 최적의 실행계획을 수립하지 못하는 경우, 힌트를 사용하여 제어해준다.
  • 특히 통계정보나 실행환경변화에 따라 실행계획이 동적으로 바뀌었을때 매우 심각한 결과를 초래하는 시스템의 경우 사용한다.
  • 옵티마이저 힌트를 쓰더라도 최적의 실행계획으로 처리되는지를 반드시 확인한다.

문서에 대하여

  • 최초작성자 : 이신재
  • 최초작성일 : 2010년 05월 03일
  • 이 문서는 오라클클럽 대용량 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법I'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.