5 Replies Latest reply: Jul 28, 2013 5:22 PM by sb92075 RSS

    V$LIBRARYCACHE

    asif.maqbool

      Hello, 

       

      According to the different rsource of Oracle, I got some information about V$LIBRARYCACHE's columns information those are:

      Each time a statment parse, the value of GETS column will increment by 1 (one).

      Each time a statement found parsed code in the librarycahce GETHITS column will increment by 1. If didn't find, will not increment.

      Each time a statment execute the value of PINS column will increment by 1.

       

      Before issuing a command, there was the  v$librarychace view content:

       

      SQL> select namespace,gets,gethits,pins,pinhits from v$librarycache where namespace like 'TABLE%';

      NAMESPACE             GETS    GETHITS       PINS    PINHITS
      --------------- ---------- ---------- ---------- ----------
      TABLE/PROCEDURE       4860       4003       1320        428

       

      First Time issued this command:

      SQL> select * from dept where deptno=30;

          DEPTNO DNAME          LOC
      ---------- -------------- -------------
              30 Marketing      Toronto

       

      then v$librarycache content:

      SQL> select namespace,gets,gethits,pins,pinhits from v$librarycache where namespace like 'TABLE%';

      NAMESPACE             GETS    GETHITS       PINS    PINHITS
      --------------- ---------- ---------- ---------- ----------
      TABLE/PROCEDURE       4861       4004       1322        430

       

      Q1:     Why the gethits & pinhits values changed?

      Q2:     Why did the increment more than 1 in Pins?

       

      Second time issued same command:

      SQL> select * from dept where deptno=30;

          DEPTNO DNAME          LOC
      ---------- -------------- -------------
              30 Marketing      Toronto

       

      then v$librarycache content:

      SQL> select namespace,gets,gethits,pins,pinhits from v$librarycache where namespace like 'TABLE%';

      NAMESPACE             GETS    GETHITS       PINS    PINHITS
      --------------- ---------- ---------- ---------- ----------
      TABLE/PROCEDURE       4861       4004       1323        431

       

      Q3. Why didn not change the gethits value?

       

      Third time issued same command:

      SQL> select * from dept where deptno=30;

          DEPTNO DNAME          LOC
      ---------- -------------- -------------
              30 Marketing      Toronto

       

      then v$librarycache content:

      SQL> select namespace,gets,gethits,pins,pinhits from v$librarycache where namespace like 'TABLE%';

      NAMESPACE             GETS    GETHITS       PINS    PINHITS
      --------------- ---------- ---------- ---------- ----------
      TABLE/PROCEDURE       4861       4004       1323        431

      Q4: Why did not change the value of pins?

       

      All those question, I asked according to my knowledge & concept which got from different resources including OTN forum.

       

      If there is any thing wrong in my concept, please correct me.

       

      Thanks.

      Asif