[SQLP]튜닝포인트 3.I/O 효율화 원리

SQLP 공부할 때 에버노트에 정리했던 내용입니다.

블록 단위 I/O

• Sequential 액세스의 선택도를 높임 : 유효 인덱스 선택도 높임. 즉 인덱스 스캔 Block 수를 줄이는 것. Multi Block I/O, 인덱스 Prefetch
  • 레코드 간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 것.
  • 인덱스 선행 컬럼이 등치 조건이 아닐 때 비효율
  • BETWEEN 조건을 IN-LIST
  • INDEX SKIP SCAN을 이용한 비효율 해소(/*+index_ss()*/)
  • 범위 검색조건 남용할때 발생하는 비효율 : LIKE나 부등호보다는 BETWEEN 사용하면 낭비는 없음
  • 같은 컬럼에 두 개의 범위 검색 조건 사용하면 1개만 처리됨(A >= 100 AND A LIKE 'a%'로 하면 A컬럼은 1개의 범위로 인덱스 스캔하게 되므로 더 유리한 컬럼으로 인덱스 사용하고 싶을 때는 강제로 컬럼 변해서 미사용 하게 함(RTRIM(A) >= 100 AND A LIKE 'a%')
  • OR-EXPANSION(USE_CONCAT 힌트가 발생, NO_EXPAND는 사용 안시킨)는 ordered_predicates 힌트 사용해야 뒤쪽 조건부터 수행함
  • 선분 이력 스캔 효율 높이는 법
• 테이블 랜덤 액세스 발생량 감소 : 인덱스 스캔 결과를 줄이는 것 혹은 최대한 인덱스 내에서 처리하는 것. 인덱스 스캔이 비효율이면 Table Full Scan 선택. 버퍼 Pinning, 테이블 Prefetch
  • 레코드 간 논리적, 물리적인 순서를 따르지 않고 한건을 읽기 위해 한 블록씩 접근하는 방식
  • 인덱스 컬럼 추가 : 인덱스 필터 역할을 추가하여 인덱스 스캔 결과를 줄인다. 단, 인덱스 클러스터링 팩터 감소하는 단점
  • PK 인덱스에 컬럼 추가: 인덱스 클러스터링 팩터 감소하는 단점
  • 인덱스만 읽고 처리 : Select절 포함해서 모든 컬럼을 오직 인덱스. 테이블 블록을 액세스 하지 않음.
  • 버퍼 pinning 효과
  • 수동으로 클러스터링 팩터 상승 : 해당 인덱스 기준으로 테이블 재생성
  • IOT(organization index 선언해서 테이블 생성) : 클러스터링 팩터(CF) 손익분 기점 극복하는 방법
  • 클러스터 테이블 생성: 클러스터링 팩터(CF) 손인분기점 극복하는 방법

---

손익분기점 극복방법    

• IOT
• 클러스터 테이블
• 파티셔닝
• 결합인덱스 스캔 구조 이해 및 주의사항

---

I/O 효율화 튜닝의 중요성은 디스크 I/O에서 비해서 메모리 속도의 우월성. 그래서 모든 DBMS는 버퍼 캐시를 경유해 I/O를 수행한다. DB 버퍼 캐시를 경유한다는 것은 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾아보고 찾지 못할 때만 디스크에서 읽는 것을 말한다. 읽고자 하는 블록을 버퍼 캐시에서 찾지 못해서  I/O CALL을 통해서 데이터 파일로부터 버퍼 캐시에 적재하는 방식에는 2가지가 있다.

• Single Block I/O : 인덱스 및 인덱스를 통한 테이블 액세스.
  • db file sequential read
• Multiblock I/O : 테이블 FULL SCAN/Index fast fullscan(Index_ffs) Extent 범위를 넘어서지 못한다.  
  • db file scattered read
  • Index fast full scan 외에도 10g부터 테이블 엑세스 없이 인덱스만 읽으면 Index Range Scan, Index Full Scan일 때도 해당 i/o
• Prefetch는 디스크 블록을 읽을 때 곧이어 읽을 가능성이 높은 블록을 미리 읽어오는 것.
  • 서로 인접하지 않은 다른 익스텐트의 블록을 single block i/o로 배치 방식으로 미리 적재하는 것을 의미하며 Multiblock I/O와 다름

---

Direct Path I/O는 DB 버퍼 캐시를 거치지 않고 바로 데이터 블록을 읽고 쓰는 것

• Temp 세그먼트 블록들을 읽고 쓸 때 : 데이터 정렬 시 시에 PGA에 있는 Sort Area에서 정렬하는데 이때 초과하면 디스크의 TEMP 세그먼트에 쓰고 읽음
• 병렬 쿼리로 Full scan 수행 시
• nocache 옵션을 지정한 LOB 컬럼을 읽을 때
• direct 옵션을 지정하고 export 수행할 때
• parallel DML을 수행할 때
• Direct Path Insert를 수행할 때 : Insert/*+Append*/SELECT , CTAS 수행 -> Exclusive Lock 발생
  • 일반 insert는 freelist에서 블록을 할당받은 후에 해당 블록을 버퍼 캐시에서 찾고 없으면 디스크에서 찾은 후에 버퍼 캐시에 해당 블록을 적재한다. 그리고 블록의 마지막 위치에 insert.
  • Direct Path Write는 freelist를 참조하지 않고 테이블 세그먼트 또는 각 파티션 세그먼트의 HWM 바깥 영역에 데이터를 순차적으로 입력. 
    1. Freelist로부터 블록 할당을 거치지 않고, 데이터 파일에 직접 입력함.
    2. HWM 바깥 영역에 데이터 입력하므로 UNDO 발생량 최소화
    3. nologing 옵션을 통해서 REDO 로그까지 최소화 가능