ch06_I/O 효율화의 원리_오라클 성능 고도화 원리와 해법_I

intro 성능튜닝 핵심

 

라이브러리 캐시 최적화

데이터베이스 Call 최소화

I/O 효율화 및 버퍼캐시 최적화

 

_*_ I/O 효율화 튜닝

인덱스 원리, 조인 원리, 옵티마이저 원리(읽어야 하는 블록의 개수) 이해 필수 

고급 SQL 활용 문제 해결 경험 

 

 

01 블록단위 I/O

: DBMS에서 I/O는 블록(페이지) 단위, 하나의 레코드에서 하나의 컬럼만 읽을 때 레코드가 속한 블록 전체를 읽게 됨

Sequntial 액세스	하나의 블록을 액세스해 그 안에 저장돼 있는 모든 레코드를 순차적 읽음 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어 나가는 방식
Random 액세스	여러개의 블록을 액세스, 메모리 버퍼에서 읽음 레코드간 논리적, 물리적 순서를 따르지 않고, 한건을 읽기위해 한 블록씨 접근

 

메모리 버퍼 캐시에서 블록을 읽고 쓸 때

데이터파일에 저장된 데이터 블록을 직접 읽거나 쓸 때

데이터파일에서 DB 버퍼 캐시로 블록을 적재

버퍼 캐시에서 변경된 블록을 다시 데이터 파일에 저장할 때(DBWR 프로세스에 의해 수행됨)

딕셔너리 캐시 로위단위 I/O

Sequential 액세스의 선택도를 높임	인덱스 사용 효과적임 create index t_idx on t(object_name, owner); select /*- index(t t_idx) */ count(*) from t where owner like 'SYS%' and object_name = 'ALL_OBJECTS';
Random 액세스 발생량 줄임	익덱스에 속하지 않은 컬럼을 참조하도록 쿼리를 변경함 클러스터링 팩터가 좋을 수록 버퍼 pinning에 의한 블록 I/O 감소 효과

 

02 Memory vs Disk I/O

 

I/O 효율화 튜닝의 중요성	DBMS는 버퍼캐시를 경유해 IO를 수행(찾지 못할 때만 디스크에서 읽음) 자주 액세스 하는 블록들이 캐시에 더 오래 남아 있도록 LRU 알고리즘을 사용
버퍼 캐시 히트율 (Buffer Cach Hit Ratio, BCHR)	버퍼캐시 효율을 측정하는 지표 BCHR  = (캐시에서 곧바로 찾은 블록 수            / 총 읽은 블록수) X 100 = ((논리적 블록읽기 - 물리적 블록읽기) / 논리적 블록읽기 ) X 100 = (1 - 물리적블록읽기 / 논리적 블록 읽기 ) X 100 = (1 - (DISK / (QUERY + CURRENT)) X 100 Direct Path Read  방식 제외, 모든 블록 읽기는 버퍼 캐시 BCHR 목표를 99%로 설정 필요
BCHR의 트레이드 오프	논리적 블록 요청 횟수 줄이고, 물리적 디시크 요청 블록수 줄이는 것이 IO 효율화 튜닝 핵심 BCHR 이 높게 나타나 더라도, 블록 찾는 과정에서 래치를 얻어야 함, 즉 비용이 수반됨 BCHR 이 100%여도 논리적으로 일거야할 블록 수의 절대량이 많다면,  튜닝 대상, 블록 읽기 최소화 필요 예) 대량 데이터를 기준으로 NL 조인 방식을 사용해 작은 테이블을 반복적 Lookup하는 경우
네트워크, 파일시스템 캐시가 I/O 효율에 미치는 영향	지능형 스토리지 : DB서버와 독립적으로 스토리 자체에 CPU와 RAM을 탑재하는 HW적인 솔루션을 도입 디스크 속도 문제, SAN 문제, RAC 인터커넥트 문제의 근본 해결 --> 논리적인 블록 요청 횟수를 최소화

 

03 Single Block vs Multiblock I/O

: 읽고자 하는 블록을 버퍼 캐시에서 찾지 못했을 때, I/O Call을 통해 데이터 파일로 부터 버퍼캐시에 적재하는 방식 

		서버 프로세스의 대기 이벤트
Single Block IO	한번의 I/O Call에 하나의 데이터 블록만 읽어 적재	db file sequential read
Multiblock IO	I/O Call이 필요한 시점에 인접한 여러 블록들을 읽어 적재	db file scattered read

 

 

04 Prefetch

- Row Prefetch : 한번의 fetch call로 Array 크기 만큼 여러 개 레코드를 가져오는 것

- 디스크 블록을 읽을 때 곧이어 읽을 가능성이 높은 블록을 미리 읽어오는 기능

- 디스크 IO 비용이 크기 때문에 성능향상을 위해 수행되는 기능

		대기이벤트 명
테이블 Prefetch	인접하지 않은 블록을 배치 방식으로 적재 Prefetch블록 모니터링 CKPT프로세스가 수행 익덱스 클러스터링 팩터가 나쁠 때(논리IO 및 디스크 IO 증가) 효과적임	df file parallel read
인덱스 Prefetch	Index Full Scan 발생시 인덱시 Prefetch 기능이 효과적일 수 있는 상황

 

05 Direct Path I/O

: 재사용 가능성이 없는 임시 세그먼트 블록들을 읽을 때 Direct Path I/O 기능이 유용함

Temp 세그먼트 블록들을 RW	Direct Path Read / Write Temp, Sort Area 부족하면 Temp TB 공간 사용
병렬 쿼리로 Full Scan 수행	Direct Path Read 방식 사용
nocache 옵션 지정한 LOB 컬럼을 읽을 때
direct 옵션을 지정하고 export 수행
parallel DML 수행할 때	Direct Path Write 사용
Direct Path Insert 수행할 때	Direct Path Write 사용

 

Direct Path Insert  방식 데이터 입력 방법

insert select 문장에  /*+ append */ 힌트사용 병렬 모드로 Insert Direct 옵션을 지정 SQL*Loader로 데이터를 로드 CTAS(Create table ... as selelct) 문장을 수행 alter table t NOLOGGIN;  REDO 로그 최소화

 

 

06 RAC 캐시 퓨전

: 부하 분산 목적, 고가용성, 확장성 목적 모델

	데이터베이스 서버 간 복제	Current 블록: 디스크로 부터 읽혀진 후 사용자의 갱신사항이 반영된 최종상태의 원본 블록 CR 블록은 Current블록의 복사본
	업무별 수직 분할
	데이터 구분에 따른 수평 분할

RAC 노드간 버퍼 캐시를 공유하며 블록 전송 메커니즘
전송없는 읽기
R/R 전송
R/W 전송
W/W 전송
W/R 전송

 

 

07 Result 캐시

: Result 캐시는 버퍼 캐시에 위치하지 않고 공유 풀에 위치, 시스템 IO 발생량 최소화 도움

: 한번 수행한 쿼리 또는  PL/SQL 함수의 결과값을 Result 캐시에 저장

DML이 거의 발생하지 않는 테이블 참조	반복수행 요청이 많은 쿼리에 사용시 IO 발생량 감소
SQL Query Result 캐시	PL/SQL 함수 Result 캐시
Result_cache_mode	Manual(힌트로 기능 설정), Force /*+  Result_cache */
Result_cache_max_size	N/A ( SGA 에서 사용항 총 용량, byte 단위)
Result_cache_max_result	5 (% , 캐시영역 점유 크기, 0.25% 사용 11g, 75% 넘지 못함)
Result_cache_remote_expiration	0 (remote 객체의 결과 보관 기간)
Result_cache 등록되 쿼리 목록과 사용현황 확인	v$result_cache_objects

 

--> Result_cache 높은 효과 기대 환경

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

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

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

 

---> Result_cache 캐시 기능 사용 자제, 불필요

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

함수 또는 바인드 변수를 가진 쿼리에서 입력되는 값의 종류가 많고, 그 값들이 골고루 입력 될때

 

 

08 I/O 효율화 원리

필요한 최소 블록만 읽도록 쿼리를 작성
최적의 옵티마이징 팩터를 제공	전략적 인덱스 구성 DBMS가 제공하는 기능 활용 (파티션, 클러스터, IOT, MV, FBI, 분석함수 등) 옵티마이져 모드 설정 - ALL_ROW : 정렬된 결과 집합 전체를 Fetch 할 것 - First_ROW: 전체 결과 집합에서 처음  일부 레코드만 Fetch 통계정보의 중요성
필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도, 실행계획 제어	/*+ leading(d), use_nl(e)  index(d dept_loc_idx) */ 옵티마이저 힌트를 사용하더라도 실행계획으로 처리되는지  확인 필요