데이터 베이스 성능 튜닝 세번째 I/O 효율_작성중

모든 DBMS의 I/O는 블록단위로 이루어짐

아래 두 쿼리는 서버의 I/O측면에선 같은 일량

SELECT ENAME FROM EMP WHERE SAL >=2000

SELECT * FROM EMP WHERE SAL >=2000

*엑세스 해야할 블록 수를 줄이는게 튜닝의 핵심

딕셔너리 캐시는 로우 단위로 I/O 수행

I/O 튜닝의 핵심 2가지 원리

* SEQUENTIAL 엑세스의 선택도 높임

* RANDOM 엑세스의 발생량 줄임

SEQUENTIAL 엑세스의 선택도 높이기

LEAF가 정렬되어 있는 인덱스를 활용 (인덱스 순서 조정)

RANDOM 엑세스 발생량 줄이기

인덱스 컬럼 추가로 인한 RANDOM ACCESS 줄임

버퍼캐시히트율(Buffer Cache Hit Ratio)

물리적 디스크 읽기를 수행하지 않고 곧바로 메모리에서 블록을 찾은 비율

BCHR = ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) * 100

        = ( ( 논리적 블록읽기 - 물리적 블록읽기) / 논리적 블록읽기 ) * 100

        = ( 1 - (물리적 블록읽기) / (논리적 블록읽기) ) * 100

같은 블록을 반복 ACCESS하는 경우 BCHR이 매우 높게 나타나므로 BCHR만 볼게 아니라 논리적 블록 ACCESS를 줄여야 한다. (대량테이블의 NL 조인)

SINGLE BLOCK I/O : 한번의 I/O CALL 에 하나의 데이터 블록만 읽어 메모리에 적재

MULTI BLOCK I/O : 한번의 I/O CALL 에 다수의 데이터 블록만 읽어 메모리에 적재

INDEX FAST FULL SCAN, 인덱스만 스캔하는 경우 말고는 대부분의 인덱스 스캔시에는 SINGLE BLOCK I/O 로 읽음

PREFETCH : 한번에 여러개의 SINGLE BLOCK I/O를 수행해서 버퍼캐시에 적재

버퍼피닝 : 랜덤 액세스에 의한 논리적 블록 요청 횟수 감소

테이블 PREFETCH : 디스크 I/O 에 의한 대기 횟수 감소

-----------------------------------------------------------------------------------------

DIRECT PATH I/O

버퍼캐시를 경유하지 않고 데이터블록을 읽고 씀 

1. Temp 세그먼트 블록들을 읽고 쓸 때

2. 병렬쿼리로 Full Scan을 수행할 때

3. nocache 옵션을 지정한 LOB컬럼을 읽을때

4. direct 옵션을 지정하고 export를 수행할때

5. parallel DML 을 수행할때

6. Direct Path Insert를 수행할때

1. SORT시 PGA의 SORT AREA 초과시 TEMP TABLE SPACE에 읽고, 쓸때

2. 병렬 FULL SCAN일때 10G부터는 병렬쿼리와 관련된 세그먼트만 메모리와 디스크를 동기화 함

3. 병렬 DML 수행, Direct Path Insert 방식으로 데이터를 insert할때 

    (undo 발생량 최소화)

    - insert....select 문장에 /*+ append*/

    - 병렬모드로 insert

    - direct 옵션 지정하고 sql*loader로 데이터 로드

    - CTAS 문장 실행

redo로그까지 최소화하려면

alter table t nologging; 

** Direct Path Insert 방식일 경우 Exclusive 모드 테이블 Lock이 걸리므로 주의 (야간 사용)

다음과 같은 경우도 포함

alter session enable parallel dml;

delete /*+ parallel(b 4) */ from big_table b;  ->Exclusive 모드 TM Lock

-----------------------------------------------------------------------------------------

Result캐시

버퍼캐시에 위치하지 않고 Shared Pool에 위치, 인스턴스 재기동시 초기화

한번 수행한 쿼리 또는 pl/sql 함수의 결과값을 result 캐시에 저장 (11g버전이후)

DML이 거의 발생하지 않는 테이블을 참조하면서 반복수행 요청이 많은 쿼리에 효과적

select /*+ result_cache */ ~~

쿼리 수행시 result 캐시 메모리에서 먼저 찾아보고 없을경우에만 쿼리 실행해서 result 캐시에 저장

결과 집합을 캐싱하지 못하는 경우

1. Dictionary 오브젝트 참조할때

2. Temporary 테이블 참조할때

3. 시퀀스로부터 currval, nextval pseudo 컬럼 호출할때

4. 아래 sql 함수 사용시

   (CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, SYS_CONTEXT, SYS_GUID, SYSDATE, SYSTIMESTAMP, USERENV)

바인드 변수 사용시는 바인드변수별로 캐싱 (V$RESULT_CACHE_OBJECTS 뷰 확인)

캐싱에 참조되는 테이블 DML시 RESULT_CACHE는 무효화

특정 블록에서도 캐싱 가능

1.(SELECT * FROM TAB1, (SELECT /*+RESULT_CACHE */ FROM TAB2) ~~

2.WITH WV_TEST AS (SELECT /*+RESULT_CACHE */ ~~

3.SELECT ~~ UNION ALL SELECT /*+RESULT_CACHE */ ~~~

4. WHERE 절 서브쿼리 캐싱은 불가능

다음에 효과적

- 작은 결과 집합을 얻으려고 대용량 데이터를 읽어야 할 때

- 읽기 전용의 작은 테이블을 반복적으로 읽어야 할 때

- 읽기 전용 코드 테이블을 읽어 코드명칭을 반환하는 함수

다음에는 사용X

- 쿼리가 참조하는 테이블에 DML 이 자주 발생

- 함수 또는 바인드 변수를 가진 쿼리에서 입력되는 값의 종류가 많고, 다양할때

-----------------------------------------------------------------------------------------

I/O 효율화

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

  -> 함수 사용은 제일 바깥쪽 쿼리에

  -> 조인도 가능하면 제일 바깥쪽 쿼리에

  -> 스칼라 서브쿼리에 같은 테이블을 조회하면 FROM 절에서 한번만 조회하게

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

  -> 통계정보 수집, 윈도우 함수 사용

힌트 사용으로 최적의 엑세스 경로 유도

오라클 성능고도화