Oracle Buffer Cache 튜닝과 Multiple Buffer Pool의 사용
우선 Keep buffer에 대해 알아보기 전에 Buffer Cache에 대해 짚고 넘어가겠습니다.
이전에 간단하게 SGA에 대한 포스팅을 하면서 Buffer Cache에 관해서도 짤막하게 포스팅 한 적이 있습니다. (Oracle SGA)
Buffer Cache Overview
많은 유형의 작업에서 Oracle Database는 버퍼 캐시를 사용하여 디스크에서 읽은 데이터 블록을 저장합니다. 그러나 Oracle Database는 정렬 및 병렬 읽기와 같은 특정 작업에 대해 버퍼 캐시를 사용하지 않을때도 있습니다.
버퍼 캐시는 SYSTEM GLOBAL AREA(SGA)의 한 부분이며, 오라클 인스턴스에 접속된 모든 프로세스들이 공유하여 사용합니다. 사용에 대한 이점은 자주 사용되는 데이터 블럭에 대한 물리적 I/O를 줄이는데 있습니다. 데이터베이스 버퍼 캐시를 효과적으로 사용하려면 불필요한 자원 소비를 피하기 위해 응용 프로그램에 대한 SQL 문을 튜닝해야합니다. 그러기 위해서는 자주 실행되는 SQL 문과 버퍼에 많은 블럭을 읽어오는 SQL 문이 올바르게 작성되었는지 확인해야합니다.
병렬 쿼리를 사용할 때는 데이터베이스 버퍼 캐시를 사용하는 대신에 PGA (Program Global Area)로 직접 읽기를 수행하도록 데이터베이스를 구성해야합니다. 이러한 구성은 시스템에 많은 양의 메모리가있는 경우 적절합니다.
Oracle 7 까지의 Buffer Cache는 별개의 틀처럼 기능하여 각 오브젝트들의 특성별, 차별화에 따른 구현이 어려웠고, 지금의 Buffer Cache 같은 기능을 제공하기 위해 Oracle 8i 에서부터 Multiple Buffer Pool이라는 기능이 추가 되었습니다. 물론 LRU 알고리즘에 의해 Buffer cache가 관리되었지만 이는 가끔 아주 큰 오브젝트가 사용되어 질 때에는 물리적 디스크 I/O의 증가를 야기 할 수 밖에 없습니다. 그러나 Oracle 8 이상에서 제공하는 Multiple Buffer Pool은 이러한 Object Access의 다양성 및 빈도의 차별성을 구분하여 Buffer Cache가 보다 세밀하게 데이터 블럭을 관리할 수 있게 되었습니다.
Buffer Cache 설정
새 데이터베이스 인스턴스를 구성 할 때 버퍼 캐시의 올바른 크기를 알 수 없습니다. 일반적으로 데이터베이스 관리자는 먼저 캐시 크기를 추정 한 다음 인스턴스에서 대표적인 워크로드를 실행하고 관련 통계를 검사하여 캐시가 필요 이상으로 구성되지 않았는지 또는 부족하게 구성되지 않았는지 확인해야합니다.
이 섹션에서는 데이터베이스 버퍼 캐시를 구성하는 방법에 대해 설명합니다. 자동 공유 메모리 관리를 사용하여 SGA (Shared Global Area)를 구성하는 경우이 섹션에 설명 된대로 데이터베이스 버퍼 캐시를 수동으로 조정할 필요가 없습니다.
- V$DB_CACHE_ADVICE View 이용하기
- Buffer Cache Hit Ratio 계산하기
- Buffer Cache Hit Ratio 해석
V$DB_CACHE_ADVICE View
v$DB_CACHE_ADVICE view는 다양한 잠재적 버퍼 캐시 크기에 대해 시뮬레이션 된 누락 비율을 보여줍니다. 이 View는 각 잠재적 캐시 크기에 대한 실제 읽기 수를 예측하는 정보를 제공하여 캐시 크기 조정을 지원합니다. 데이터는 또한 물리적 판독 계수를 포함하는데, 이는 버퍼 캐시가 주어진 값으로 크기 조정될 때 현재 물리적 판독 횟수가 변경 될 것으로 추정되는 요인을 나타냅니다.
그러나 실제 읽기는 파일 시스템 캐시에서 읽기를 통해 실제 읽기를 수행 할 수 있으므로 Oracle Database의 디스크 읽기라고 확신할 수는 없습니다. 따라서 캐시에서 성공적으로 블록을 찾는것과 캐시 사이즈 사이의 관계가 항상 일정하지 않습니다. 버퍼풀의 사이즈를 조정할 때, cache hit ratio에 영향을 주지 않는 의미없는 버퍼 추가는 피해야 합니다.
다음 그림은 물리적 I/O 비율과 버퍼 캐시 크기 간의 관계를 보여줍니다.
위 그림에 예시 된 예를 살펴보면 다음과 같은 결과가 나타납니다.
- 버퍼 수가 증가하면 물리적 I/O 비율이 감소합니다.
- 점 A와 B와 점 B와 C 사이의 물리적 I / O 감소는 그래프에서 점선으로 표시된 것처럼 매끄럽지 않습니다.
- 지점 A에서 지점 B로 버퍼를 증가시키는 이점은 지점 B에서 지점 C으로 보다 상당히 높습니다.
- 버퍼 크기의 지속적인 증가가 계속 늘어난다 하여도 그것이 가져오는 이익은 점점 줄어듭니다.
V$DB_CACHE_ADVICE view를 활성화 하는것에는 약간의 Overhead가 있을수 있습니다. Advisory view를 활성화하면 CPU 사용량이 약간 증가합니다. Advisory view와 관련된 CPU 및 메모리 오버 헤드를 줄이기 위해 Oracle Database는 샘플링을 사용하여 cache advisory statistics를 수집합니다. 버퍼 풀의 버퍼 수가 적으면 처음에는 샘플링이 사용되지 않습니다.
V$DB_CACHE_ADVICE view 사용법
- DB_CACHE_ADVICE 초기화 매개 변수의 값을 ON으로 설정하십시오. 이 설정은 advisory view를 사용가능하게 합니다. DB_CACHE_ADVICE 파라미터는 dynamic이며, 그래서 운영중에 설정을 on/off 할 수 있습니다.
- 운영중인 인스턴스에서 워크로드를 실행합니다. V$DB_CACHE_ADVICE view를 조회하기 전에 워크로드가 안정화 될때까지 기다려야 합니다.
- V$DB_CACHE_ADVICE view를 조회합니다.
다음 예는 V$DB_CACHE_ADVICE view의 다양한 캐시 크기에 대한 기본 버퍼 풀에 대한 예상 I/O 요구 사항을 리턴하는 값을 조회합니다.
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor,
estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';
이 쿼리의 결과는 다음과 같습니다.
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ------------ ----------- ------------
30 3,802 18.70 192,317,943 10% of Current Size
60 7,604 12.83 131,949,536
91 11,406 7.38 75,865,861
121 15,208 4.97 51,111,658
152 19,010 3.64 37,460,786
182 22,812 2.50 25,668,196
212 26,614 1.74 17,850,847
243 30,416 1.33 13,720,149
273 34,218 1.13 11,583,180
304 38,020 1.00 10,282,475 Current Size
334 41,822 .93 9,515,878
364 45,624 .87 8,909,026
395 49,426 .83 8,495,039
424 53,228 .79 8,116,496
456 57,030 .76 7,824,764
486 60,832 .74 7,563,180
517 64,634 .71 7,311,729
547 68,436 .69 7,104,280
577 72,238 .67 6,895,122
608 76,040 .66 6,739,731 200% of Current Size
위 예시에서는 캐시가 현재 크기 인 304MB 대신 212MB 인 경우 예상 물리적 읽기 수가 1.74 배 증가 함을 보여줍니다. 따라서 캐시 크기를 212MB로 줄이는 것은 좋지 않습니다.
그러나 캐시 크기를 334MB로 늘리면 잠재적으로 읽기가 0.93배 감소 할 수 있습니다. 시스템에서 추가 30MB 메모리를 사용할 수 있고 SGA_MAX_SIZE 매개 변수 값이 증가를 허용하는 경우 기본 버퍼 캐시 풀 크기를 334MB로 늘리는 것이 좋습니다.
Buffer Cache Hit Ratio 계산하기
Buffer Cache Hit Ratio은 디스크 액세스없이 버퍼 캐시에서 요청 된 블록을 찾은 빈도를 계산합니다. 이 비율은 V$SYSSTAT 성능보기에서 선택한 데이터를 사용하여 계산됩니다. 버퍼 캐시 적중률을 사용하여 V$DB_CACHE_ADVICE view에서 예측 한대로 물리적 I/O를 확인할 수 있습니다.
아래 테이블은 버퍼 캐시 적중률을 계산하는 데 사용 된 V $ SYSSTAT보기의 통계값을 설명합니다.
| Statistic | Description |
|---|---|
| consistent gets from cache | 버퍼 캐시에서 블록에 대해 일관된 읽기가 요청 된 횟수. |
| db block gets from cache | 버퍼 캐시에서 CURRENT 블록이 요청 된 횟수. |
| physical reads cache | 디스크에서 버퍼 캐시로 읽은 총 데이터 블록 수. |
V$SYSSTAT 조회
SELECT name, value
FROM V$SYSSTAT
WHERE name IN ('db block gets from cache', 'consistent gets from cache',
'physical reads cache');
V$SYSSTAT view를 이용하여 계산식을 만들어서 Buffer Cache Hit Ratio를 계산할 수 있습니다.
1 – ((‘physical reads cache’) / (‘consistent gets from cache’ + ‘db block gets from cache’))
실제로 이 계산식을 이용해 만든 쿼리입니다.
COL bcache for 999.99 HEADING 'Database Buffer Cache Hit Ratio' select ((1-((phy.value)/(cur.value+con.value)))*100) AS bcache from v$sysstat cur, v$sysstat con, v$sysstat phy where cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name='physical reads';
Buffer Cache Hit Ratio 해석
버퍼 캐시 크기를 늘리거나 줄 일지 결정하기 전에 먼저 버퍼 캐시 적중률을 검사해야합니다.
캐시 적중률이 낮다고해서 반드시 버퍼 캐시 크기를 늘리면 성능에 도움이된다는 의미는 아닙니다. 반대로 캐시 적중률이 높다고 해서 버퍼 캐시의 크기가 현재의 워크로드에 적합한 크기를 가진다고 할 수도 없습니다.
버퍼 캐시 적중률을 해석하려면 다음 요인을 고려해야 합니다.
- Single pass로 처리를 수행하거나 SQL 문을 최적화하여 자주 액세스하는 데이터의 반복 스캔을 피해야합니다.
- 동일한 큰 테이블이나 인덱스를 반복적으로 스캔하면 캐시 적중률이 인위적으로 높아질 수 있습니다. 자주 실행되고 많은 Buffer Gets를 발생하는 SQL문을 검사하여 실행 계획이 최적인지 확인해야합니다.
- 클라이언트 프로그램 또는 중간 계층에서 자주 액세스하는 데이터를 캐싱하여 동일한 데이터를 다시 쿼리하지 않게 합니다.
- OLTP 응용 프로그램을 실행하는 큰 데이터베이스에서는 많은 행에 한 번만 액세스하거나 액세스 하지 않게 합니다. 사용 후에 블록을 메모리에 보관할 목적이 없는 경우가 많기 때문입니다.
- 버퍼 캐시 크기를 지속적으로 늘려서는 안됩니다. 데이터베이스가 전체 테이블 스캔을 수행하거나 버퍼 캐시를 사용하지 않는 조작을 수행하는 경우 버퍼 캐시 크기의 지속적인 증가는 성능에 영향을 미치지 않습니다.
- 큰 테이블의 Full scan이 발생할 때 적중률이 낮은 것을 고려해야합니다.
Short table scan은 특정 크기 임계 값 아래의 small table에서 수행되는 스캔입니다. Small table에 대한 정의는 최대 Buffer Cache 사이즈의 2% 입니다.
Multiple Buffer Pool 설정
대부분의 시스템에는 단일 기본 버퍼 풀이 일반적으로 적합합니다. 그러나 응용 프로그램의 버퍼 풀에 대한 자세한 지식이 있는 DBA라면 Multiple buffer pool을 구성하면 도움이 될 수 있습니다.
비정형 액세스 패턴이있는 세그먼트의 경우이 세그먼트의 블록을 두 개의 별도 버퍼 풀 (KEEP Pool 및 RECYCLE Pool)에 저장하는 것이 좋습니다. 세그먼트의 액세스 패턴은 지속적으로 액세스 (때로는 Hot이라고 함)하거나 자주 액세스하지 않는 경우 (예 : 하루에 한 번 일괄 작업으로 액세스하는 큰 세그먼트)에는 비정형적일 수 있습니다.
Multiple buffer pool을 사용하면 이러한 불규칙성을 해결할 수 있습니다. KEEP Pool을 사용하여 버퍼 캐시에서 자주 액세스하는 세그먼트를 유지하고, RECYCLE Pool을 사용하여 객체가 버퍼 캐시에서 불필요한 공간을 소비하지 않도록 할 수 있습니다. 객체가 버퍼 캐시와 연결되면 해당 객체의 모든 블록이 해당 캐시에 배치됩니다. Oracle Database는 특정 버퍼 풀에 할당되지 않은 객체에 대해 DEFAULT 버퍼 풀을 유지 관리합니다. 기본 버퍼 풀 크기는 DB_CACHE_SIZE 초기화 매개 변수에 의해 결정됩니다. 각 버퍼 풀은 동일한 LRU 교체 정책을 사용합니다. 예를 들어, KEEP 풀이 할당 된 모든 세그먼트를 저장할만큼 크지 않은 경우 가장 오래된 블록이 캐시에서 만료됩니다.
적절한 버퍼 풀에 오브젝트를 할당하면 다음을 수행 할 수 있습니다.
- I/O 감소 또는 제거
- 객체를 별도의 캐시로 격리 또는 제한
이 섹션에서는 다중 버퍼 풀을 구성하는 방법에 대해 설명하고 다음 주제를 설명해 보도록 하겠습니다.
- Multiple buffer pool 사용시 고려 사항
- Multiple buffer pool 사용
- 개별 버퍼 풀에 V $ DB_CACHE_ADVICE보기 사용
- 개별 버퍼 풀에 대한 버퍼 풀 적중 비율 계산
- 버퍼 캐시 사용 패턴 검사
- KEEP 풀 구성
- RECYCLE 풀 구성
Multiple buffer pool 사용시 고려 사항
Multiple buffer pool을 사용하는 경우 다음 사항을 고려해야 합니다.
- 큰 세그먼트에 대한 Random Access.
- Oracle Real Application Cluster 인스턴스
큰 세그먼트에 대한 Random Access
매우 큰 세그먼트(버퍼 캐시의 크기와 비교하여)가 크거나 제한되지 않은 인덱스 범위 스캔으로 액세스되는 경우 LRU 에이징 방법에 문제가 발생할 수 있습니다. 비 순차 물리적 읽기의 상당 부분 (10 % 이상)을 차지하는 단일 세그먼트는 매우 큰 것으로 간주 될 수 있습니다. 큰 세그먼트에 대한 Random reads는 다른 세그먼트의 데이터를 포함하는 버퍼 블록이 캐시에서 제거 될 수 있습니다. 큰 세그먼트는 많은 양의 버퍼 캐시를 소비하지만, 실제로 캐시에 상주하면서 발생하는 이점이 없습니다.
매우 자주 액세스하는 세그먼트는 버퍼가 버퍼 캐시에서 수명을 초과하지 않을 정도로 자주 예열되므로 큰 세그먼트 읽기의 영향을 받지 않습니다. 그러나 이 문제는 큰 세그먼트 읽기로 인한 버퍼 에이징을 견딜 수있을 정도로 자주 액세스하지 않는 웜 세그먼트에 영향을줍니다. 이 문제를 해결하기위한 세 가지 옵션이 있습니다.
- 액세스 한 오브젝트가 인덱스인 경우 인덱스가 selective인지 판별하십시오. 그렇지 않은 경우 인데스를 selective으로 사용하도록 SQL 문을 조정하십시오.
- SQL 문이 최적화되면 큰 세그먼트를 별도의 RECYCLE 캐시로 이동하여 다른 세그먼트에 영향을 미치지 않도록하십시오. RECYCLE 캐시는 DEFAULT 버퍼 풀보다 작아야하며 버퍼를 더 빠르게 재사용해야합니다.
- Small 또는 warm 세그먼트를 큰 세그먼트에 사용되지 않는 별도의 KEEP 캐시로 이동하는 것을 고려해야합니다. 캐시에서 누락을 최소화하려면 KEEP 캐시 크기를 조정해야합니다. 쿼리에서 액세스 한 세그먼트를 KEEP 캐시에 저장하여 특정 쿼리에 대한 응답 시간을 보다 예측 가능하게하고 버퍼에서 LRU 정책에 의해 만료되어 제거되지 않도록 할 수 있습니다.
Oracle Real Application Cluster 인스턴스
Oracle RAC (Oracle Real Application Cluster) 환경에서 각 데이터베이스 인스턴스에 대해 Multiple buffer pool을 사용할 수 있습니다. 데이터베이스의 각 인스턴스에 대해 동일한 버퍼 풀 세트를 적용하지 않아도 상관없습니다. 각각의 인스턴스의 애플리케이션 요구 사항에 따라 각 인스턴스를 조정하면 됩니다.
Multiple buffer pool의 종류
Multi Buffer Pool의 Cache운영형태는 Keep, Recycle, default로 구분되어지는데 다음과 같습니다.
KEEP
요즘 많이 사용하는 In-Memory DB들과 비슷한 개념인데, 특정 재사용률이 아주 높은 Object들만 Buffer Cache에 항상 상주시키며, Access 빈도가 아주 높은 Object들에 대하여 Disk I/O를 최소화시켜 성능적인 개선을 이끌어낼수 있습니다. Keep 설정을 해두면 DB가 시작할때, 해당 데이터들을 자동으로 읽어 Buffer Cache위에 상주 시킵니다.
RECYCLE
재사용 빈도가 낮은 특정 Object들의 데이터 블럭들을 Access 후 바로 메모리에서 제거하도록 관리합니다. 따라서 Recycle 영역을 필요로 하는 다른 Object들이 언제나 즉시 필요한 Buffer를 할당 받을 수 있도록 유지합니다.
DEFAULT
기존의 단독 Buffer Cache와 동일한 관리체계를 가집니다.
Buffer Cache에 상주되어 있는 오브젝트 확인
모든 세그먼트가 가지는 블럭의 수를 조회하는 쿼리
COLUMN object_name FORMAT A40 COLUMN number_of_blocks FORMAT 999,999,999,999 SELECT o.object_name, COUNT(*) number_of_blocks FROM DBA_OBJECTS o, V$BH bh WHERE o.data_object_id = bh.OBJD AND o.owner != 'SYS' GROUP BY o.object_Name ORDER BY COUNT(*);
아래와 같은 결과 값을 보여줍니다.
OBJECT_NAME NUMBER_OF_BLOCKS ---------------------------------------- ---------------- OA_PREF_UNIQ_KEY 1 SYS_C002651 1 .. DS_PERSON 78 OM_EXT_HEADER 701 OM_SHELL 1,765 OM_HEADER 5,826 OM_INSTANCE 12,644
아래와 같은 쿼리를 통해 현재 Buffer Cache에 어떤 Object들이 상주하고 있는지 알수 있습니다.
select a.object_name "Object_Name", a.OBJECT_TYPE, round(((sum(v.cnt)*8192)/1024/1024),2) "buff_size(MB)"
from dba_objects a,
( select obj,count(*) cnt
from x$bh
group by obj ) v
where a.data_object_id = v.obj
group by rollup(object_name,object_type)
order by 3;
※ 8192는 오라클 DB에 기본값으로 설정된 (8K) Block Szie입니다. 다른 값으로 변경되었다면, 마찬가지로 해당 DB에 맞게 바꿔줘야 합니다.
Cache 대상 테이블의 선정
- 프로그램 중요도
Keep 대상 선정에 있어서 가장 중요한 부분이 바로 해당 세그먼트를 조회하는 업무 프로그램의 중요도입니다. 해당 프로그램이 중요하지 않다면 굳이 Keep Buffer를 사용해야 할 필요가 없습니다. 반대로 해당 세그먼트를 조회하는 프로그램이 중요도가 아주 높고 어떻게든 수행시간을 단축해야 한다면 프로그램 수행 빈도와 상관없이 KEEP 대상으로의 선정을 고려할 수 있습니다. - 세그먼트 크기
세그먼트 크기가 일정하지 않고, 과다하게 커지는 세그먼트는 Keep Buffer 의 효율성을 떨어뜨릴 수 있습니다. Keep된 세그먼트는 Keep Buffer의 용량이 부족하면 오래된 블록부터 Default Buffer 로 밀려나게 되는데, 크기가 계속 커지는 세그먼트가 Keep Buffer에 존재한다면 타 세그먼트를 조회하는 프로그램의 성능 저하를 가져올 수 있기 때문입니다. 따라서 일정한 사이즈 또는 변동 량이 심하지 않으면서 최대 크기가 일정 수준 이하인 경우의 세그먼트를 선정하는 것이 바람직합니다. 예를 들면 ‘최대 크기가 10 만 블록 이하인 세그먼트’ 같은 기준을 정할 수 있습니다. - Full Table Scan & Index Full Scan & Index Fast Full Scan
KEEP Buffer에 KEEP 된 세그먼트를 조회할 때 효율성을 극대화 하기 위해서는 다소 많은 량을 처리해야 하는 경우도 있습니다. Scan 범위가 넓은 비효율 Index Scan 이나 Full Table Scan, Index Fast Full Scan으로 처리되는 세그먼트가 대상이 될 수 있습니다.
Keep 대상 선정 SQL 쿼리
SELECT owner,
table_name,
index_name,
partition_name,
SUM(blocks) AS t_blocks
FROM (
SELECT sg.owner,
decode(SUBSTR(s.ob_type, 1, 5), 'TABLE', s.ob_name, 'INDEX', (
SELECT table_name
FROM dba_indexes
WHERE index_name = s.ob_name
) ) AS table_name,
decode(SUBSTR(s.ob_type, 1, 5) , 'INDEX', s.ob_name) AS index_name,
sg.partition_name,
sg.blocks
FROM (
SELECT DISTINCT object_name AS ob_name ,
object_type AS ob_type
FROM v$sql_plan
WHERE (operation = 'TABLE ACCESS'
AND options = 'FULL')
OR (operation = 'INDEX'
AND options = 'FULL SCAN')
OR (operation = 'INDEX'
AND options = 'FAST FULL SCAN')
) s,
dba_segments sg
WHERE s.ob_name = sg.segment_name
)
GROUP BY owner, table_name, index_name, partition_name
HAVING SUM( blocks ) > 100000;
Keep Buffer Pool, Recycle Buffer Pool 적용 방법
alter table hr.employeers storage(buffer_pool keep); alter index hr.employeers_pk storage(buffer_pool keep); alter table hr.salary storage(buffer_pool recycle);
Multi Buffer Pool Monitoring
각 Pool이 가지는 Buffer Cache Hit Ratio를 조회 하는 쿼리 입니다.
SELECT name, physical_reads, db_block_gets, consistent_gets, round(((1 - (physical_reads / (db_block_gets + consistent_gets)))*100),2) "Hit Ratio%" FROM V$BUFFER_POOL_STATISTICS;
결과 값은 아래와 같이 나옵니다.
NAME Hit_Ratio ------------- ----------------- KEEP 95.23% RECYCLE 85.01% DEFAULT 90.01%
이런식으로 Multiple buffer pool를 적용하여 Disk I/O를 감소시키고, 효율적인 Buffer Cache 사용이 가능해 집니다.
1 Response
[…] Buffer Cache에 대한 내용(https://rastalion.dev/archives/1142)에 이어서 Redo Log Buffer에 대해 이야기 […]