Oracle Library Cache and Dictionary Cache

2010.05.12 08:37

조인상 조회 수:8883

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

Writer : http://www.ischo.net -- ischo // System Engineer in Replubic Of Korea

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

 

본문 : 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
번호 제목 글쓴이 날짜 조회 수
55 캐릭터셋 확인하는 쿼리 조인상 2010.05.12 8158
54 v$datafile 의 status 에 recover로 표시되는 경우 조인상 2010.05.12 10021
53 NT기반 오라클환경에서 OS 다시 깔았을때 복구방법 조인상 2010.05.12 9186
52 AIO 문제때문에 오라클 실행안될때 조인상 2010.05.12 9702
51 제약조건 이름은 아는데 테이블을 모를경우 찾는 스크립트 조인상 2010.05.12 8475
50 오라클 온라인백업스크립트 구성 예 조인상 2010.05.12 7475
» Oracle Library Cache and Dictionary Cache 조인상 2010.05.12 8883
48 오라클 인덱스 분리해서 임포트 하는 방법 조인상 2010.05.12 6669
47 오라클 hotbackup 스크립트 조인상 2010.05.12 9616
46 오라클 삭제하는 방법 조인상 2010.05.12 14186
45 AIX 64bit - oracle 10.2.0.1 설치 file 조인상 2010.05.12 12786
44 오라클 홈디렉토리 변경 ( /oracle_backup => /oracle ) 조인상 2010.05.12 11961
43 Linux 32bit ( EntePrise AS 3.0) - 오라클 9.2.0.4 설치 조인상 2010.05.12 7364
42 INCREMENTAL, CUMULATIVE, COMPLETE EXPORT & IMPORT 조인상 2010.05.12 7177
41 오라클 cold 백업 스크립트(특정데이터파일만 추출) NT용 file 조인상 2010.05.12 9245
40 TNS-12540: TNS:internal limit restriction exceeded 조인상 2010.05.12 9340
39 테이블 컬럼의 데이터타입 변경 조인상 2010.05.12 14426
38 테이블의 특정 컬럼에 걸려있는 제약 조건의 확인 조인상 2010.05.12 8006
37 테이블 복구 사례 조인상 2010.05.12 6860
36 Oracle Flashback 복구 조인상 2010.05.12 8020
서버에 요청 중입니다. 잠시만 기다려 주십시오...