오라클 성능 고도화 원리와 해법 I (2016년-1)
I/O 효율화 원리
0
0
4,083
by 구루비 블록 I/O Block I/O Memory I/O Disk I/O Direct Path 캐시퓨전 Result 캐시 I/O 효율화 [2016.04.29]
- 성능 튜닝의 3대 요소
- 1. Block 단위 I/O
- 2. 메모리/디스크 I/O
- 3. Single block I/O / Multi block I/O
- 4 Table/Index Prefetch
- Direct parh I/O
- RAC 캐시퓨전
- Result cache
- I/O 효율화 원리 요약
성능 튜닝의 3대 요소
- Library cache 최적화
- DB Call 최소화
- I/O 효율화 및 버퍼 최적화
1. Block 단위 I/O
- 대부분의 DB 에서 I/O 단위는 블록(혹은 Page)다.
\--> 논리적 Row 단위가 아닌 I/O 를 위한 물리적 단위가 존재 - Seqential read: 하나의 블록을 엑세스 해 모든 내용을 읽는것.
- Random access: 하나의 레코드를 읽으려고 블록 통채로 읽는 것.
- Random access 보다 Sequential accessl 가 효율이 높다.
- 특정 컬럼만 조회하나 전체를 조회하나 일 량(Cost)는 같다.
\-->일부 컬럼 레벨 I/O 를 지원하는 DW 계열 DB 도 있다.
- SQL 성능이나 Optimizer 의 판단을 좌우하는 지표는 I/O 에 소요 된 Block 이다.
- Block I/O 는 아래와 같은 상황에서 발생한다.
메모리 버퍼에서 블록을 읽고 쓸 때
파일에 저장 된 블록을 읽고 쓸 때
DB 에서 버퍼캐시로 적재 할 때
버퍼에서 다시 DB 로 저장할 때
- Block 의 단위는 2kb 부터 4kb, 8kb ... 64kb 까지 가능하다.
다른 크기의 블록을 사용하려면 Tablespace, Buffer 를 개별 지정 해 주어야 한다.
- Oracle Dicaionary cache 는 Block 이 아니라 Row 단위로 I/O 수행한다.
Dictionary cache 를 Row cache 라고도 한다.
Sequentian read vs. Random access
Sequential read
- 논리적/물리적 순서를 따라 레코드 스캔하는 방식
인덱스 리프블록의 레코드는 포인터를 따라 연결되어 있다.
이를 따라 스캔하는것은 시퀀셜 리드에 속한다.
읽은 레코드 중 실제 결과로 선택되는 비중이 클 수록 효과적이다.
Random access
- 논리/물리 순서와 상관 없이 한 건을 읽기 위해 한 블록 씩 접근한다.
보통 인덱스-테이블 간 엑세스에서 많이 발생한다
Inner table NL 조인을 위한 인덱스 엑세스일 경우 루트-리프 블록 간 엑세스도 성능에 많은 영향을 준다
Random access 시 발생하는 성능 향상을 위해 버퍼 피닝이나 테이블 프리패치 등의 기능이 구현된다.
성능 향상을 위해선 random access 를 줄이고 sequential read 를 높여야 한다.
Sequential read 에서 결과집합 선택도 높이기
\-테이블 풀 스캔 후 대부분이 필터링되고 일부만 선택된다면 인덱스를 이용하는 것이 효과적.
\-참조 컬럼이 모두 인덱스 안에 있으면 인덱스 range scan 만으로 결과를 얻을 수 있다.
\-인덱스의 컬럼 순서에 따라서도 range scan 의 선택도가 달라짐
동일한 결과를 얻기 위해 더 많은 leaf block 을 읽어야 함
Random access 줄이기
\-인덱스가 속하지 않는 컬럼을 Select 참조 시 인덱스 \-> 테이블 간 random access 발생
- 인덱스 출력 건수에 비해 테이블 블록 엑세스 횟수가 적은것은 버퍼 피닝 효과 때문
Table access 단계의 1016 - Index access 단계의 81 = 935 block
37094 번 access 했으나 935 block 만 읽음
클러스터링 펙터가 좋을 수록 버퍼 피닝에 의한 I/O 감소 효과는 커짐
select /*+ index(t ind1) */ count(*) from test1 t ^M
where owner like 'SYS%' and object_name='ALL_OBJECTS'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 2 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 8 0.06 0.14 80 4064 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.06 0.14 80 4066 0 4
Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 4
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=1016 pr=20 pw=0 time=36844 us)
1 1 1 TABLE ACCESS BY INDEX ROWID TEST1 (cr=1016 pr=20 pw=0 time=36834 us cost=237 size=5478 card=66)
37094 37094 37094 INDEX RANGE SCAN IND1 (cr=81 pr=20 pw=0 time=19669 us cost=23 size=0 card=47568)(object id 526155)
- 읽고 필터링 되는 데이터가 많을 경우 필터조건을 인덱스 구성 컬럼 끝에 추가하여 엑세스 조건으로 변환.
2. 메모리/디스크 I/O
- 디스크가 메모리보다 느리므로 DB 는 버퍼캐시를 통해 I/O 수행한다.
버퍼캐시를 먼저 찾고 없으면 디스크를 검색
버퍼 효율을 높여서 대부분의 처리를 메모리에서 할 수 있도록 하는것이 성능을 좌우 함
버퍼 캐시 히트율
- 전체 읽은 블록 중 얼마만큼을 버퍼에서 찾았는지를 나타내는 것
캐시에서 직접 찾은 블록 / 전체 읽은 블록 * 100 - Direct path read 를 제외하고 모든 읽기는 버퍼를 통해 이루어 진다.
디스크에서 읽는다고 해도 버퍼에 적재 후 읽는다.
논리적 읽기 에서 물리적 읽기를 해야 실제 캐시에서 직접 읽은 블록 수가 나온다.
아래의 경우 4066 - 80 = 3986 이 실제 Buffer cache 에서 읽은 블록이다.
cpu time 과 elapsed time 의 차이는 대부분 I/O 에 의한 소요 시간이다.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 2 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 8 0.06 0.14 80 4064 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.06 0.14 80 4066 0 4
버퍼 캐시 히트율의 한계
- 같은 블록을 반복적 엑세스 하는 상황이면 논리적 I/O 가 비 효율적으로 많아도 BCHR 은 높다.
- 이 경우 버퍼를 찾는 과정에서 래치를 얻어야 하므로 많은 비용이 소모된다.
Network / Filesystem cache
- DB 밖의 영역(Storage, network) 등으로 인해서도 성능의 차이가 많이 난다.
ex> 동일한 Block 을 읽어도 스토리지 캐시 등으로 인해 성능차이가 날 수도 있다. - 각 벤더사들은 이를 효율화 하기 위해 노력을 기울이는 중이며, 성능에 미치는 요소들이 점점 다각화 되는 추세이다.
- 그럼에도 불구하고 가장 확실한 해결책은 블록 요청 횟수를 최소화 하는 것.
3. Single block I/O / Multi block I/O
블록을 Disk 를 통해 버퍼에 적재하는 방법은 크게 두 가지
Single block I/O
- 인덱스를 통한 테이블 엑세스 시 인덱스/테이블 모두 이 방법으로 처리
Multi block I/O - IO Call 시 인접한 블록을 같이 읽어 메모리에 적재하는 것
- Full scan 처럼 물리적 순서에 따라 읽을 때는 인접한 블록을 같이 읽는것이 효율적
OS 레벨의 I/O 단위가 DB Block size 와는 다르게 별도로 있기 때문에(보통 1M 라고 함) - 물리적으로 연속된 단위는 Extent 이므로 이 범위를 넘어 Multi block read 를 하지는 못한다.
- 멀티블록 리드의 단위는 db_multi_block_read_cont 파라메터로 정해진다
OS 에서 설정하는 I/O 단위를 넘을수는 없다
인덱스 스캔은 왜 single block I/O 를 하는가
- 인덱스는 논리적 순서로 스캔하지만, 블록의 물리적 순서는 논리적 순서와 다르기 때문
- Index range scan / Index full scan 모두 single block 읽기를 수행 함
- multi block read 는 다음 상황에서 적용 된다.
index fast full scan
table full scan
테이블 엑세스 없는 index range scan/index full scan - 서버 프로세스는 읽는 시점마다 I/O 요청 후 대기한다.
db file sequential read - Single block I/O 요청 시 발생
db file scattered read - Multi block I/O 요청 시 발생
- 대량 데이터를 읽을 때 multi block 방식이 유리 한 이유는 I/O call 을 줄이기 때문
- Single block I/O 방식으로 읽은 블록은 LRU list 에서 MRU 쪽에 위치
- Multi block I/O 방식으로 읽은 블록은 대략 중간 쯤 정도에 위치
4 Table/Index Prefetch
정의
- 디스크를 읽을 때 다음에 읽을 가능성이 있는 블록을 같이 읽어오는 기능
Multi block I/O 도 일종의 프리패치 기능이라 할 수 있다 - 테이블/인덱스 프리패치는 한 번에 여러개 Single block I/O 를 동시에 수행하는 것
- 물리적으로 인접하지 않은 블록을 배치 방식으로 미리 적재 하는 것
- 블록을 읽는 도중 물리적 I/O 가 필요하면 call 을 발생시키고 잠시 대기하는데,
다음 블록을 미리 적재하면 대기상태에 빠지는 횟수를 줄일 수 있음 - 시스템 전반의 디스크 경합을 줄이기보다, 시스템 Call 을 줄이고 개별 쿼리 성능 향상 목적
- 오라클이 내부적으로 판단해서 수행하지만, 프리패치 블록이 실제 엑세스로 이어지지 않는 비율이 높으면 기능 정지시킴
CKPT 프로세스가 모니터링 함
아래 쿼리로 조회 가능하다
SQL> select name, value from v$sysstat
where name in ('physical reads cache prefetch','prefetched blocks aged out before use');
NAME VALUE
---------------------------------------------------------------- -----------
physical reads cache prefetch 214298948
prefetched blocks aged out before use 31027630
2 rows selected.
- 이벤트는 db file parallel read 이다(10046 으로 프리패치 확인 가능하다)
인덱스 Prefetch
- 브렌치 블록을 읽을 때 앞으로 읽을 주소를 미리 얻을 수 있으므로 미리 캐싱 가능
- 2번 브렌치 블록을 읽고 6번 리프블록을 읽는 시점에 6,7번까지 같이 적재 해 놓는 것
- 가장 효과적일 수 있는 상황은 인덱스 풀 스캔 시
부분범위 처리로 스캔 후 멈추지만 않으면 모든 리프블록을 다 읽기 때문
프리패치 방법으로 스캔 시 리프블록 위의 브랜치블록을 읽어야 하므로 I/O 량은 약간 더 증가 - 연관 파라메터는 아래 두 개이다
\_index_prefetch_factor
기본값은 100
작게 설정할수록 인덱스 프리패치 더 선호
\_db_file_noncontig_mblock_read_count
한 번에 최대 몇 개 블록을 프리패치 할지 결정
1 지정 시 기능 정지
SQL> select ksppinm name,
2 ksppstvl value,
3 decode(bitand(ksppiflg/256,1),1,'true','false') ses_modifiable,
4 decode(bitand(ksppiflg/65536,3),1,'immediate',2,'deferred',3,'immediate','false') sys_modifiable,
5 ksppdesc description
6 from sys.x$ksppi i, sys.x$ksppcv v
7 where i.indx = v.indx
8* and i.ksppinm in ('_index_prefetch_factor', '_db_file_noncontig_mblock_read_count')
SQL> /
NAME VALUE SES_M SYS_MODIF DESCRIPTION
-------------------------------------------------- ---------- ----- --------- --------------------------------------------------------------------------------
_db_file_noncontig_mblock_read_count 11 false false number of noncontiguous db blocks to be prefetched
_index_prefetch_factor 100 true immediate index prefetching factor
SQL>
테이블 프리패치
- 테이블 룩겁 프리패치, 혹은 데이터블록 프리패지라고도 함
- 인덱스 경유해 레코드를 엑세스 할 때 리프블록에 연결된 다른 블록까지 미리 캐싱하는 기능
- Disk I/O 대기 횟수 감소시켜 랜덤엑세스 성능 향상 가능하다
- 인덱스 클러스터링 펙터가 나쁠 때 효과를 발휘한다
\--> 클러스터링 팩터가 나쁘면 Disk I/O 가 많기 때문
- 6번 리프블록을 읽어 12번 테이블 블록을 읽는 시점에 13, 15, 18 번 블록까지 미리 적재
- 연관 파라메터는 아래와 같다
SQL> select ksppinm name,
2 ksppstvl value,
3 decode(bitand(ksppiflg/256,1),1,'true','false') ses_modifiable,
4 decode(bitand(ksppiflg/65536,3),1,'immediate',2,'deferred',3,'immediate','false') sys_modifiable,
5 ksppdesc description
6 from sys.x$ksppi i, sys.x$ksppcv v
7 where i.indx = v.indx
8* and i.ksppinm in ('_table_lookup_prefetch_size','_table_lookup_prefetch_thresh','_multi_join_key_table_lookup')
9
NAME VALUE SES_M SYS_MODIF DESCRIPTION
-------------------------------------------------- -------------------- ----- --------- ------------------------------------------------------------
_table_lookup_prefetch_size 40 false false table lookup prefetch vector size
_multi_join_key_table_lookup TRUE false false TRUE iff multi-join-key table lookup prefetch is enabled
_table_lookup_prefetch_thresh 2 false false table lookup prefetch threshold
SQL>
- DB2 의 경우 리프블록을 먼저 스캔 후 결과집합을 rowid 순으로 정렬 후 테이블 엑세스 하는 방식을 취한다.
Direct parh I/O
- 일반적 I/O 는 버퍼를 경유하며 DBWR 이 주기적으로 변경 블록을 파일에 기록
- 버퍼 캐싱은 시스템 전반적 I/O 성능을 향상시키지만
개별 프로세스 입장에서는 대용량 I/O 시 모두 버퍼에 적재 후 읽으면 성능이 오히려 나빠 짐
재 사용 가능성이 없는 세그먼트의 경유는 버퍼에 적재하지 않는것이 시스템 전반적 성능에 유리하다 - Direct Path I/O 가 수행되는 경우는 아래와 같다
temp segment 블록을 읽고 쓸 때
병렬 full scan 을 수행
nocache 옵션 지정 한 lob segment 를 읽을 때
direct 옵션으로 export 수행할 때
parallel dml 수행 할 때
direct path insert 수행할 때
대기 이벤트 별 direct I/O
direct path read/write temp
\-데이터 정렬 시 pga 안의 sort area 를 이용한다
\-sort 공간 부족 시 temp tablespace 이용하는데
정렬 된 데이터를 temp tablespace 에 쓰고 읽을 때 direct path i/o 방식 사용한다
direct path read
\-병렬 쿼리로 full scan 수행 시 발생
\-수행 전 연관 세그먼트에 대해 버퍼 - 파일 간 동기화 수행하여 dirty buffer 를 없엠
버퍼의 내용이 파일에 기록되지 않은 상태에서 파일을 직접 읽으면 정합성에 문제
direct path write
- 병렬 DML 혹은 direct path insert 방식 사용 시 발생
/*\+ append \*/ 힌트 사용, 병렬 인서트, direct 옵션을 준 sql loader, CTAS 수행 시 발생
RAC 캐시퓨전
- 동시 사용자가 많을 때 부하 분산 목적으로 db 마다 분산전략을 사용하는데
1. 서버 간 복제
여러 서버를 두고 각 서버의 트렌젝션 데이터를 상호 복제
실시간 동기화 필요 시 복제 부하때문에 분산 효과를 얻기 힘듬
2. 업무 별 수직 분할
업무 영역 별 db 를 따로 두고 각기 테이블 관리하며 분산쿼리 이용해 조회
분산 영역으로 자주 조회되는 공통 영역의 범위에 따라 성패가 좌우 됨
3. 데이터 구분에 따른 수평 분할
스키마는 같지만 데이터 구분에 따라 db 를 따로 가져가는 방식
ex>동일 구조의 테이블인데 지역별로 다른 db
분할 데이터 간 의존성이 낮을 때 유리
서버 간 데이터 이동이 발생할 시 어떻게 처리 할 지 고민 필요
- 물리적으로 분산시킨 데이터를 논리적으로 통합하는 클러스터링 기법도 발전을 거듭 해 왔다
RAC 모델은 공유 디스크 방식에 기반을 두면서 인스턴스 간 버퍼 공유 가능
튜닝이 잘 되지 않아 많은 블록 I/O 를 일으킬 때인스턴스 간 경합이 심해 짐
RAC 의 캐시퓨전 프로세싱 원리를 이해 할 필요 있음
RAC 의 글로벌 캐시
- 클러스터링 된 모든 노드의 버퍼를 하나의 버퍼 캐시로 간주
- 필요 데이터블록이 다른 노드에 있으면 직접 가져 옴
- 모든 블록에 마스터 노드가 있고 이를 통해 캐싱 및 락 정보 관리
글로벌 캐시 원리는
읽고자 하는 블록이 로컬 캐시에 없을 때 마스터 노드에 전송 요청
마스터노드는 블록을 캐시한 노드에 전송 지시
어느 노드에도 캐싱되어있지 않으면 직접 읽도록 권한 부여
Current 블록
디스크로부터 읽은 후 갱신이 반영된 한개의 최종 원본
CR 블록
커런트 블록의 복사본으로 여려 버전이 존재 가능함
- RAC 의 Curent 블록은 Shared 와 Exclusive 로 나뉜다
Scur 상태일 때는 동시에 여러 노드에 캐싱 가능
XCur 상태일 때는 하나의 노드에만 캐싱 가능 - 자주 읽히는 데이터 블록을 각 노드가 Scur 로 캐싱하면 가장 효율적
- 한 노드가 Scur 블록을 Xcur 로 변경 시 다른 노드는 Null 로 down 되어 사용 불가 함
노드 간 전송 메커니즘 상세
1. 전송 없는 읽기
- A 노드에서 K 블록 읽을 때 어떤 노드에도 캐싱된 상태가 아니며 k 블록 scn 은 123
A 노드는 마스터인 B 노드에 전송 요청 - gc cr request
B 노드는 어떤 노드에도 K 를 캐싱하지 않음을 확인하고 A 노드에 직접 Scur 로 읽도록 권한 부여
A 노드는 디스크에서 읽어 로컬캐싱
2. 읽기/읽기
- A 노드만 K 를 Scur 로 캐싱 한 상태에서 C 가 K 를 Scur 로 읽음
C 는 MASTER 인 B 에 K 전송요청 - gc cr request
B 는 K 를 A 가 캐싱하므로 C 에 k 를 주도록 A 에 지시
A 는 C 에 전송, C 는 받아서 Scur 캐싱하고 B 에 완료 메시지 보냄
3. 읽기/쓰기
- A, C 노드 모두 K 블록을 SCur 로 캐싱 중
- C 가 K 를 Xcur 로 업그레이드 시도(블록 갱신 목적)
마스터 B에게 K 를 Xcur 로 업글 요청
B 는 k 를 A 노드도 캐싱하는 중임을 확인하고 A 에 null 로 다운하라고 지시
A 는 c에게 down 했음을 알려줌
C 는 k 를 Xcur 로 업글하고 결과를 마스터 B 에게 알림, A 가 null 로 다운한것도 함께 알림
C Xcur 로 변경하고 블록 변경하여 scn 증가 123 \--> 154
4. 쓰기/쓰기
- A 는 k 를 null 로 소유 중이며 C는 Xcur 로 가지고 있음
- C 의 Xcur scn 은 154 로 증가,
- 데이터파일 블록은 아직 123 이므로 dirty buffer 상태
- 이때 A 가 다시 k 를 Xcur 로 갱신 위해 소유하려고 함
A 가 마스터 B 에게 k 를 xcur 로 요청
B 는 k 를 C 가 Xcur 로 캐싱 학인, A 에게 전송 지시
C 는 A 에게 전송, 가진 블록은 null 로 다운(c 의 xcur 은 커밋되지 않아 rowlock 상태일 수도 있다)
A 는 k 를 Xcur 로 갱신했음을 B 에게 알림
\--> 다른 인스턴스가 생신중인 블록을 읽을 때 row lock 이 해소되기 전에도 블록을 주고받는다
\--> 쓰기 쓰기 상황이라도 Disk 에 기록 없이 바로 다른 Instance 에 전송한다(디스크 동기화 없이 진행)
A 가 xcur 로 다시 갱신했으므로 k scn 은 154 \--> 168
5. 쓰기/읽기
- A 는 k 를 xCur 로 가지고 있고 C 는 null 로 가지고 있음
- A 가 가진 Cur sCn 은 168
- DB 블록 scn 은 123
- C 가 k 를 Scur 모드로 읽기 시도
C 는 B 에게 k 를 sCur 로 요청
B 는 k 를 A 가 Xcur 로 캐싱 확인 후 C 에게 전송 지시
A 는 C 에게 블록 전송, 자신의 블록은 Scur 로 다운
A 에서 Commit 되지 않았을 경우 Current 가 아닌 Cr Copy 를 만들어 전송
--> C 는 읽기만 하므로 Current 를 보낼 필요까지는 없다
--> Current 를 보내면 언젠가 다시 가져와야 하므로 부담스럽다
A 에서 Commit 되더라도 바로 Current 블록을 보내지 않는다
--> 처음에는 Cr Copy 만 전송하다 일정 횟수 이상 반복 요청이 오면 Current 를 보냄
--> Current 를 보내려면 자신의 xCur 을 sCur 로 다운해야 하는데 곧이어 갱신이 필요한 상황이면 다시 xCur 로 업그레이드 해야 하기 때문
--> 그때는 sCur 을 가져간 다른 노드도 모두 null 로 다운해야 하므로 RAC 부하 증가
--> Cr Copy 를 보내는 횟수는 _fairness_threshold 에 의해 결정(10g defAult = 4, 11g 는 2)
--> 값에 도달하면 redo 버퍼를 비우고 Xcur 을 Scur 로 다운,
Scur 이므로 이후에 읽기 요청하는 노드는 Cr Copy 가 아니라 Scur 로 전송받음
- C 는 k 를 Scur 로 캐싱한 후 B 에게 알림, A 가 가진 블록이 sCur 로 다운된 것 도 같이 알림
캐시퓨전 성능 향상을 위해선
- 주로 읽기 위주라면 \_fairness_count 를 낮게 설정하는것이 성능에 도움
cr copy 전송 없이 빠르게 scur 로 다운하고 current 블록으로 전송하므로
xcur 을 scur 로 다운했다 다시 xcur 로 업글하는 반복 할 가능성이 적다면
읽기 요청이 반복되는 블록을 가급적 빨리 scur 로 보내주는것이 좋다
아래 쿼리의 downgrade ratio 가 높다면 current 로 공유할 수 밖에 없음에도 cr 을 만들어 보내주느라 비효율적 작업을 함을 의미
SQL> select
data_requests, fairness_down_converts
, round (fairness_down_converts / data_requests * 100) "DOWNGRADE RATIO(%)"
from v$cr_block_server;
DATA_REQUES FAIRNESS_DO DOWNGRADE R
----------- ----------- -----------
136788509 83742131 61
1 rows selected.
- rac 구성 시 데이터 가공 노드와 읽는 노드를 분리하는것은 성능에 좋지 않다
- Dynamic remastering 이 가능 함
A 가 master 인 리소스를 B 가 반복 요청한다면 어느순간부터 master 가 B 로 변경
자주 사용하는 리소스의 상태정보를 직접 관리하므로 성능 향상에 도움이 됨
RAC 캐시퓨전 부하도 I/O 부하와 같은 맥락이다
블록의 읽기 요청이 많으면 I/O 이벤트 증가, RAC 관련 이벤트 증가
SQL 튜닝을 통해 블록 읽기 요청 횟수를 줄여 인터커넥트 전송량을 감소시켜야 한다
Result cache
- Shared pool 에 위치
- 버퍼캐시는 쿼리에서 자주 사용되는 블록을 캐싱하는 메모리 공간
-> 이것도 비용이 수반되는 작업이므로 반복 엑세스가 많을 때 성능 저하 발생
-> BCHR 이 낮을 수 밖에 없는 대용량 데이터라면 더 어려움
11g 에서 제공하는 result cache 기능은
- 한 번 수행한 쿼리나 함수의 결과 값을 캐싱
- DML 이 발생하지 않는 테이블이나 반복 수행이 많은 쿼리에 효과적
- Result Cache: Latch, Result Cache: SO Latch 래치를 통해 관리한다
- V$RESULT_CACHE_OBJECTS 뷰로 확인 가능하다
SQL> select /*+ result_cache */ object_name from test1;
SQL> select
CREATION_TIMESTAMP
,ID
,TYPE
,STATUS
,NAME
,NAMESPACE
,BLOCK_COUNT
,CACHE_ID
from V$RESULT_CACHE_OBJECTS
ORDER BY CREATION_TIMESTAMP;
CREATION_TIMESTAMP ID TYPE STATUS NAME NAMES BLOCK_COUNT CACHE_ID
------------------- ----------- ---------- --------- -------------------------------------------------------------------------------------------------------------------------------- ----- ----------- ---------------------------------------------------------------------------------------------
2016/04/30 07:39:20 0 Dependency Published OPS$ORACLE.TEST1 1 OPS$ORACLE.TEST1
2016/04/30 07:39:20 1 Result Invalid select /*+ RESULT_CACHE */ * from test1 SQL 9 7dw9mfrbc4sckb8u8ubc9fswk1
2016/04/30 07:39:46 10 Result Invalid select /*+ result_cache */ object_name from test1 SQL 2 3ndv1c9p6scrv33mxya0cyknzg
2016/04/30 07:40:04 12 Result Invalid select /*+ result_cache */ object_name from test1 SQL 2 3ndv1c9p6scrv33mxya0cyknzg
2016/04/30 07:40:10 14 Result Invalid select /*+ result_cache */ object_name from test1 SQL 2 3ndv1c9p6scrv33mxya0cyknzg
2016/04/30 07:41:47 16 Result Bypass select /*+ result_cache */ object_name from test1 SQL 2 3ndv1c9p6scrv33mxya0cyknzg
6 rows selected.
- 메뉴얼 지정 방법일 때 아래와 같이 사용 가능하다
select /*+ result_cache */ col1, col2 from table;
- 함수에서는 아래와 같이 사용 가능하다
create or replace function test_fnc (in_num number)
return varchar2
RESUTL_CACHE RELIES_ON(cache_table)
is
..
-서버 프로세스는 result cache 메모리를 먼저 찾아보고 캐싱되지 않으면 쿼리 수행 후 캐시에 저장
두 가지 영역으로 구성 된다
-SQL Query result cache
-PL/SQL function result cache
관련 파라메터는 아래와 같다
- result_cache_mode
manual: result_cache 힌트 명시한 sql 등록
force: no_result_cache 힌트 명시하지 않은 모든 sql
Default manual - result_cache_max_size
sga 에서 bytes 로 result cache 사용량 지정
Default N/A - result_cachem_max_result
하나 sql 이 전체 result cache 에서 사용할 수 있는 최대 크기 % 지정
Default 5 - result_cache_remote_expiration
remote 객체의 result cache 결과를 얼마나 보관할지 분 단위로 지정
Default 0
크기 지정은 메리 관리 방법에 따라
- mamory_target 사용 시 0.25%
- sga target 사용 시 0.5%
- shared pool 지정 시 1%
- 어떤 방법이든 shared pool 의 75% 이하
사용 불가능한 경우
- 일관성 없는 결과가 나오는 경우 사용이 불가하다
DICTIONARY 오브젝트 참조
TEMP TABLE 참조
시퀀스 사용
DATE 관련 함수 사용할 때
SYS_CONTEXT, USERENV 등의 함수 사용 할 때 - 참조 테이블에 DML 발생 시 캐시 무효화 된다
변경 사항에 따른 쿼리 영향에 관계 없이 무조건 무효화
파티션으로 나뉘어져 있어도 동일하다 - 함수 사용 시 RELIES_ON 에 명시한 테이블에 DML 발생 시 캐시 무효화 된다
사용 유의사항 및 권고사항
- 바인드 변수가 달라져도 개별적으로 캐싱한다
변수 값이나 결과집합이 다양한 쿼리는 사용을 지양한다 - 자주 DML 이 발생하는 테이블을 캐싱 시 비 효율적이다
캐시 관리하는 과정에서 래치 경합 발생
result cache 의 hit ratio 가 낮아진다면 쿼리 수행 비용 증가 - Inline view, WITH, UNION 절 사용 시 힌트를 준 쿼리 블록만 독립적으로 캐싱된다
Where 조건절의 서브쿼리 캐싱은 불가능하다
- 작은 결과집합을 얻기 위해 대용량 데이터 읽을 때 권장
- 읽기 전용의 작은 테이블을 반복 엑세스 할 때 권장
- 읽기 전용 코드 테이블을 읽어 코드명 반환 할 때 권장
- ient result cache 기능도 함께 제공하나 설명은 되어 있지 않음
I/O 효율화 원리 요약
필요한 최소 블록만 읽도록 쿼리 제공
쿼리 튜닝 예시는 책 참조
최적 옵티마이징 팩터 제공
1. 전략적 인덱스 구성
2. DBMS 제공하는 기능 활용
파티션, 클러스터, IOT 등...
3. 옵티마이저 모드 설정
all_rows, first_rows
4. 통계정보 생성
아래 항목들이 통계정보로 수집 가능하다
CPU
평균 Single block, Multi block I/O 속도
평균 Multi block I/O 수
5. 필요 시 힌트를 적극적으로 사용한다
"구루비 DB 스터디 모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 I " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3134
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.