Tuning the Shared Pool

Shared Pool의 architecture
- library cache(주요 관심사)
- data dictionary cache(row cache)
- UGA(User Global Area)
- flashback buffer
- ash buffer


Shared Pool
  : 새로운 object (LCO:Library Cache Object) 가 메모리 할당 요구 - 가능하면 free chunk가 할당됨.
    큰 LCO 는 여러개의 chunk로 만들어짐.
    chunk는 연속적이다.
    LRU 알고리즘에 의해 관리됨.
    관련 지표 : latch : shared pool

  ※ Q  : 왜 shared pool tuning을 먼저 하는가?
      Re : hard parsing을 하면 상당히 비용이 든다.
             performance 관련 문제가 자주 생기는 영역이기 때문에
             shared pool의 튜닝이 완료되면 그 외의 영역에서 효과를 볼 가능성이 크기 때문에

Library Cache
  : 커서들과 관련된 복잡한 metadata가 저장되어 있음.
    SQL 문과 유저에게 공유될 PL/SQL block이 저장되어 있음
    동일 문장에 대한 반복 parsing을 줄일 수 있음.
    관련 view : v$librarycache
    관련 지표 : latch : library cache

  ● library cache에 튜닝이 필요한 것을 확인하는 방법
     1. statspack/AWR indicators
        - load profile
        - instance efficiencies
           execute to parse : 높을 수록 좋음. parse가 적고 execution이 크면 이 숫자가 큼.
                                      cursor 공유가 잘 안되고 있음. 음수가 되는 경우가 있다. 
                                      계산식 {100 * (1- parse/execute)}
           parse CPU to Parse Elapsed : parse elapsed는 parse하는데 걸린 시간.
       - top wait events : latch : shared pool, latch : library cache
          지표(latch : library cache, latch : shared pool) 들을 보고 적절한 대응을 한다.
       - time model

     2. v$librarycache의 주요 컬럼
        - gets : LCO를 찾아본 횟수, 명령을 던진다.-> 동일 문장을 찾으려고 한 횟수
        - gethits : 동일 문장을 찾은 횟수
        - pins : LCO를 읽거나 실행한 횟수, 
        - pinhits : LCO에 특정 표시를 하는 행위
        - reloads : 메모리 부족으로 내려진 sql이 다시 load된 횟수
                        명령을 던진다 -> 그 명령을 위한 메모리를 할당 -> 메모리에
                        증가되고 있다 -> hard parse가 크다.
        - invalidation : 참조하고 있는 객체가 alter 되었을 경우, invalidate됨.
                             dbms_stats로 통계를 새로 잡는 것도 invalid하게 만듬.
                            증가할 경우 hard parsing이 증가한다.

  ● latch : library cache지표를 줄이기 위한 튜닝 목표
    1. hard parsing을 줄이는 것
       : OLTP에서 주로 발생.
       ① 공유 가능한 문장이 되도록 한다.(코딩 규약 준수, 바인드 변수 사용 촉진 등)
       ② 메모리를 적당히 크게 줄 것.
       ③ reparsing을 유발하는 invalidation을 줄일 것 -> DDL사용 줄이고, 통계 생성 주기를 조금 길게 할 것

    2. soft parsing을 줄이는 것
       ① session_cached_cursor 파라미터를 설정한다.
           이 파라미터의 값이 설정되어 있으면 세션당 설정되어 있는 수만큼의
            LCO handle의 주소가 PGA로 옮겨진다.(3번이상 수행된 SQL에 한해서)
       ② hold_cursor 파라미터를 설정한다.
       ③ cursor_space_for_time 파라미터를 설정한다.

    3. 단편화(fragmentation)를 줄이는 것
       ① 서로 다른 크기가 원인일 경우
          ⓐ 10g R2 버전으로 업그레이드 -> 특정 크기의 배수로 chunk를 할당하기 때문에 단편화가 발생가능성이 줄어든다.
          ⓑ 예약 공간을 설정을 변경한다.
              - 관련 파라미터 : shared_pool_reserved_size, shared_pool_size
              - 관련 view : v$shared_pool_reserved (판단하는데 쓰임)
                                 request_misses : 0이면 예약이 바로 되는 것 -> shared_pool_reserved_size를 줄인다.
                                 request_failueres : 
       ② 들락날락
          ⓐ keep : 자주쓰는 오브젝트를 메모리에 오래도록 남긴다.
          ⓑ 이름이 없는 pl/sql블럭에 이름을 붙인다. -> keep
          ⓒ large pool 설정
       ※ 단편화란?
           : 서로 다른의 chunk가 들락날락 하기 때문에 총량은 많으나 실제로 쓸 수 있는 공간이 부족한 현상.
      
    
Data Dictionary Cache
 : 관련 view : v$rowcache
  dc_free_extents : tablespace생성시 extents 크기를 너무 작게 잡을 경우 값이 커진다. -> tablespace 새로 생성해야 한다.

UGA
 : large pool을 잡는다.
   v$sesstat을 사용하여 UGA에 해당하는 크기를 구함.

Large Pool

SQL> select * from v$sgastat where pool = 'large pool';

상황)
프로젝트 내에 직접 개발하는 것과 제품이 같이 있을 경우에서 제품내에서 바인드 변수를 사용하지 않아
제품이 실행되기만 하면 서버에 많은 부하를 줄 때 해법은?

  1. 제품을 새로 산다.
  2. 그냥 쓴다.
  3. cursor_sharing을 exact에서 similar를 바꾼다. 커서 공유가 된다.-> bind변수로 바뀐다.
    (단, 이 파라미터 변경시 많은 부작용이 있으므로 관련 내용을 충분히 습득한 후 적용한다. 8i~)
    OLAP = exact(가끔 SQL이 날아오기 때문에 정확한 실행계획을 선택하도록)로 두고
    OLTP = similar(비슷한 쿼리끼리 같은 실행계획을 쓴다.)


실습 예제) latch : library cache 관련 경합 예제 (선생님 블로그 보고 나중에 넣을 것)



기타 참고사항

- Granule 
   : SGA memory가 할당되는 단위. 메모리에서의 extent
                4M               or 16M
                 ↓                      ↓
                SGA 1G이하       2G

- Chunk
   : 메모리의 작은 조각
     Shared Pool에서 라이브러리 캐쉬 오브젝트를 위해 할당되는 메모리 단위.
     필요에 의해 다양한 크기를 가짐.

- Mutex
  : Mutual Exclusive의 줄임말.
    latch와 비슷하게 자원을 보호하는데 쓰이는 매커니즘
    자원 관리 매커니즘의 일부를 OS로 넘긴다. 
    경합의 우려가 latch보다 적다.
    latch로 자원을 보호할 때 보다 빨라짐.

   관련 view : v$mutex_sleep, v$mutex_sleep_history
   관련 wait 지표 : cursor:mutex x
                          cursor:mutex s
                          cursor:pin x
                          cursor:pin s
                          cursor:pin s wait on X

참고 : http://blog.naver.com/orapybubu?Redirect=Log&logNo=40047213691
         http://www.oracle.com/technology/global/kr/pub/columns/dbtuning02.html