by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

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






문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.