I/O 효율화 원리

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

[1] 애플리케이션 측면에서의 I/O효율화 원리

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

⭐️ 최소 일량을 요구하는 형태로 논리적 집합을 정의하고, 효율적인 쿼리를 작성하는 것이 무엇보다 중요하다. ⭐️

[예시 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;

[예시 2 게시판 쿼리 튜닝 후]

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
        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 : 결과집합을 확정 짓고서, 그 이후에 조인한다.

[예시 3 거래현황 집계 쿼리(p484)]

• 튜닝 포인트 1 :테이블 한번만 읽고서도 원하는 결과를 도출할 수 있도록 변경한다.
• 튜닝 포인트 2 :아우터 조인 부분을 SUM CASE문으로 변경하여 구현

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

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

1. 전략적 인덱스를 구성할 수 있다.

2. DBMS가 제공하는 다양한 기능을 활용할 수 있다.

• 파티션, 클러스터, IOT, MV, FBI, 분석함수 등 DBMS가 제공하는 기능들을 적극 활용할 수 있다.

3. 목적에 맞는 옵티마이저 모드를 설정할 수 있다. (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한다면 풀스캔으로 처리할때보다 더 느려지고 시스템 리소스도 낭비하는 결과를 초래하므로 옵티마이저 모드 설정은 매우 중요하다!

4. 통계정보의 중요성

dbms_stats.gather_table_stats 프로시저를 이용해, 통계정보를 수집한다.

• 특히 통계정보나 실행환경변화에 따라 실행계획이 동적으로 바뀌었을때 매우 심각한 결과를 초래하는 시스템의 경우에 사용하도록 한다.

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

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

• 옵티마이저 힌트를 쓰더라도 최적의 실행계획으로 처리되는지를 반드시 확인한다.