본문 : http://www.ischo.net -- 조인상 //시스템 엔지니어

+++++++++++++++++++++++++++++++++++++++++++++++++++++++


(9I) BUFFER CACHE ADVISORY : DB_CACHE_ADVICE, V$DB_CACHE_ADVICE
===============================================================

PURPOSE
-------

Oracle 9i에서 Buffer Cache의 크기를 정하기 위해 참조할 수 있는
DB_CACHE_ADVICE parameter와 V$DB_CACHE_ADVICE 에 대해 알아보기로 한다.


Explanation
-----------

Oracle 9i부터는 SGA의 영역의 크기를 온라인 상태에서 바꿀 수 있는 Dynamic SGA
기능이 제공된다. 동적으로 바꿀 수 있는 주요 요소로는 Shared Pool, Large Ppool,
Buffer Cache 세 가지이다.
그런데, 이 Dynamic SGA 기능을 이용해서 Buffer Cache의 크기를 조절하는 것은
사용자의 판단에 의해 이루어져야 한다. 이 때 사용자가 참조할 수 있는
통계 정보 생성을 위해, Oracle 9i에서는 Dynamic SGA 기능과 더불어,
Buffer Cache Advisory 기능을 제공하고 있다.

1. Buffer Cache Advisory

Buffer Cache Advisory는 특정 Buffer Cache의 크기를 조절했을 때 성능을
예측하는 통계 정보를 생성한다. 이 통계 정보를 바탕으로 사용자가 현재의
작업 부하를 고려해서 적당한 크기로 Buffer Cache의 크기를 조절할 수 있다.
(Dynamic SGA 기능 이용). Buffer Cache의 주요 특징은 다음과 같다.

1) initSID.ora 화일의 파라미터 DB_CACHE_ADVICE를 이용해서 이 기능을
enable/disable할 수 있다. 이 파라미터는 dynamic 파라미터이다.
즉, DBA가 원하는 시점에 온라인 상태에서 enable/disable할 수 있다.

2) 현재 Buffer Cache의 block들에 대한 참조 정보(reference trace)를 바탕으로
Buffer Cache의 크기를 달리했을 때의 성능을 주기적으로 simulation한다.

3) 위의 simulation은 Buffer Cache Advisory 기능이 enable된 이후 시점부터
Buffer Cache의 block들에 대한 모든 참조 정보를 이용한다.

4) 위에서 simulation한 정보를 V$DB_CACHE_ADVICE 뷰에 기록한다. 이 뷰에는
각 buffer cache 별로 현재 크기의 10%에서 200%까지 20개의 크기에 대한
simulation 정보를 기록한다. 각 크기 별로 기존 block 참조 정보를 이용해서
예상되는 물리적 읽기 수를 제공한다.

5) Buffer Cache Advisory 기능의 사용은 다음 두 가지의 오버헤드를 일으킨다.

6) CPU : Advisory 기능은 Buffer Cache 별로 bookkeeping을 위한 아주 약간의
CPU 오버헤드가 필요하다.

7) MEMORY : Advisory 기능은 Buffer Blcok 당 Shared Pool에서 약 700 bytes
정도의 메모리를 할당한다.


2. Buffer Cache Advisory Parameters : DB_CACHE_ADVICE

DB_CACHE_ADVICE 파라미터는 ON, OFF, READY 세 가지 값을 가질 수 있는데
(default : OFF), 각각의 상태의 의미는 다음과 같다.

- OFF : Advisory 기능이 disable되고, CPU나 MEMORY 오버헤드가 없음.
- ON : Advisory 기능이 enable되고, CPU나 MEMORY 오버헤드가 발생함.
- READY : Advisory 기능은 disable되나, Shared Pool의 메모리는 할당됨.

DB_CACHE_ADVICE의 값은 ALTER SYSTEM SET DB_CACHE_ADVICE={ON|OFF|READY}
명령을 이용하여 변경하는데, 값 변경 시 주목할 점은 다음과 같다.

1) OFF 상태 -> ON 상태
Shared Pool에서 메모리 할당을 받을 수 없어 ORA-4031 에러가 발생할 수도 있다.
그렇지만, Shared Pool에 충분한 메모리가 있는 경우에는 V$DB_CACHE_ADVICE 뷰를
reset하고 통계 정보를 수집한다.

2) READY 상태 -> ON 상태
Shared Pool에 이미 메모리가 할당되어 있기 때문에 ORA-4031 에러 없이 정상
동작한다.

3) ON 상태 -> READY 상태
V$DB_CACHE_ADVICE 뷰의 상태는 그대로 보존되고, Shared Pool의 메모리도 보존된다.

4) OFF 상태 -> READY 상태
Shared Pool에서 메모리 할당을 받을 수 없어 ORA-4031 에러가 발생할 수도 있다.
그렇지만, Shared Pool에 충분한 메모리가 있는 경우에는 정상적으로 메모리를
할당받는다.


3. V$DB_CACHE_ADVICE 뷰

V$DB_CACHE_ADVICE 뷰의 컬럼은 다음과 같다.

- id : Buffer Cache의 id (1 ~ 8)
- name : Buffer Cache의 이름
- block size : 현 Buffer Cache의 block 크기
- advice_status : Buffer Cache Advisory 기능의 상태(ON or OFF:Ready 상태도
            OFF로 표시)
- size_for_estimate : simulation에 사용한 Buffer Cache의 크기(단위:KB)
- buffers_for_estimate : simulation에 사용한 Buffer Cache의 크기
                (단위:block 갯수)
- estd_physical_read_factor : (물리적 읽기 예상# / Buffer Cache 읽기#)
- estd_physical_reads : 물리적 읽기 예상치

이 문서에서는 Buffer Cache Advisory 기능을 enable시키는 방법,
V$DB_CACHE_ADVICE 뷰의 정보를 해석하는 방법을 테스트 내용으로 설명하고,
이 기능의 활용 방안을 간단하게 기술한다.


Example
-------

1. Dynamic SGA 기능을 이용해서 Default Buffer Cache의 크기 조정 

Dynamic SGA 기능을 이용해서 Default Buffer Cache의 크기를 32M로 설정한다.

connect system/manager
SQL> alter system set db_cache_size=32M;

시스템이 변경되었습니다. 


2. Buffer Cache Advisor

Buffer Cache Advisor 기능을 enable시킨다.

connect system/manager
SQL> alter system set db_cache_advice=on;

시스템이 변경되었습니다.


3. V$DB_CACHE_ADVICE 뷰 확인

다음과 같이 Buffer Cache Advisory 기능이 enable된 상태의 V$DB_CACHE_ADVICE
뷰의 내용을 확인한다.

SQL> select * from v$db_cache_advice; 

ID      NAME            BLOCK_SIZE  ADV    SIZE_FOR_ESTIMATE
---------- -------------------- ----------  ---    -----------------
BUFFERS_FOR_ESTIMATE  ESTD_PHYSICAL_READ_FACTOR  ESTD_PHYSICAL_READS
--------------------  -------------------------  -------------------     
3      DEFAULT          4096      ON        3144           
786              NULL                          0     

3      DEFAULT          4096      ON        6288         
1572                                          0     

3      DEFAULT          4096      ON        9432         
2358                                          0     

........     

3      DEFAULT          4096      ON      59736         
14934                                          0     

3      DEFAULT          4096      ON      62880         
15720                                          0

테스트 DB에는 표준 block size가 4K인 Default Buffer Cache만 존재한다.
따라서, 위 질의의 결과에 대해서 ID=3인 Default Buffer Cache에 대한 row들만
나타난다.

각 row의 ADVICE_STATUS는 ON으로 세팅되어 있다.

현재 Default Buffer Cache의 크기가 32M이므로, SIZE_FOR_ESTIMATE 컬럼의
값이 약 10%인 3M에서 200%인 62M까지 20개 Buffer Cache에 대한 row들을
보여준다.

Buffer Cache Advisory 기능이 방금 초기화되어서 ESTD_PHYSICAL_READS의 값은
0, ESTD_PHYSICAL_READ_FACTOR의 값은 NULL로 나타난다.


4. Buffer Cache에 read문을 발생시키는 SQL 문장 수행

다음과 같이 disk에서 Buffer Cache로 DB block을 읽어들이는 질의를 수행해 보자.
이들 질의는 sales_history 스키마를 이용한다. 사용자 id와 Password는 sh/sh이다.

SQL> connect sh/sh
연결되었습니다.

SQL> select count(*) from sales s, times t
  2  where s.time_id = t.time_id;

SQL> select count(*) from sales s, products p
  2  where s.prod_id = p.prod_id; 

SQL> select count(*) from sales s, customers c
  2  where s.cust_id = c.cust_id;


5. V$DB_CACHE_ADVICE 뷰 정보를 이용한 Buffer Cache 크기 조절

앞의 질의를 수행한 후, V$DB_CACHE_ADVICE 뷰를 살펴본 예는 다음과 같다.

ID      NAME            BLOCK_SIZE  ADV    SIZE_FOR_ESTIMATE
---------- -------------------- ----------  ---    -----------------
BUFFERS_FOR_ESTIMATE  ESTD_PHYSICAL_READ_FACTOR  ESTD_PHYSICAL_READS
--------------------  -------------------------  ------------------- 
3      DEFAULT          4096      ON        3144           
786                        1.8014                2967     

3      DEFAULT          4096      ON        6288         
1572                      1.0174                1676     

3      DEFAULT          4096      ON        9432         
2358                      1.0052                1656     

3      DEFAULT          4096      ON      12576         
3144                      1                  1647

...     

3      DEFAULT          4096      ON      62880         
15720                      1                  1647


20 rows selected.

그런데, 위에 나타난 결과가 의미하는 바를 제대로 파악하기 위해서는 우선
ESTD_PHYSICAL_READ_FACTOR와 ESTD_PHYSICAL_READS 컬럼의 의미를 제대로 이해해야
한다. V$DB_CACHE_ADVICE 뷰를 설명할 때, 이 두 컬럼의 의미를 다음과 같이
설명하였다.

- estd_physical_read_factor : (물리적 읽기 예상# / Buffer Cache 읽기#)
- estd_physical_reads : 물리적 읽기 예상치

즉, estd_physical_read_factor는, 실제 Buffer Cache Advisory 기능을 enable시킨
이후, Buffer Cache에 실제 발생한 physical read number 대비, Buffer Cache의
크기를 V$DB_CACHE_ADVICE 뷰의 row에 나와 있는 크기로 조정했을 때 예상되는
physical read number(estd_physical_reads)의 비율을 의미한다.

그러면, 이러한 배경 지식을 가지고 위의 결과를 분석해 보자.

분석1)

4번 째 row 이후부터 estd_physical_read_factor, estd_physical_reads 컬럼의
값은 모두 1과 1647이다. 이 정보의 의미는 Buffer Cache Advisory 기능을
enable한 이후, 실제로 Buffer Cache로 읽어 들인 block 수가 1647이고, Buffer
Cache의 크기를 해당 row에 나타나 있는 크기로 조정을 해도 예상되는 physical
buffer read의 수가 1647임을 나타낸다. 왜냐하면, 이 1647개의 block 정보를
위의 sample 질의들을 수행하기 전에 해당 테이블들의 정보가 Buffer Cache에
올라와 있지 않았기 때문에, 어쩔 수 없이 physical read를 수행할 수 밖에 없기
때문이다.

분석2)

3번 째 row까지는 estd_physical_read_factor, estd_physical_reads 컬럼의 값이
모두 1 이상이고 1647 이상의 값을 갖는다. 이 경우는 Buffer Cache의 크기가 너무
작아서 해당 질의를 수행하기 위해 buffer로 읽어들여 온 block이 replace된 후에
다시 읽어들여 오기 때문에다. 즉, 같은 block을 위의 질의들을 수행하는 도중에
물리적으로 1번 이상 읽어들여 왔음을 의미한다.

결론)

위의 sample 질의와 같은 workload 상황 하에서는 실제 Buffer Cache의 크기를
약 12M 정도로 줄여도 무방함을 알 수 있다.


6. Buffer Cache Advisory 기능과 Shared Pool 메모리 사용

우선 Buffer Cache Advisory 기능을 disable시키기 전에 SGA 메모리 영역의 메모리
사용 현황을 확인한다.

connect system/manager
SQL> select * from v$sgastat;

POOL      NAME                  BYTES
----------- -------------------------- ----------       
        fixed_sga              285332
....
shared pool free memory            51132112
....
shared pool trigger inform            1716
shared pool sim memory hea          5417680
shared pool PL/SQL MPCODE            1109016
....

Buffer Cache Advisory 기능이 ON 상태이기 때문에, Shared Pool 내에
sim memory header라는 영역이 할당되어 있음을 알 수 있다. 현재 Default
Buffer Cache의 block 수는 7860개(32M/4K)이고, block buffer 당 700 bytes
정도의 메모리가 할당되어 sim memory header 영역의 크기는 약 5M이다.

다음과 같이 Buffer Cache Advisor 기능을 OFF 시키고, SGA 메모리 영역을 다시
확인한다.

connect system/manager
SQL> alter system set db_cache_advice = off;

시스템이 변경되었습니다.

SQL> select * from v$sgastat;

POOL      NAME                  BYTES
----------- -------------------------- ----------       
        fixed_sga              285332
....
shared pool free memory            56549780
....
shared pool trigger inform            1716
shared pool PL/SQL MPCODE            1109016
....

Shared Pool의 sim memory header 영역이 사라지고, 이 영역에서 사용하던 약 5M
정도의 메모리가 free memory로 반환되었음을 알 수 있다.

다음은 DB_CACHE_ADVICE 파라미터가 READY 상태일 때, SGA 메모리 영역을 미리
할당함을 알 수 있다.

connect system/manager
SQL> alter system set db_cache_advice = ready;

시스템이 변경되었습니다.

SQL> select * from v$sgastat;

POOL      NAME                  BYTES
----------- -------------------------- ----------       
        fixed_sga              285332
....
shared pool free memory            49321520
....
shared pool trigger inform            1716
shared pool sim memory hea          5415464
shared pool PL/SQL MPCODE            1115020
....

Note :
Buffer Cache Advisory 기능이 enable된 상태에서 Dynamic SGA 기능을 이용해서
특정 Buffer Cache의 크기를 변경하더라도 V$DB_CACHE_ADVICE의 정보가 변경되지
않는다. 따라서, Buffer Cache Advisory 기능 사용 도중에는 Dynamic SGA 기능을
사용하지 않는 것이 좋다.


Reference Documents
-------------------
<Note:1008866.6>