Tuning the Buffer Cache

Database Buffer Cache 구조

그림 설명.
DBA + Block class에 해시 함수를 적용하면 특정 bucket으로 할당됨.
여러 bucket을 하나의 cbc latch로 보호하기 때문에 경합이 원인이 된다.
Buffer Header는 doulble linked list로 연결되어 있다.
currnet block과 cr블럭은 같이 연결되어 있다.
latch를 획득시에 두가지 모드 shared 모드와 exclusive모드가 있다.
Bucket하나에 Header하나가 좋음.
Working Set은 버퍼의 부분집합이다. LRU list와 CKPT cue 두가지가 있다.
Working set의 갯수는 cpu의 갯수와 DBWR의 갯수에 따라 달라짐.


튜닝의 목표
1. hit ratio를 높일 것
   ※ hit ratio?  원하는 data block을 메모리에서 찾을 확률
       계산법 : 1 - physical reads/logical reads

SQL> select name, value
         from v$sysstat
        where name in ('db block gets from cache',
                               'consistent gets from cache',
                               'physical reads cache');

     - db block gets from cache : caching 되어 있는 data block을 읽은 횟수
     - consistent gets from cache : cr블럭을 읽은 횟수
     - physical reads cache : 디스크에서 data block을 읽은 횟수


    hit ratio만으로는 튜닝의 대상인지 판단하기 어렵다.
    예)
    A SQL               99%                  99만블럭 / 100만블럭
    B SQL               60%                  60블럭 / 100블럭


2. wait를 줄일 것 : 더 비중을 둔다.
   ① latch : cache buffer chains (cbc latch)
      관련 view : v$latch, v$system_event
      여러 유저가 같은 block을 access할 경우 cbc latch에 경합이 생긴다.
      비효율적인 SQL, hot segments일 경우 이 지표가 올라간다.

      hot segment를 찾는 방법
      ⓐ 아래의 SQL을 실행

SQL> select *
         from (select owner, object_name, object_type, statistic_name, sum(value)
                  from v$segment_statistics

                  group by owner, object_name, object_type, statistics_name
                  order by sum(value) desc)
         where rownum < 10

      ⓑ statspack을 남길 경우 level을 7이상으로 한다. -> segment 통계가 남는다.

   ② latch : cache buffer lru chains (cblc latch)
      관련 view : v$latch, v$system_event

   ③ buffer busy waits
      관련 view : v$system_event
      : 하나의 버퍼에 여러 개의 프로세스가 동시에 사용하려고 할 때 발생하는 wait지표 
        buffer lock을 header에 기록
      buffer busy waits를 확인하는 SQL

SQL> select class, count
         from v$waitstat
         where count>0
         order by count desc;

     block class를 확인하는 SQL

SQL> select rownum, w.* from v$waitstat w;

     block class의 의미

Class	Description
1	data block
2	sort block
3	save undo block
4	segment header
5	save undo header
6	free list
7	extent map
8	1st level bmb
9	2nd level bmb
10	3rd level bmb
11	bitmap block
12	bitmap index block
13	file header block
14	unused
15 + 2*r	undo header block(r = Undo Segment 번호. 0 = System undo segment)
16 + 2*r	undo block(r = Undo Segment 번호. 0 = System undo segment)

      15 : system이라는 undo segment의 헤더 블럭
      16 : system이라는 undo segment의 일반 undo 블럭
      17 : 사용자가 사용할 수 있는 undo tablespace안에 있는 1번 undo segment의 헤더 블럭
      18 : 사용자가 사용할 수 있는 undo tablespace안에 있는 1번 undo segment의 undo 블럭

      cf) read by other session
          : 한 프로세스가 데이터 블럭을 읽어서 메모리에 올리고 있는 중에 다른 프로세스가 읽으려고 할 때 발생하는 지표
           -> I/O의 문제가 있는 것.

   ④ free buffer waits
      관련 view : v$system_event
      server process가 lru알고리즘을 이용해서 free buffer 를 찾는다. 그때 free buffer가 없어서 기다릴 경우 발생하는 지표
      - 메모리의 크기를 늘여주거나 줄여준다.
         관련 파라미터를 설정한다.(계속해서 모니터링 해야 한다.)
           db_cache_size, db_keep_cache_size, db_recycle_cache_size
          사전 준비사항
           create index cust_idx storage (buffer_pool keep ...)
           create table customer storage (buffer_pool recycle ...)
           create index cust_name storage (buffer_pool keep ....)

          advisor를 사용하는 방법 : db_cache_advice를 on으로 설정하여야 한다.
            관련 view : v$db_cache_advice

   ⑤ reads waits
      - 관련 wait 지표
        db file sequential read : single block io를 할 때 올라감.
        db file parallel read : 병렬 처리로 data block을 읽어 올림.
        db file scattered read


튜닝 방법
1. Database Buffer Cache의 크기를 늘이거나 줄이는 것
   : ASMM, Advisor
2. SQL Tuning
3. Multiple Buffer Pool(default, keep, recycle)
    교도소 : recycle
    io를 줄이는 것이 목적.
4. Caching Table
  사전 지식 : LRU 알고리즘.
   아주 자주쓰는 작은 테이블을 caching tables로 만든다.

SQL> create table t1 .... cache;

-- select시에 hint를 이용
select /*+ cache */
* from t1;

5. DB_nK_CACHE_SIZE : multiple block size
6. DBWR 튜닝(N개 또는 Slave process(서버 프로세스가 DBWR의 역할을 대신하도록 한다.))
    - DBWR의 갯수 변경 : DB_WRITER_PROCESSES파라미터를 변경시켜주면 됨. 최대 20개까지 늘일 수 있음.
    - slave process : DBWR_IO_SLAVES파라미터를 변경시켜주면 됨.
7. Direct Path I/O
8. I/O system(SAME)
9. MBRC

flushing the Buffer Cache

SQL> alter system flush buffer_cache

기타 참고사항

- OWI (Oracle Wait Interface)?
   : 오라클 서버 내에서 일어나는 여러가지 일들을 여러가지 지표를 통하여 확인하고

- Cache Fusion
    : 한 데이터베이스에 두개 이상의 instance가 있는 환경(RAC)에서 disk에서 block을 읽어오는 것이 아니고 다른 instance의 buffer를 읽는 것.

- LRU알고리즘의 종류
  1. standard lru
  2. touch count lru

- SQL에서의 literal은 문자, 숫자, 날짜를 의미한다.

- 기술면접 예상질문 :
    nested loop join에 대해서 말씀해보세요.(비교적 쉬운 질문)
    Query Transformation에 대해서 말씀해보세요.
    logical optimization, physical optimization에 대해서 말씀해보세요.
    histogram에 대해서 아는데로 말씀해보세요.

- dirty buffer : 원본과 내용이 다른 버퍼

- keep의 크기는 들어갈 데이터의 크기에 조금 더 준다. - 왜? cr블럭을 위해서...

참고 : http://blog.naver.com/orapybubu?Redirect=Log&logNo=40047392715
         http://wiki.ex-em.com/index.php/Buffer_busy_waits