[오라클] Library Cache (v$librarycache table)

Library cache
▶ V$LIBRARYCACHE Table
latest 인스턴스 시작 이후에 발생하는 모든 Library cache actibity를 표시
Library cache 행동 감시
사용자SYS와 SELECT ANY TABLE의 시스템권한을 가진 SYSTEM과 같은 사용자만 이용 가능.
NAMESPACE column : SQL 문장과 PL/SQL 블록들을 위한 Library cache actibity
                                 ('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER')
PINS : Library cache 내 실행된 항목 수
RELOADS : 실행 단계의 Library cache miss 수
Query ①
SQL> select sum(pins) "Executions", sum(reloads) "Cache misses while executing"
  2  from v$librarycache;
Executions Cache misses while executing
---------- ----------------------------
   1534122                           24
↘ Query ②
SQL> select to_char(trunc(sum(reloads)/sum(pins) * 100, 5), 99.99999) ||
  2  '% (less than 1%)' "Library cache miss ratio"
  3  from v$librarycache;
Library cache miss ratio
-------------------------
  .00156% (less than 1%)

SQL> select namespace name, gets, gethits,round(gethitratio*100,2) "GetHit Percentage",
  2  Pins, pinhits, round(pinhitratio*100,2) "PinHit Percentage", reloads, invalidations
  3  from v$librarycache
  4  order by 1 ;
NAME                  GETS    GETHITS GetHit Percentage       PINS    PINHITS
--------------- ---------- ---------- ----------------- ---------- ----------
PinHit Percentage    RELOADS INVALIDATIONS
----------------- ---------- -------------
BODY                    20          8                40         19          8
            42.11          0             0
CLUSTER                558        550             98.57        434        424
             97.7          0             0
INDEX               116392     116350             99.96     116361     116319
            99.96          0             0

NAME                  GETS    GETHITS GetHit Percentage       PINS    PINHITS
--------------- ---------- ---------- ----------------- ---------- ----------
PinHit Percentage    RELOADS INVALIDATIONS
----------------- ---------- -------------
JAVA DATA                0          0               100          0          0
              100          0             0
JAVA RESOURCE            0          0               100          0          0
              100          0             0
JAVA SOURCE              0          0               100          0          0
              100          0             0

NAME                  GETS    GETHITS GetHit Percentage       PINS    PINHITS
--------------- ---------- ---------- ----------------- ---------- ----------
PinHit Percentage    RELOADS INVALIDATIONS
----------------- ---------- -------------
OBJECT                   0          0               100          0          0
              100          0             0
PIPE                     0          0               100          0          0
              100          0             0
SQL AREA            343944     343502             99.87    1058533    1057735
            99.92         24             1

NAME                  GETS    GETHITS GetHit Percentage       PINS    PINHITS
--------------- ---------- ---------- ----------------- ---------- ----------
PinHit Percentage    RELOADS INVALIDATIONS
----------------- ---------- -------------
TABLE/PROCEDURE     367737     367360              99.9     367952     367440
            99.86          0             0
TRIGGER                 24         21              87.5         24         21
             87.5          0             0


11 rows selected.