dba_hist_librarycache

The dba_hist_librarycache view contains the statistical history for library cache activity. This view stores snapshots for the v$librarycache dynamic view. The library cache stores SQL cursors, Java classes, and PL/SQL programs in executable form. If library cache contention is significant, this view can be queried to get more details about particular library objects that may cause such a contention. These details will give users hints about ways to potentially reduce library cache contention.

 

In order to understand the importance of library cache tuning, users should always be aware that a library and dictionary cache miss is more expensive in terms of resources than data buffer miss because it involves a significant amount of CPU work.

 

SQL> desc DBA_HIST_LIBRARYCACHE

 

Name              Null?    Type

----------------- -------- ------------

SNAP_ID           NOT NULL NUMBER

DBID              NOT NULL NUMBER

INSTANCE_NUMBER   NOT NULL NUMBER

NAMESPACE         NOT NULL VARCHAR2(15)

GETS                       NUMBER

GETHITS                    NUMBER

PINS                       NUMBER

PINHITS                    NUMBER

RELOADS                    NUMBER

INVALIDATIONS              NUMBER

DLM_LOCK_REQUESTS          NUMBER

DLM_PIN_REQUESTS           NUMBER

DLM_PIN_RELEASES           NUMBER

DLM_INVALIDATION_          NUMBER

REQUESTS

DLM_INVALIDATIONS          NUMBER

 

The lib_cache_int_10g.sql query can be used to get a report for library cache statistics.

 

 

 

SEE CODE DEPOT FOR FULL SCRIPTS

 

The following is a possible result of this query.

 

SQL> @ Lib_cache_int_10g.sql

 

Name Space      Get Requests Get Pct Miss Pin Requests Pin Pct Miss    Reloads Invalidations

--------------- ------------ ------------ ------------ ------------ ---------- ------

BODY                    1840   5,76086957         3117   4,39525184         24      0

CLUSTER                  216   2,31481481          532   1,12781955          1      0

INDEX                     37   97,2972973           41    87,804878          0      0

JAVA DATA                  3   33,3333333            5           40          0      0

JAVA RESOURCE              0                         0                       0      0

JAVA SOURCE                0                         0                       0      0

OBJECT                     0                         0                       0      0

PIPE                       0                         0                       0      0

SQL AREA               31706     7,459156       120148   2,84482472        495     60

TABLE/PROCEDURE        13926   17,6576188        83460    5,5415768        425      0

TRIGGER                  119   14,2857143          488   3,89344262          2      0

 

The report shows what particular types of library cache contents have the highest miss percentage. This means that these objects require additional work to reload them back into library cache, thereby causing CPU overhead.

 

The Ion tool has a report named Library Cache, represented in Figure 4.11, which generates charts against the dba_hist_librarycache view.

 

 

The above Ion screenshot displays a sample chart produced for particular types of library cache objects. This chart shows an activity history of how the database requested particular types of objects during the selected snapshot interval.

 

SEE CODE DEPOT FOR FULL SCRIPTS