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

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


Oracle Library Cache and Dictionary Cache



Library Cache



■ The V$LIBRARYCACHE Table

· 가장 최근의 인스탄스 startup이후 모든 library cache activity를 나타냄

· Library cache 행동을 감시

· 사용자 SYS 와 SELECT ANY TABLE 시스템 권한을 가진 사용자, 즉 SYSTEM과 같은 사용자만이 이용

  NAMESPACE column : SQL 문장과 PL/SQL 블록들을 위한 library cache activity
      ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER')
  PINS: library cache 내의 항목이 실행된 수
  RELOADS : 실행단계에서의 library cache miss의 수

■ Querying the V$LIBRARYCACHE Table

  SELECT SUM(pins) "Executions",
    SUM(reloads) "Cache Misses while Executing"
  FROM v$librarycache;


  [ 질의의 결과 ]

        Executions  Cache Misses while Executing
          ----------  -------------------------
            320871                  549
1. PINS column의 합은 SQL 문장, PL/SQL blocks, object 정의가 총 320,871번 실행하기 위해 access 되었음

2. RELOADS의 합은 실행횟수 중 549번의 실행이 library cache의 대 기 상태를 발생

3. 총 PINS에서 총 RELOADS의 비율 : 0.17%



총 RELOADS는 0에 가까워야 하고 PINS에서 RELOADS의 비율이 1% 이상이라면 library cache miss들을 줄여야 함





Dictionary Cache



■ The V$ROWCACHE View



· V$ROWCACHE TABLE 질의 : 캐쉬 기능을 확인하기 위해

PARAMETER : Data dictionary item, 'dc_'로 시작

ex) file description을 위한 통계 : dc_files

GETS : Data dictionary의 각 부분에 대한 요구 횟수

GETMISSES : 캐쉬 miss를 발생시키는 데이타 요구의 수

COUNT : 사용가능한 캐쉬 entry의 총수

USAGE : 그 중에서 데이타를 저장하고 있는 캐쉬 entry의 수

GETMISSES 대 GETS의 비율 10~15%를 초과할 경우 data dictionary cache가 사용할수 있는 메모리를 증가

→ SHARED_POOL_SIZE값 증가시킴.





멀티쓰레드서버에서 shared pool 튜닝



▶ Sard pool의 크기 증가 → SHARED_POOL_SIZE 증가시킴





Buffer Cache

♣ V$SYSSTAT
▶ 버퍼 캐쉬 튜닝을 위해 사용
  db block gets, consistent gets : 합은 데이타를 요구하는 총 횟수
  physical reads : 디스크상의 datafile에 access한 data 결과로써
request의 총 횟수

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


▶ 적중률(hit ration) : 데이타가 디스크에서 읽혀지지 않고 메모리에서 읽혀지는 비율
    1 - ( physical reads / (db block gets + consistent gets)) 


1.  Reducing Buffer Cache Misses
  - 적중률이 60%~70%보다 작다면 성능향상을 위해 캐쉬내 버퍼의 수를 증가
  - 버퍼 캐쉬를 더 많이 만들기 위해서 초기화 파라메터 DB_BLOCK_BUFFERS를증가 

-  X$KCBRBH
- 초기화 파라메터 DB_BLOCK_LRU_EXTENDED_STATISTICS에 의해 조절
[ 예제 ]
    캐쉬에 20개의 버퍼를 추가했을 때 얼마나 많이 캐쉬 적중이 일어날 것인지 를 결정
    SELECT SUM(count) ach
    FROM sys.x$kcbrbh
    WHERE indx <20;
적중률 = 1 - (physical reads - ACH/(db block gets + consistent gets)) 

2.  Removing Unnecessary Buffers
♣ X$KCBCBH 
- 더 작은 캐쉬의 성능을 판단하는 통계치를 포함
-  X$KCBCBH 테이블은 X$KCBRBH 테이블의 구조와 비슷
-  DB_BLOCK_LRU_STATISTIC = TRUE로 설정함으로써 통계치를 collect.

[ 예제 ]
  현재 버퍼가 100 버퍼라면 990버퍼로 줄었을 때의 cache miss는 얼마일까?
    SELECT SUM(count) acm
    FROM sys.x$kcbcbh
    WHERE indx >= 90;
  적중률 = 1- (physical reads + ACM / (db block gets + consistent gets))


Rollback Segment



·system undo header : 이 statistics의 값은 SYSTEM rollback segment의 header block를 포함하는 buffer들을 기다리는 수이다.

·system undo block : 이 statistics의 값은 SYSTEM rollback segment의 header block 이외의 다른 block들을 포함하는 buffer들을 기다리는 수이다.

·undo header : 이 statistics의 값은 SYSTEM rollback segment의 header block 이외의 다른 rollback segment의 header block들을 포함하는 buffer들을 기다리는 수이다.

·undo block : 이 statistics의 값은 SYSTEM rollback segment이외의 다른 rollback segments 의 header block과는 다른 blocks들을 포함하는 buffer들을 기다리는 수이다.



▶ ·SELECT class, count
    FROM v$waitstat
    WHERE class IN ('system undo headr', 'system undo block', 'undo header', 'undo block');


▶  결과값은
CLASS COUNT 
------------------------------------ ------------------
system undo header 2089 
system undo block 633 
undo header 1235 
undo block 942 

▶  동일한 시점에서 데이터를 요구하는 총 수를 비교하라.
▶ ·SELECT SUM(value)
    FROM v$sysstat
    WHERE name IN ('db block gets', ' consistent gets');

▶  결과값은
SUM(VALUE)
----------------------
929530
▶ 각 계층들을 위해 기다리는 값들이 요구하는 총수의 1% 보다 더 크다면 contention의 감소를 위해 더 많은 rollback segment의 생성을 고려해야 한다.



Redo log buffer



1. Space in the Redo Log Buffer

▶ Redo log space request에 대한 statistic은 user process가 redo log buffer에 있는 공간이 할당되기를 기다리는 시간이다.

·SELECT name, value
          FROM V$SYSSTAT
          WHERE name = 'redo log space requests';
▶ 이 값이 일관성있게 증가하면 process 는 버퍼의 공간을 위해 기다려야만 한다. 이 경우 redo log buffer의 크기는 증가시켜라.

▶ Redo log space requests의 값이 0에 가까울 때 까지 5%씩 redo log buffer의 크기가 증가시키도록 하라.



2. Redo Log Buffer Latches



1) The Redo Allocation Latch

▶ redo log buffer에서 redo entry를 위한 공간을 할당하는 것을 제어한다.

▶ 버퍼에 공간을 할당할 때는, 오라클 유저 프로세스가 반드시 redo allocation latch를 가지고 있어야만 한다.

▶ 만약 단 하나의 redo allocation latch가 있다면 한번에 단 하나의 유저 프로세스만인 버퍼에 공간을 할당하는 것이 가능하다.

▶ redo allocation latch에 복사한후 유저 프로세서는 latch를 해제한다.

▶ LOG_SMALL_ENTRY_MAX_SIZE



2) Redo Copy Latches

▶ Redo entry가 너무 커서 redo allocation latch에 복사할 수 없다면, 유저 프로세서는 버퍼에 entry를 복사하기 전에 redo copy latch를 갖고 있어야만 한다.

▶ redo copy latch를 잡고 있는 동안, 유저 프로세서는 버퍼에 할당된 공간 안에 redo entry를 복사한 후, redo copy latch를 해제한다.

▶ LOG_SIMULTANEOUS_COPIES



3) Redo Log Activity

▶ V$LATCH

·GETS : 이 칼럼은 latch에 대한 willing-to-wait 요구가 성공된 수

·MISSES : 이 칼럼은 초기 willing-to-wait가 성공된 시간의 수

·SLEEPS : 이 칼럼은 초기 willing-to-wait request이후에 한 프로세스가 latch를 요구하고, 기다린 시간의 수

- latch에 대한 요구가 성공될 때 GETS값이 하나 증가한다.

- latch에 대한 초기 요구가 waiting의 결과를 가질 때 MISSES값이 하나 증가한다.

- 프로세스가 latch를 두 번씩(initial request후에 한번, 두 번째 request후에 다시 한 번) 기다렸으므로 SLEEPS값은 둘 증가한다.

·MMEDIATE GETS : 각 latch에 대한 immediate request가 성공한 수

·IMMEDIATE MISSES : 각 latch에 대한 immediate request가 성공하지 못한 수

·SELECT ln.name, gets, misses, sleeps, immediate_gets, immediate_misses
          FROM v$latch l, v$latchname ln
          WHERE ln.name IN ('redo allocation', 'redo copy') AND ln.latch# = l.latch#;

▶  결과
NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
------------------  --------------------------  --------------------------  --------------------------  -------------------------- 
redo allo....  252867  63  0  0 
redo copy  0  0  22830  0 

·If the ratio of MISSES to GETS exceeds 1% => (MISSES/GET) * 100
·if the ratio of IMMEDIATE_MISSES to the sum of IMMEDIATE_GETS and IMMEDIATE_MISSES exceeds 1%
=> (IMMEDIATE_MISSES/(IMMEDIATE_GETS + IMMEDIATE_MISSES)) * 100


3. Reducing Latch Contetion



● 2개 이상의 오라클 프로세스가 동시에 동일한 latch를 갖고자 할 때 대부분의 경우 latch contention이 일어난다.

● latch contention은 하나의 프로세스가 한번씩 동작하는 single-CPU computer인 경우는 드물게 일어난다.



1) Reducing Contention for the Redo Allocation Latch

▶ LOG_SMALL_ENTRY_MAX_SIZE의 값을 감소시킴으로서 redo allocation latch에 복사된 redo entry 의 크기와 수를 감소할 수 있다.



2) Reducing Contention for Redo Copy Latches

▶ LOG_SIMULTANEOUS_COPIES의 값을 증가



Multi-threaded server



1. Reducing Contention for Dispatcher Processes



1) Identifying Contention for Dispatcher Processes



Busy Rates for Dispatcher Processes

▶ V$DISPATCHER

·SELECT network "Protocol", SUM(busy) / ( SUM(busy) + SUM(idle) ) "Total Busy Rate"
          FROM v$dispatcher
          GROUP BY network;
▶ 만약 특별한 프로토콜의 dispathcher process가 시간당 50%이상씩 바쁘게 되면, dispatcher process를 증가시킴으로서 성능향상에 좋을 수 있다.



Wait Times for Dispatcher Process Response Queues

▶ V$QUEUE

·SELECT network "Protocol",
          DECODE( SUM(totalq), 0 ,'NO RESPONSES',
          SUM(wait)/SUM(totalq) || 'hundredths of seconds ')
          "Average Wait Time per Response"
          FROM v$queue q, v$dispatcher d
          WHERE q.type = 'DISPATCHER' AND q.paddr = d.paddr GROUP BY network ;
▶ 특별한 네트워크 프로토콜을 위한 평균 wait time이 application이 동작중일 때 고정적으로 계속 증가된다면, dispatcher process를 추가시킴으로서 성능을 향상시킬 수 있울 것이다.



2) Adding Dispatcher Processes

▶ ALTER SYSTEM 명령 : MTS_DISPATCHER parameter를 를 추가

▶ MTS_MAX_DISPATCHERS



2. Reducing Contention for Shared Server Porcesses



1) Identifying Contention for Shared Server Processes

▶ V$QUEUE

·SELECT DECODE (totalq, 0, 'NO Requests', wait/totalq || 'hundredths of seconds')
          "Average Wait Time Per Requests"
          FROM v$queue
          WHERE type = 'COMMON' ;
▶ 얼마나 많은 shared server process가 동시에 동작하는지

·SELECT COUNT(*) "Shared Server Processes"
          FROM v$shared_servers
          WHERE status != 'QUIT';
2) Adding Shared Server Processes

▶ MTS_MAX_SERVERS

▶ 오라클이 자동적으로 shared server process를 증가하거나 명시적으로 아래 방법중 하나를 통해서 shared process를 증가시킬수 있다.

·ALTER SYSTEM 명령의 MTS_SERVERS parameter