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

리눅스 백업 스크립트 (0)	2020.03.19
오라클 백업(핫백업/콜드백업) (0)	2019.08.06
ConfrolFile 백업 및 복구 (0)	2018.12.05
UNDO & REDO 알아보기 (0)	2017.06.30
FLASHBACK - 사례 ( update 문에서 조건식 누락으로 이전 데이터로 복구 수행 ) (0)	2017.06.29

index 분석을 통한 reblild 대상 알아보기 (0)	2019.03.04
Oracle Events (0)	2018.12.11
I／O 효율화 원리 (0)	2018.10.29
gc buffer busy 대기이벤트의 Parameter P3의 의미 (0)	2018.10.29
Performance Tuning Tools (성능 튜닝 툴) (0)	2018.06.20

오라클 백업(핫백업/콜드백업) (0)	2019.08.06
Archive log mode에서의 백업 및 복구 (0)	2018.12.05
UNDO & REDO 알아보기 (0)	2017.06.30
FLASHBACK - 사례 ( update 문에서 조건식 누락으로 이전 데이터로 복구 수행 ) (0)	2017.06.29
오라클 백업 및 복구(Flashback) (0)	2017.05.19

Table/Index 에 대한 move를 통한 reorg 작업 (0)	2019.04.11
오라클 플랜을 보는 법 (1)	2018.12.06
SQL Profile 이용 SQL Plan 변경 (이전 글 PLAN_SET_OUTLINE 과 같이 알아두기) (0)	2017.08.31
PLAN_SET_OUTLINE 이용한 SQL Plan 변경 (0)	2017.08.30
해시조인 뽀개기 (0)	2017.08.23

구분	종류	종류
순위(RANK) 관련	RANK, DENSE_RANK, ROW_NUMBER	대부분 지원
집계(AGGREGATE) 관련	SUM, MAX, MIN, AVG, COUNT	SQL Server 경우 Over절 내 Orderby 지원 못함
순서 관련 함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD	ORACLE 만 지원
그룹 내 비율 관련 함수	CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT	PERCENT_RANK 함수는 ANSI/ISO SQL 표준과 Oracle DBMS에서 지원하고 있으며, NTILE 함수는 ANSI/ISO SQL 표준에는 없지만, Oracle, SQL Server에서 지원하고 있다. RATIO_TO_REPORT 함수는 Oracle에서만 지원되는 함수(현업에서 유용).
선형분석을 포함한 통계분석 함수	CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY	특화되어있으므로 생략

SQL trace _ 참조 (0)	2019.10.02
복수행 함수 (그룹 함수) (0)	2018.12.13
PLSQL - 다차원 콜렉션 (0)	2018.01.24
피벗 (행->열) (0)	2017.12.22
BULK COLLECT (0)	2017.08.17

Oracle Events (0)	2018.12.11
Achive Mode 설정 및 복구 (0)	2018.12.05
gc buffer busy 대기이벤트의 Parameter P3의 의미 (0)	2018.10.29
Performance Tuning Tools (성능 튜닝 툴) (0)	2018.06.20
Oracle Wait Event 모니터링 (0)	2018.03.08

구분	이벤트명	설 명
일상적인 Wait Event	db file scattered read	Full Scan시 OS에 I/O를 요청해놓고 대기
일상적인 Wait Event	db file sequential read	Index Scan시 OS에 I/O를 요청해놓고 대기
(IO, Network)	log file sync	변경 log buffer를 log file에 반영하는 동안 대기
	DFS lock handle	OPS 환경에서 노드간 분산 Lock 교환에 따른 대기
	global cache cr request	OPS 환경에서 노드간 Buffer Block 교환에 의한 대기
자원 경합에 따른 Wait Event	enqueue	Type에 따라 세분화 (24개의 enqueue type (9i))
	latch free	Name에 따라 세분화 (239개의 latch가 존재 (9i))
	buffer busy waits	동일블록에 대한 동시 액세스에 따른 경합
	free buffer waits	free buffer를 할당위해 DBWR의 Write를 대기
	Log buffer space	Log buffer를 할당 받기 위해 LGWR의 write를 대기
	library cache lock	SGA내의 library cache를 참조하기 위한 대기(검색)
	row cache lock	SGA내의 dictionary cache를 참조하기 위한 대기
Idle Event	SQL*Net message from client	Client로부터의 작업요청을 대기
Idle Event	Pmon timer	PMON이 할일 없을 때 대기하는 Event

Shared pool	library cache latch, shared pool latch, row cache objects
Buffer Cache	cache buffers chains latch, cache buffers lru latch, cache buffer handle
Redo log	redo allocation latch, redo copy latch, redo writing latch
OPS	dlm resource hash list

Latch	Lock
latch free ( library cache ) latch free ( library cache load lock)	library cache lock, library cache pin library cache load lock
latch free ( row cache objects )	row cache lock
latch free ( shared pool )

전체 글

'ORACLE > 백업및복구' 카테고리의 다른 글

'ORACLE > ADMIN' 카테고리의 다른 글

'ORACLE > 백업및복구' 카테고리의 다른 글

튜닝 전

1차 개선 - ROWNUM 사용

2차 개선 - NL 조인 유도하기 위해 힌트 사용

3차 개선 - 그룹함수를 분석함수를 사용하여 변경

4차 개선 - EMP_V 인라인 뷰의 그룹함수를 분석함수로 대체

'ORACLE > 튜닝' 카테고리의 다른 글

윈도우 함수(WINDOW FUNCTION)

그룹 내 순위함수.

3.1 RANK 함수

3.2 DENSE_RANK 함수

3.3 ROW_NUMBER 함수

일반 집계 함수

3.4 SUM 함수

3.5 MAX 함수

3.6 MIN 함수

3.7 AVG 함수

3.8 COUNT 함수

그룹 내 행 순서 함수

3.9 FIRST_VALUE 함수

3.9 LAST_VALUE 함수

3.9 LAG 함수

3.10 LEAD 함수

그룹 내 비율 함수

3.11 RATIO_TO_REPORT 함수

3.11 PERCENT_RANK 함수

3.11 CUME_DIST 함수

3.11 NTILE 함수

'ORACLE > SQL' 카테고리의 다른 글

성능 튜닝의 3대 요소

1. Block 단위 I/O

Sequentian read vs. Random access

Sequential read

Random access

성능 향상을 위해선 random access 를 줄이고 sequential read 를 높여야 한다.

Sequential read 에서 결과집합 선택도 높이기

Random access 줄이기

2. 메모리/디스크 I/O

버퍼 캐시 히트율

버퍼 캐시 히트율의 한계

Network / Filesystem cache

3. Single block I/O / Multi block I/O

블록을 Disk 를 통해 버퍼에 적재하는 방법은 크게 두 가지

인덱스 스캔은 왜 single block I/O 를 하는가

4 Table/Index Prefetch

인덱스 Prefetch

테이블 프리패치

Direct parh I/O

대기 이벤트 별 direct I/O

direct path read/write temp

direct path read

direct path write

RAC 캐시퓨전

RAC 의 글로벌 캐시

노드 간 전송 메커니즘 상세

1. 전송 없는 읽기

2. 읽기/읽기

3. 읽기/쓰기

4. 쓰기/쓰기

5. 쓰기/읽기

캐시퓨전 성능 향상을 위해선

Result cache

두 가지 영역으로 구성 된다

관련 파라메터는 아래와 같다

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

사용 불가능한 경우

사용 유의사항 및 권고사항

I/O 효율화 원리 요약

필요한 최소 블록만 읽도록 쿼리 제공

최적 옵티마이징 팩터 제공

'ORACLE > ADMIN' 카테고리의 다른 글

'ORACLE > ADMIN' 카테고리의 다른 글

'ORACLE > ADMIN' 카테고리의 다른 글

'ORACLE > ADMIN' 카테고리의 다른 글

'ORACLE > ADMIN' 카테고리의 다른 글

티스토리툴바