Library Cache Hit Ratio

[NEXERS]

같이 보아야 할 문서  :  http://blog.naver.com/kiyoun82/110068847853

[Oracle] Hit Ratio 확인   

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

1. Buffer Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0))/
(SUM(DECODE(name, 'db block gets', value,0))+ 
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio" 
FROM V$SYSSTAT; 
================================================================================== 
2. Library Cache Hit Ratio
SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio"
From V$LIBRARYCACHE;
 ==================================================================================
3. Data Dictionary Cache Hit Ratio
SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio"
FROM V$ROWCACHE;
 ==================================================================================
=========================

오라클 메모리 구조와 튜닝
=========================

일단 오라클 메모리 구조는 오라클 소프트웨어 코드 부분, SGA(System 
Global Area), PGA(Program Global Area ) , Sort area 로 나뉘어 진다. 
이중 SGA 부분은 데이타베이스 버퍼캐쉬, 리두 로그버퍼, Shared Pool 로 
나뉘고 오라클 인스턴스에 대한 데이터와 컨트롤 정보를 저장하기 
위해서 할당된 메모리 세그먼트이다.
이중 SQL 문이 저장되는 Shared Pool은 라이브러리 캐쉬와 딕셔너리 캐쉬로 
나누어진다.
초기화 파라미터 파일의 DB_BLOCK_BUFFERS (데이터베이스 버퍼 
캐쉬의 버퍼 수에 대한 파라미터)나 SHARED_POOL_SIZE (공유 SQL 
영역에 할당된 바이트수)같은 파라미터들이 이 SGA의 크기를 결정해 준다. 
데이타베이스 버퍼캐쉬는 실제 데이타가 올려지는 부분인데 
DB_BLOCK_BUFFERS의 값에 기초하여 SGA에 할당되는 메모리의 양이며 
이는 사용하는 오라클의 블록 크기에 의해서 결정된다. 예를 들어서 블럭 
크기가 2K로 이런 버퍼에 32,768바이트가 할당될 때, 만일 블럭 크기를 
4K로 늘이는 경우, 초기화 파라미터 파일에서 DB_BLOCK_BUFFER값을 
조절하지 않아도 데이터베이스 버퍼의 메모리 요구량은 65,536바이트로 
증가할 것이다.

1. 라이브러리 캐쉬 : 이 캐쉬는 Private SQL 영역과 Shared SQL 영역,
PL/SQL 영역으로 나뉘며 수행되어야 할 SQL 문장이 이부분에 올라와 
parse되고 실행가능한 SQL 문장을 저장한다. 
모든 SQL 문장을 위한 SGA에서의 공유 부분은 각 문장의 파스트리와 
실행 계획을 포함하는 메모리양이다. 
여기서 파스 트리란 SQL 문장을 파싱한 결과를 나타내는 트리이고 실행 
계획은 문장이 수행될 방법에 대한 계획을 포함하는 로드맵이다. 
private 부분은 SQL문장과 연관된 모든 커서에 대해 SGA에서 차지하는 
지속적인 부분과, SQL 문장이 수행될 때 할당받고 문장이 완료되었을 때 
해제되는 실행시간 부분으로 나뉜다.
SGA에 할당된 공간을 효율적으로 사용하기 위해서 커서의 실행시간 부분에 
할당되었던 메모리를 해제하기 위해 커서의 수행이 완료되었을 때는 반드시 
닫아야 한다. 
SGA를 튜닝하는데 있어서 중요한 점은 오라클이 shared pool에 파싱된 실행 
가능한 문장들을 저장할 수 있도록 라이브러리 캐쉬가 충분한지 확인하는 
것이며 현재 설정된 크기에서의 라이브러리 캐쉬의 성능을 계산하기 위해 
v$librarycache dictionary 뷰를 보아야 한다.

SELECT SUM(pins) "Exexution"
SUM (reloads) "Cache Misses while Executing"
From V$LIBRARYCACHE;

이 뷰의PIN은 캐쉬의 히트(메모리에서 읽기)를 나타내고 RELOAD는 
캐쉬의 미스(디스크에서 읽기)를 나타낸다. PINS 컬럼과 RELOAD컬럼에 
있는 값과 이 동적 성능 테이블에서의 두 값의 비율은 현재의 크기가 
SGA에서 라이브러리 캐쉬의 최적 크기인지를 나타내주는 좋은 척도가 되며 
두 값의 비율은 0에 가까운 것이 좋다. 1%를 넘는 비율은 캐쉬의 히트 
비율이 증가되어야한다는 것을 나타낸다. 이 히트 비율의 증가는 공유 저장 
풀의 크기를 증가하면 된다.

2. 딕셔너리 캐쉬: 딕셔너리 캐쉬는 데이터베이스에서 세그먼트에 부속된 
데이터 딕셔너리 정보(즉, 인덱스, 시퀀스, 테이블)와 파일 공간의 가용도
(오브젝트 생성과 확장을 위해 필요한 공간의 획득), 오브젝트 권한을 
저장하고 있다. 새롭게 요구되는 정보에 대해서 공간이 요구되면 딕셔너리 
정보는 이 캐쉬로 옮겨진다. 딕셔너리 캐쉬의 항목에 대한 히트 비율을 
살펴보면 이 캐쉬의 효율성에 대한 아이디어를 얻을수 있다. 
미스 비율은 미스된 횟수를 미스된 횟수와 히트된 횟수의 합으로 나눈 
퍼센트 값으로 계산된다.

제대로 튜닝된 데이터베이스라면 dictionary 캐쉬의 평균 히트 비율이 90%를 
넘어야 한다.

이의 튜닝은 V$ROWCACHE를 참조 하여야 한다.
SELECT parameter,gets,getmisses
FROM V$ROWCACHE; 또는

SELECT SUM(gets) "Data Dictionary Gets"
SUM(getmisses) "Data Dictionary Cache Get Misses"
FROM V$ROWCACHE; 를 실행하여 getmisses 비율이 10-15%를 넘지 
않아야 한다.

3. 데이터베이스 버퍼 캐쉬: 오라클은 연산 수행중에 데이터베이스 버퍼 
캐쉬에 데이터를 읽고 쓸 필요가 있다. 캐쉬 히트(cash hit)란 필요한 
정보가 이미 메모리에 있는 것을 말하고 캐쉬 미스(cach miss)란 오라클이 
반드시 데이터 요구를 충족시켜주기 위해 디스크 입/출력을 실행하여야 
하는 것을 말한다. 데이터베이스 버퍼 캐쉬의 크기를 결정할 때 캐쉬 
미스를 최소로 유지하는 것이 중요하다. 초기화 파라미터 파일 항목
DB_BLOCK_BUFFERS은 데이터베이스 블록 버퍼 캐쉬의 크기를 콘트롤한다

데이타베이스 버퍼캐쉬의튜닝을 위해서는 V$SYSSTAT를 참조하여야 한다.

SELECT name,value
FROM V$SYSSTAT
WHERE name in (*db block gets*, *consistent gets*,physical reads*); 
를 실행한 값에 의해
hit ratio = 1- physical reads / (db block gets + consistent gets) 로 
구한 값이 70-80%아래이면 DB_BLOCK_BUFFERS를 늘려주어야 한다.

DB_BLOCK_LRU_EXTENDED_STATISTICS 파라미터가 0이 아닌 값을 갖도록 하여 
버퍼를 추가하거나 제거했을 때 히트 값이 증가되는 값을 알아볼수 있다. 
예를 들어, 1,500이란 값은 데이터베이스 버퍼풀 내에서 추가되거나 
제거되는 버퍼를 1,500개까지 알 수 있다. 
x$kcbrbh 테이블은 버퍼 크기 파라미터의 증가에 대한 정보를, x$kcbcbh 
테이블은 파라미터의 감소에 대한 정보를 제공한다.

다음은 x$kcbrbh에 쿼리하는 데이터베이스 블록 버퍼 캐쉬에 버퍼를 
추가했을 때의 효과를 보여준다.

select 250*trunc (indx/250)+1||
'to ' ||250*(trunc(undx/250)+1) "Interval",
sum (count) "Buffer Cace Hits"
from sys.x$kcbrbh
group by trunc (indx/250) ;

이 쿼리를 실행한 결과는 다음과 같다.

NUMBER OF BUFFERS CHANGE IN CACHE HITS
1 to 5000 1,205
501 to 1,000 1,098
1,001 to 1,500 6,790
결과를 위로부터 아래로 읽으면 버퍼 캐쉬에 500개의 버퍼를 추가했을 때 
추가로 얻어지는 캐쉬 히트는 1,205이다 1,000개를 추가하면, 2,303 
(1,205+1,098)개가 증가하며, 1,500 버퍼를 추가하면 7,093
(1,205+1.098+6,790)만큼이 증가한다.
다음의 코드는 버퍼가 버퍼 캐쉬에서 제거됐을 때 캐쉬 히트가 얼마나 
감소하는 지 보여준다. 
select 25*trunc (indx/25)+1||
'to'||25*(trunc(indx/25)+1) "Interval",
sum (count) "Buffer Cache Hits"
from sys.x$kcbcbh
group by trunc (indx/25)
결과는 다음과 같다.
NUMBERS OF BUFFERS CHANGE IN CACHE HITS
1 to 25 3203
26 to 50 56
51 to 75 17
이번에는 아래에서 위로 읽으면 마지막 25개의 버퍼(51에서 75)가 제거되면 
17 개의 캐쉬 히트를 손실하고, 마지막 50개의 버퍼를 제거하면 73(17+56)
만큼 손실된다. 또 처음 25개의 버퍼가 3,276(17+56+3203) 캐쉬 히트에 
대한 책임이 있음을 알 수 있다.

x$kcbrbh 와 x$kcbcbh 쿼리의 결과를 관찰한 후, 초기화 파라미터 파일을 
편집해서 DB_BLOCK_BUFFERS 항목을 변경해야 함을 알 수 있을 것이다. 
이 파라미터의 조절은 인스턴스를 셧다운하고 다시 시작해야 변경한 값의 
효력이 있다.

4. 리두 로그 버퍼 캐쉬: 리두로그 버퍼 캐쉬는 온라인 리두 로그의 정보를 저장한다. 
LGWR 백그라운드 프로세스는 새로운 리두항목에 대해 이 캐쉬에서의 
공간이 항상 사용가능하여야 효율적으로 기록할 수 있다. 리두로그 버퍼는 
공유 메모리 세그먼트이므로 오라클은 래치를 사용해서 이 버퍼 캐쉬를 
관리하며 래치는 메모리의 lock이다. 리두 할당 래치(redo allocation latch)
는 리두로그 버퍼 캐쉬에서 공간에 대한 요구를 관리하며 이 래치를 
사용하여 복사될 수 있는 리두항목의 최대 크기는 초기화 파라미터 
파일의 LOG_SMALL_ENTRY_MAX_SIZE에 의해서 결정된다. 만일 복사되는 정보가 
이 파라미터 값보다 크다면 리두 카피래취 (redo copy latch)에 저장된다. 
즉 멀티 CPU시스템에서, 
이 최대 값을 넘는 리두 항목들은 리두 복사래치에 의해 복사되는 동안 
복사되고 보호된다. 단일 CPU시스템에서 모든 항목들은 리두 할당 래치를 
사용해서 복사된다. 
튜닝 프로세스에서는 리두로그 버퍼에서 정보가 나가고 들어오는 것과 같은 
정보 이동에 있어서 대기(wait)조건이 일어나지 않도록 하는 것이 
중요하다. 이는 v$latch 성능 테이블을 사용해서 알수 있다. 전체 얻을 
것에 대해 미스가 1%보다 크면 리두할당이나 리두복사 래치에 대해서 경쟁이 
일어나게 된다. 이 경쟁을 없애거나 줄이는 것이 메모리 튜닝 프로세스의 
일부분이다.

SELECT name,gets,misses,immediate_gets,sleeps
FROM V$LATCH 
WHERE name IN (redo allocation , redo copy);

결과치의 값에 의해 Get에 대한 Miss 비율이 1%를 넘지 않도록하고,
(immediate_gets + immediate_misses)에 대한 immediate_misses의 비율이 1% 
를 넘지 않도록 튜닝하여야 한다. 일반적으로 멀티 프로세서 시스템에서 
초기화 파라미터 파일 항목 LOG_SIMULTANEOUS_COPIES는 CPU 갯수의 두 배값
을 설정한다. 이렇게 하는 것이 redo 복사 래치에서의 잠재적인 경쟁을 
줄이는 데 도움이 될 것이다. 

[출처] [Oracle] Hit Ratio 확인 |작성자 라이더