성능 튜닝의 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

크기 지정은 메리 관리 방법에 따라

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. 필요 시 힌트를 적극적으로 사용한다

I／O 효율화 원리