DB 메모리

Tibero 데이터베이스 메모리 정보에 대하여 설명합니다.

데이터베이스 버퍼 Cache 적중률

데이터베이스 버퍼 Cache의 적중률을 점검합니다.

데이터베이스 버퍼의 적중률이란 사용자가 액세스한 메모리 Block 가운데 이미 Cache가 되어있어 물리적 I/O 없이 액세스 할 수 있는 Block의 비율을 나타냅니다.

만약 데이터베이스 버퍼의 적중률이 권장값 미만일 경우에는 할당된 데이터베이스 버퍼의 크기가 너무 적거나 지나치게 많은 I/O를 유발하는 애플리케이션이 존재한다는 것을 의미합니다.

Single DB

다음은 Single DB 쿼리 작성 예입니다.

SELECT pr1.value + pr2.value "Physical read", 
       bg1.value+bg2.value+bg3.value+bg4.value+bg5.value "Logical read" , 
       TRUNC((1-((pr1.value+pr2.value)/ (bg1.value+bg2.value+bg3.value
       +bg4.value+bg5.value)) ) *100, 2) "hit(MORE THAN 60-70%)"
FROM   v$sysstat pr1,
       v$sysstat pr2, 
       v$sysstat bg1 , 
       v$sysstat bg2 , 
       v$sysstat bg3 , 
       v$sysstat bg4 , 
       v$sysstat bg5
WHERE  pr1.name = 'block disk read'
       and pr2.name = 'multi block disk read - blocks' 
       and bg1.name = 'consistent block gets'
       and bg2.name = 'consistent multi gets - blocks'
       and bg3.name = 'block gets (CRX)' 
       and bg4.name = 'current block gets'
       and bg5.name = 'current block gets - no wait';

결과는 아래와 같습니다.

Physical read   Logical read   hit(MORE THAN 60-70%)
-------------   -------------  -----------------------
3300                 12233287                  99.97

1 row selected.

각 항목에 대한 설명은 아래의 "TAC DB" 내용을 참고합니다.

TAC DB

다음은 TAC DB 쿼리 작성 예입니다.

SELECT  ins.inst_id,
        pr1.value + pr2.value "Physical read", 
        bg1.value+bg2.value+bg3.value+bg4.value+bg5.value "Logical read" , 
        TRUNC((1-((pr1.value+pr2.value)/ 
        (bg1.value+bg2.value+bg3.value+bg4.value+bg5.value)) ) *100, 2) 
        "hit(MORE THAN 60-70%)"
FROM    (select distinct inst_id 
         from gv$sysstat
         ) ins,
         gv$sysstat pr1, 
         gv$sysstat pr2, 
         gv$sysstat bg1, 
         gv$sysstat bg2, 
         gv$sysstat bg3, 
         gv$sysstat bg4, 
         gv$sysstat bg5
WHERE pr1.name = 'block disk read'
         and pr2.name = 'multi block disk read - blocks' 
         and bg1.name = 'consistent block gets'
         and bg2.name = 'consistent multi gets - blocks' 
         and bg3.name = 'block gets (CRX)'
         and bg4.name = 'current block gets'
         and bg5.name = 'current block gets - no wait' 
         and pr1.inst_id = ins.inst_id
         and pr2.inst_id = ins.inst_id 
         and bg1.inst_id = ins.inst_id 
         and bg2.inst_id = ins.inst_id 
         and bg3.inst_id = ins.inst_id 
         and bg4.inst_id = ins.inst_id 
         and bg5.inst_id = ins.inst_id

결과는 아래와 같습니다.

INST_ID     Physical read    Logical read     hit(MORE THAN 60-70%)
--------    -------------    -------------    ----------------------
101                   556             4799                    88.41         
102                   415             5621                    92.61

2 rows selected.

각 항목에 대한 설명은 다음과 같습니다.

항목	설명
INST_ID	Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
Physical read	데이터 Block을 디스크에서 읽어오는 횟수
Logical read	데이터 Block을 버퍼 Cache에 요청한 횟수
hit	Hit 버퍼 캐시의 적중률 계산법: ( 1 - physical read / logical read) * 100

데이터베이스 버퍼의 적중률은 OLTP, DSS, DW 등 데이터베이스를 사용하는 애플리케이션의 특성에 따라 권장값 이 다릅니다. 일반적으로 OLTP 시스템은 90% 이상이 좋고, DSS나 DW 시스템은 80~85% 이상이면 좋다고 할 수 있으나 순수하게 Batch 처리업무 위주의 시스템인 경우는 50% 아래라도 문제가 된다고 단정할 순 없습니다.

데이터베이스 버퍼의 적중률이 기준값보다 낮은 경우에는 O/S상 메모리 여유량을 확인하여 메모리 부족으로 인한 Paging, Swapping이 발생하지 않는 한도 내에서 tip 파일의 DB_CACHE_SIZE값을 늘려 데이터베이스 버퍼의 크기를 증가시킬 수 있습니다. 그러나 현실적으로 디스크상의 모든 데이터 Block을 Cache하는 것은 불가능하므로 일반적으 로 데이터베이스 버퍼의 증가가 최선의 해결책이 될 수는 없습니다.

특히, 다량의 디스크 Block을 빈번히 Full Scan하는 애플리케이션이 존재한다면 적중률을 높은 수준으로 유지하는 것이 불가능하므로 액세스 패턴이 비효율적인 애플리케이션을 추출하여 지속적인 튜닝을 수행해야 합니다.

---

SQL Cache 적중률

SQL Cache 적중률을 확인합니다.

Single DB

다음은 Single DB 쿼리 작성 예입니다.

SELECT namespace, 
       gets, 
       gethits, 
       gethitratio, 
       pins, 
       pinhits, 
       pinhitratio
FROM v$LIBRARYCACHE
WHERE namespace = 'SQL AREA';

결과는 아래와 같습니다.

NAMESPACE      GETS     GETHITS     GETHITRATIO  PINS       PINHITS     PINHITRATIO
----------    ------    --------    -----------  -------    --------    ------------
SQL AREA      554000    550909      99.44        1101207     1101207            100

1 row selected.

각 항목에 대한 설명은 아래의 ‘TAC DB’ 내용을 참고합니다.

TAC DB

다음은 TAC DB 쿼리 작성 예입니다.

SELECT  inst_id,
        namespace, 
        gets, 
        gethits, 
        gethitratio, 
        pins, 
        pinhits, 
        pinhitratio
FROM GV$LIBRARYCACHE
WHERE namespace = 'SQL AREA'

결과는 아래와 같습니다.

INST_ID  NAMESPACE  GETS  GETHITS  GETHITRATIO  PINS  PINHITS  PINHITRATIO
-------  ---------  ----- -------- -----------  ----- -------- -------------
   101    SQL AREA    183     110	  60.1    112	   112		 100
   102    SQL AREA    137      83         60.58    84	    84		 100

2 rows selected.

각 항목에 대한 설명은 다음과 같습니다.

항목	설명
INST_ID	Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
NAMESPACE	Library Cache, DD Cache의 항목 (SQL AREA : Library Cache)
GETS	Cache의 총 접근 횟수
GETHITS	Cache의 적중 횟수
GETHITRATIO	Cache의 적중률 계산법: (PINHITS / PINS) * 100
PINS	Cache에 있는 객체에 대한 요청을 PIN 한 횟수
PINHITS	Cache에 이미 PIN 되어진 객체의 횟수
PINHITRATIO	PIN 수로 나눈 PINHITS 수의 비율로 1에 가까운 값은 객체 시스템의 대부분이 PIN을 시도하고 액세스 Cache 된 것 나타냄

데이터 Dictionary Cache의 적중률은 90% 이상 유지할 것을 권장합니다.

적중률이 기준값 이하일 때 초기화 파라미터 TOTAL_SIZE와 DB_CACHE_SIZE의 비율을 조정하여 공유 Cache의 크기를 증가시켜 적중률을 높일 수 있습니다.

---

Dictionary Cache 적중률

Dictionary Cache 적중률을 확인합니다.

Single DB

다음은 Single DB 쿼리 작성 예입니다.

SELECT  TO_CHAR(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Current Time", 
        ROUND( ( sum(hit_cnt) - sum(miss_cnt) ) / sum(hit_cnt) * 100, 1) 
        "Dictionary Cache Hit Ratio(%)"
FROM v$rowcache;

결과는 아래와 같습니다.

Current Time                 Dictionary Cache Hit Ratio(%)
----------------------       -------------------------------
   2013/05/16 17:34:22                                  99.9
   
1 row selected.

각 항목에 대한 설명은 아래의 "TAC DB" 내용을 참고합니다.

TAC DB

다음은 TAC DB 쿼리 작성 예입니다.

SELECT  INST_ID,
        TO_CHAR(sysdate, 'yyyy/mm/dd hh24:mi:ss') "Current Time",
        ROUND( ( sum(hit_cnt) - sum(miss_cnt) ) / sum(hit_cnt) * 100, 1) 
        "Dictionary Cache Hit Ratio(%)"
FROM gv$rowcache 
GROUP BY INST_ID;

결과는 아래와 같습니다.

INST_ID              Current Time         Dictionary Cache Hit Ratio(%)
-----------   --------------------       --------------------------------
        101    2013/05/16 17:42:06                                95.5
        102    2013/05/16 17:42:06                                97.3

2 rows selected.

각 항목에 대한 설명은 다음과 같습니다.

항목	설명
INST_ID	Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
Current Time	해당 SQL을 수행한 시간
Hit Ratio	데이터 Dictionary Cache의 적중률

데이터 Dictionary Cache는 데이터 Dictionary 정보를 Cache하는 영역으로 공유 Cache에서 할당합니다. Dictionary Cache의 크기는 사용자가 따로 지정할 수 없습니다. Tibero는 공유 Cache 가운데 Dictionary Cache를 우선적으로 할당 하므로 라이브러리 Cache의 적중률이 양호하다면 Dictionary Cache의 적중률도 양호합니다.

Dictionary Cache 적중률은 인스턴스가 기동한 후 어느정도 시간이 지난 후에야 의미가 있으며 적중률을 90%이상 유지할 것을 권장합니다. 적중률이 기준값 이하일 때 초기화 파라미터 TOTAL_SHM_SIZE와 DB_CACHE_SIZE의 비율 을 조정하여 공유 Cache의 크기를 증가시켜 적중률을 높일 수 있습니다.

---

공유 Cache의 Free 메모리

공유 Cache의 Free 메모리 크기를 확인합니다.

Single DB

다음은 Single DB 쿼리 작성 예입니다.

SELECT round(total/1024/1024, 1) "Shared Cache Total (MB)", 
       round(used/1024/1024, 1) "Used (MB)",
       round((total - used)/1024/1024, 1) "free (MB)"
FROM v$sga
WHERE name='SHARED POOL MEMORY';

결과는 아래와 같습니다.

Shared Cache Total (MB)     Used (MB)     free (MB)
-----------------------   -----------    ---------------
                475.2            4.5               470.7
                
1 row selected.

각 항목에 대한 설명은 아래의 "TAC DB" 내용을 참고합니다.

TAC DB

다음은 TAC DB 쿼리 작성 예입니다.

SELECT  inst_id,
        round(total/1024/1024, 1) "Shared Cache Total (MB)", 
        round(used/1024/1024, 1) "Used (MB)",
        round((total - used)/1024/1024, 1) "free (MB)" 
FROM gv$sga
WHERE name='SHARED POOL MEMORY';

결과는 아래와 같습니다.

    INST_ID     Shared Cache Total (MB)     Used (MB)     free (MB)
-----------     -----------------------     ----------    ----------
        101                      2704.6          75.2         2629.4
        102                      2704.6          74.3         2630.3

2 rows selected.

각 항목에 대한 설명은 다음과 같습니다.

항목	설명
INST_ID	Tibero 인스턴스 정보 (TAC DB 질의 쿼리)
Shared Cache Total	전체 공유 Cache의 메모리 사이즈
Used	현재 사용 중인 메모리의 양
free	가용한 메모리의 사용량

공유 Cache 영역은 라이브러리 Cache, Dictionary Cache로 구성됩니다. 공유 Cache는 기본적으로 Cache를 위한 영 역이므로 여유공간이 남아있는한 기존 오브젝트에 대해 Cache에 두고 계속 소모하려는 속성이 있습니다. 따라서 라이브러리 Cache나 Dictionary Cache의 적중률이 양호하다면 일반적인 운영 시간대에 Free 메모리 크기가 낮은 상태를 유지하는 것은 정상입니다.

항상 큰 Free 메모리가 남아 있다면 공유 Cache가 불필요하게 크게 설정되어 있다는 것을 의미하며 일시적으로 갑 자기 Free 메모리 크기가 증가하는 경우는 Fragmentation이 많이 발생하여 많은 오브젝트가 한꺼번에 Flush된 것 을 의미하므로 문제를 겪고 있다는 증상일 수 있습니다.

공유 Cache 메모리 Fragmentation을 예방하기 위해 사이즈가 큰 PSM Block 사용을 자제하고 Literal SQL 문의 경우 바인드 변수를 사용하도록 수정합니다.