Chapter 9 Tuning Buffer Cache

Chapter 9 Tuning Buffer Cache

1.Oracle Database Architecture

1)Buffer Cache: Default, Keep, Recycle , nK buffer (개별적 할당가능)

                  - Using Parameter : \show parameter cache_size 로 조회 가능.

2)DBWn : 0~19 (20\개 프로세스 -> 개수 조절가능.

3)System, Temp , Sysaux, Datafile, Und

4)LRU 기반의 정책
             5) cache fusion(RAC 환경에서 인스턴스 failover)

6)증분checkpoint 메커니즘(lgwr부터 수행 scn번호 증분) 

7)Advisors:자동메모리관리(asmm)를 사용안할시 활성화.

8)대량 i/o작업시 os 공간에 private pool할당 ex)append hint 절 혹은 direct paht 설정 direct=Y

2..Buffer Hash Table for Lookups

1)  buffer cache를 효과적으로 관리하기 위해  hash chain 구조를 사용

2)  CBC latch

A  Cache된 data block을 찾을 때 획득해야 하는 latch

        B  Buffer를 탐색/변경하려는 p/s는 반드시 해당 체인을 관리하는 CBC latch를 획득해

                                   함

3)  hash bucket table

A.       header들 관리

B.       함수를 통해 그룹화된 버퍼 헤더 목록 관리 테이블

3.buffer chain

             1) 하나의 해시 버킷에 연결된 버퍼 헤더 집합

             2) 실제 양방향이다.

             3) 동일한 해쉬값을 갖는 헤더 블록들은 하나의 해쉬 버켓에 의해 관리

4) 헤쉬버켓의 버퍼 헤더들은 double linked list 형태로 연결되어 하나의 hash chain으로 관리

4.. buffer heather 들은 shared pool에 저장되어 있다.

                  1) 버퍼헤더는 포인터 값

                  2) 버퍼의 메타정보를 갖고 실제 버퍼 캐시와 연결된

                  3)bufferheader에 대한 정보는 x$bh

 

5..Working set

 1)buffer cache의 모든 buffer들은 반드시 LRU/LRUW list에 속한다

 2)instance 가동시 buffer pool에 최소 8개 할당

                           select set_id, dbwr_num from x$kcbwds order by set_id;

                           0000.. DBw0 이면

                           2개면 양분되어 0 1 0 1이 된다.

           3)LRU list + LRUW list(dirty list)

           4)working set 수 = _db_block_lru_latch로 결정

                           > select count(*) from v$latch_children

                           where name like 'cache buffers lru chain%';

 

 

#LRU list

main : 사용된 buffer들이 대한 list(used list). touch count 알고리즘을 쓰기 때문에 hot영역과 cold 영역으로 나뉜다.

sub : free buffer list. 먼저 확인하는 부분. 확인이 안되면 main으로 올라가서 lru end에 저장

 

# LRUW list(dirty list)

             main : 변경된 buffer list.

             sub : 현재 dbwn에 의해 기록중인 buffer list

 

 

 

 

<오라클 block은 어떻게 hash bucket에 적재되는가?>

 

racle은 buffer cache에 적재된 block을 검색하기 위해 DBA(Data Block Address)와 class number에 대해 간단한  hash 함수를 적용한 결과를 이용해 해쉬 bucket을 적재 /검색한다.

db_block_scan_max_pct(기본값은 40)

 

 

 

6. Symptoms

 

 

1)latch에 의한 경합이 문제

              2)Latch:cache buffer chains (CBC latch)    

                동일 table이나 index를 여러 세션이 동시에 스캔 수행시 발생

                SQL 문 있는지 확인시 가장 먼저 획득해야 하는 것.

 

 

 

 

7.Latch:cache buffer LRU chains

1)서로 다른 table이나 index들을 여러 세션이 동시에 처음 읽거나 데이터의 변경이 빈번할 때 발생

2)버퍼 캐시에 원하는 블록이 존재하지 않아 working set scan시 cache buffers LRU chain latch의 획득해야 함

               주요 원인은 비효율적인 SQL의 과도한 free buffer : hot block

 

8.Buffer busy waits

             1)buffer busy wait: 많은 세션이 특정블럭에서 경합이 자주 발생할때.

                  2)해결방법 : block size를 줄여 경합을 줄이거나 revers index를 고려해볼만도함.

                  3)free buffer wait: free buffer 를 찾지 못한경우(lru list에서 40% 탐색후 시점)

                 4)해결방법: buffer cache size 늘린다.

9.Read waits

1)Index Scan : DB File Sequential Read

2)FTS(Multi Block Access) : DB File Parallel Read, DB File Scattered Read

3)Read Wait Time이 길 경우

Disk에 과도한 요청을 하는 SQL을 튜닝한다

디스크의 용량을 추가한다.

 

10. Free buffer waits

1)Solution

Buffer Cache 사이즈를 적당히 조절한다

Cache 옵션을 사용한다

10g부터는 Small Table에서 FTS를 하는 빈도가 상당히 높다. 

 

à       Reload의 원인 -> Cache 옵션 지정

Keep, Recycle Pool을 사용한다

빠른 쓰기를 위해서 DBWn를 증가시킨다

Private I/O(OS Memory) Pool 사용

 

 

 

11. Cache hit ratio

메모리 상에 내가 요청한 데이터를 찾을 수 없기 때문에 떨어진다.(큰 데이터가 많이 있거나.... ) ->  keep / cache option 사용으로 해결

A low cache hit ratio, below 80%

 

해결방안 : size문제나 경합 문제를 찾아서 해결

 

12. Cache Buffer Chains Latch Contention

 

 

 

1)cache buffer chains latch에 대한 경합이 발생하는 경우

               원인 : 1> hot block    2> 잘못 작성된 SQL

               비효율적인 SQL문, execution 당buffer get이 높은 SQL문.

                 Logical reads가 높은 SQL문(large index range scan), full table scan or hot block(hot buffer)에 의한 경우

 

 

 

2)HOT block(hot buffer) 발생하는 경우

                 다수의 세션이 동일한 cache buffer chains 래피에 의해 보호되고 있는 하나 이상의 블록들을 반복적으로 접근할 대 발생

               db_file_scattered_read는 full scan발생

               db_file_sequential_read는 single block 에 대한 read가 많이 발생

               긴 해쉬 체인들(long hash chains)

               DBWR의 작업략이 많을 때 발생

               cache가 너무 작을 때 발생

 

 

3)해결 방법 및 고려 사항

               -Statpack이나 awrrpt를 통해서 상태 확인

             -cache buffers chains latch wait event를 일으키는 SQL 튜닝

             -execution 당 buffer get이 높은 SQL 튜닝

             -table full scan보다 index range scan이 빈번하게 발생하는 경우 index 의 검색 --range를 축소할 수 있도록 유도

             -table을 EXPORt하고, pctfree수치를 매우 높게 설정 후 데이터를 IMPORT

             -table의 block당 record의 수를 최소화

 

13.Finding Hot Segments

 

1)Characteristics of cache buffer chains latch contention:

              – Many accesses to one or more block under the same latch

              – Worse with larger block sizes

                CBC latch가 많이 발생하면

 

2)To find hot segments:

 

             SQL> SELECT * FROM ( SELECT owner, object_name,

                     object_type, statistic_name, sum(value)

                     FROM V$SEGMENT_STATISTICS

                 GROUP BY owner, object_name, object_type, statistic_name

                   ORDER BY SUM(value) DESC)

                    WHERE ROWNUM < 10;

                 query까지 조회 가능 : 비효율적인 것은 무엇인가…

 

14. Buffer Busy Waits

 

1) Application-level contention for buffers in the buffer cache

 

 

2)Identify buffer busy waits contention: block 별 wait 정보들을 조사할 때

 

SELECT object_name, value

FROM V$SEGMENT_STATISTICS

WHERE statistic_name 'buffer busy waits' AND

value > 20000;

SELECT class, count

FROM V$WAITSTAT

WHERE count>0

ORDER BY count DESC;

 

15.Calculating the Buffer Cache Hit Ratio

 

SELECT name, value

FROM v$sysstat

WHERE name IN ('db block gets from cache',

'consistent gets from cache',

'physical reads cache');

 

                 --dual에 놓고 계산--

PHYSICAL READS = 'physical reads cache')

 

LOGICAL READS = ('consistent gets from cache' +

'db block gets from cache')

 

HIT RATIO = 1 - PHYSICAL READS/LOGICAL READS

             select (1-(14081/642657))*100 from dual;

 

 

16. Buffer Cache Hit Ratio Is Not Everything

 

1) A badly tuned database can still have a hit ratio of 99% or better.

 

2) Hit ratio is only one part in determining tuning performance.

             SQL 구문, wait event와 함께 보아야한다.

 

3) Hit ratio does not determine whether a database is optimally tuned.

 

4)      Use the wait interface to examine what is causing a bottleneck:

(아래 세가지를 같이 보아야 한다.)

             – V$SESSION_WAIT

             – V$SESSION_EVENT

             – V$SYSTEM_EVENT

17.Interpreting Buffer Cache Hit Ratio

 

1)Hit ratio is affected by data access methods:

             – Full table scans (index full scan도)

             – Repeated scans of the same tables : 인위적 상승

             – Large table with random access : 100만건 있으면 첫번째 걸릴 수도있고 마지막에 걸리수도 있다.

             – Data or application design

 

2)Investigate increasing the cache size if:

             먼저 조사해야 할 부분

             – Hit ratio is low

   – Application is tuned to avoid full table scans : index 같은 것들을 더 추가하던가 하여야 한다.

 

18.Read Waits

 

1) List of wait events performing disk reads into the buffer cache:

 

             – db file sequential read (index)

비효율적인 SQL문장이나 비효율적인 index 스캔이 자주 수행ㅎ되는 경우 불필요한 물리적 I/O로 인해 db file sequential read대기가 증가할 수 있다.

선택도(selectivity)가 좋지 않은 index의 사용은 db file sequential read 대기의 주범이다.

부적절한 index의 사용은 i/O분만 아니라 버퍼 캐시의 경합을 유발할 수 있다.

SQL 문장에 최적화되고, index를 효율적으로 사용하는 것만으로 대부분의 문제를 미연에 방지할 수 있다.

 

             – db file parallel read (multi block)

 

             – db file scattered read (multi block) : 가장 일반적

                           db file scattered read 대기가 주로 발생하는 sql 문을 추출.

만일 불필요하게 FTS or index fast_full scan을 수행하고 있다면 SQL문 tuning하여 좀 더 합리적인 index를 생성해주면 문제는 해결

                           넓은 범위의 데이터를 읽을 때는 FTS가 훨씬 유리한 경우가 많다.

무리하게 index를 생성시켜 주는 것이 아니라, 해당 SQL문의 특성을 고려하여 FTS or index range scan이 유리한지에 대한 판단이 필요.

 

2)If wait time for reads is high:

            

– Tune the SQL statement that issues most disk reads by sorting V$SQL by DISK_READS and BUFFER_GETS.

                           V$SQL : 물리적 논리적 읽기를 저장한 view

 

                          – Grow buffer cache if needed.

 

                          – Reduce writes due to checkpointing.

 

                          – Add more disk capacity.

                           분산 효과를 높일 수 있다.

 

 

 

 

19.memory상에 free buffer가 없을 경우

 

1)buffer cache가 너무 작거나 dirty block들을 disk에 기록하는 작업이 빠르지 못한 경우. DBW의 갯수를 늘릴 수 있다.

 

2)ree buffer waits 대기이벤트는 버퍼캐쉬 내부에 데이터블록을 읽어 들이거나, CR이미지를 생성하기 위한 free buffer을 찾지 못할 때 발생

이것은 버퍼캐쉬가 너무 작건, 더티 블록들을 디스크로 기록하는 작업이 충분히 빠르지 못하다는 것을 의미힘

 

LRU list에서 free buffer를 찾을 때 _db_block_scan_max_pct(기본값 40) 값만큼 스캔을 하고도 free buffer를 찾지 못하면 스캔을 멈추고 DBWR에게 dirty block을 disk에 기록하고 free buffer를 확보할 것을 요청한다. 쓰기가 완료되면 해당 buffer를 사용한다.

 

 

 

             [발생 원인]

 

             1. 비효율적인 SQL문

                           v$sql.disk_reads를 수행하는 SQL문 조회.

해당 SQL문들은 full table scan, index fast full scan 또는 선택도가 좋지 않은 index를 사용하여 table을 접근한 것

 

             2. 불충분한 DBWR p/s 수

하나의 DBWR p/s만 존재한다면, 모든 working set에 대한 service를 하나의 DBWR p/s가 수행.

다수의 DBWR p/s가 존재한다면, working set을 균등하게 분배하여 더 많은 DBWR p/s는 더욱 효율적으로 working set을 service할 수 있고 더 높은 처리량을 보장

                           > select set_id, dbwr_num from x$kcbwds order by set_id.;

 

             3. 느린 I/O sub system

DBWR에서 db file parallel write 대기시간이 길게 나타난다면 I/O system에 문제가 있다고 판단

Direct I/O를 사용하는 경우 CPU 개수가 충분하다면, DB_writer_processes 값을 조정해서 DBWR의 개수를 증가시키는 것을 병행할 수 있다.

 

20.Solutions

1) Properly size the buffer cache. : ASMM 사용 권장

             2) Cache objects.

                           table의 option 중

                           cache - 상주시켜라

                                        FTS : M(hot)

                                        index : M(hot)

                           nocache

                                        FTS : cold 영역(L)으로

                                        index : hot영역으로(M)

 

             3) Use the keep and recycle pools.

               hit ratio 높이기

 

             4)Increase the writing speed of DBWn.

                          

             5)Use private I/O pool.

                           direct path 방식을 적절히 사용

                           대용량일 경우                  

 

 

 

 

 

====================================================================

select total_waits

from v$system_event

where event='free buffer watis';

 

사용 가능한 buffer 대기수가 높으면 DBWn 수를 증가. I/O slave 구성 고려.

 

[출처] Chapter 9 Tuning Buffer Cache- (Pride of OCM ...& OCMKOREA) |작성자 zzealouss