Again the same story : Oracle provides a useful view to check librarycache statistics (v$librarycache), but this view is a cumulative one. So how to check what’s is going on my database right now with cumulative values ?

Right : You have to substract the values between 2 measures.

So to get real-time librarycache statistics, you can use the librarycache.pl script (click on the link and then on the view source button to copy/paste the source code) that basically takes a snapshot based on the gv$librarycache view each second (default interval) and computes the differences with the previous snapshot.

Let’s see an example:

./librarycache.pl

15:20:30   INST_NAME    NAMESPACE   RELOADS INVALIDATIONS   GETS    GETRATIO    PINS    PINRATIO
15:20:30   BDT          TRIGGER         0   0           1   100.0           10  100.0
15:20:30   BDT          TABLE/PROCEDURE 0   0           2   100.0           46  100.0
15:20:30   BDT          BODY            0   0           5   100.0           20  100.0
15:20:30   BDT          SQL AREA    0   0           16  88.9            213 100.0
--------------------------------------> NEW
15:20:31   INST_NAME    NAMESPACE   RELOADS INVALIDATIONS   GETS    GETRATIO    PINS    PINRATIO
15:20:31   BDT          TABLE/PROCEDURE 0   0           1   100.0           24  100.0
15:20:31   BDT          TRIGGER         0   0           1   100.0           10  100.0
15:20:31   BDT          PIPE            0   0           1   100.0           1   100.0
15:20:31   BDT          SQL AREA    0   0           12  92.3            162 100.0

So, as you can see 12 gets occured on the SQL AREA during the last second without invalidations or reloads. The output is sorted on the GETS column but you could choose to sort on another one.

Let’s see the help:

./librarycache.pl help

Usage: ./librarycache.pl [Interval [Count]] [inst=] [top=] [namespace=] [sort_field=]
        Default Interval : 1 second.
        Default Count    : Unlimited

  Parameter      Comment                                                           Default
  ---------      -------                                                           -------
  INST=          ALL - Show all Instance(s)                                        ALL
                 CURRENT - Show Current Instance
                 INSTANCE_NAME,... - choose Instance(s) to display

                    << Instances are only displayed in a RAC DB >>

  TOP=           Number of rows to display                                         10
  NAMESPACE=     ALL - Show all NAMESPACE (wildcard allowed)                       ALL
  SORT_FIELD=    RELOADS|INVALIDATIONS|GETS|PINS                                   GETS

Example : ./librarycache.pl
Example : ./librarycache.pl sort_field='PINS'
Example : ./librarycache.pl namespace='%TRI%'

As usual:

  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • You can choose to filter on namespace (by default no filter is applied).
  • You can choose the column to sort the output on.
  • This script is oracle RAC aware : you can work on all the instances, a subset or the local one.
  • You have to set oraenv on one instance of the database you want to diagnose first.
  • The script has been tested on Linux, Unix and Windows.