shared pool的深入剖析（三）

基本命令：

ALTER SESSION SET EVENTS 'immediate trace name library_cache level n';

其中 n 代表Level级别,对于9.2.0及以后版本,对于library_cache不同Level含义如下:
Level =1 ,转储Library cache统计信息
Level =2 ,转储hash table概要
Level =4 ,转储Library cache对象，只包含基本信息
Level =8 ,转储Library cache对象，包含详细信息(包括child references,pin waiters等)
Level =16,增加heap sizes信息
Level =32,增加heap信息

Library cache由一个hash表组成,而hash表是一个由hash buckets(buckets即list或者array)组成的数组.

每个hash bucket都是由多个library cache handle组成的一个双向链表。
Library Cache Handle指向Library Cache Object和一个引用列表。
Library Cache Object进一步分为:依赖表、子表和授权表等。

我们看一下library cache的结构:

1）Level =1 ,转储Library cache统计信息
SQL> alter session set events 'immediate trace name library_cache level 1';
Session altered.
SQL> select spid from v$session s inner join v$process p on s.paddr=p.addr
        inner join v$mystat m on s.sid=m.sid and m.statistic#=0;
SPID
------------
27905
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@redhat4 ~]$ cd $ORACLE_BASE
[oracle@redhat4 oracle]$ cd admin/jiagulun/udump/
[oracle@redhat4 udump]$ ls *27905*
jiagulun_ora_27905.trc
[oracle@redhat4 udump]$ cat jiagulun_ora_27905.trc
/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_27905.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      redhat4
Release:        2.6.9-89.ELsmp
Version:        #1 SMP Mon Apr 20 10:34:33 EDT 2009
Machine:        i686
Instance name: jiagulun
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 27905, image: oracle@redhat4 (TNS V1-V3)

*** SERVICE NAME:() 2012-09-28 15:10:44.701
*** SESSION ID:(159.3) 2012-09-28 15:10:44.701
Thread 1 checkpoint: logseq 14, block 2, scn 760339
  cache-low rba: logseq 14, block 50459
    on-disk rba: logseq 14, block 50615, scn 772195
  start recovery at logseq 14, block 50459, scn 0
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 78Kb in 0.35s => 0.22 Mb/sec
Total physical reads: 4096Kb
Longest record: 9Kb, moves: 0/100 (0%)
Change moves: 2/32 (6%), moved: 0Mb
Longest LWN: 10Kb, moves: 0/41 (0%), moved: 0Mb
Last redo scn: 0x0000.000bc862 (772194)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 42/42 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 379/454 = 0.8
----------------------------------------------
*** 2012-09-28 15:10:45.059
KCRA: start recovery claims for 42 data blocks
*** 2012-09-28 15:10:45.066
KCRA: blocks processed = 42/42, claimed = 42, eliminated = 0
*** 2012-09-28 15:10:45.067
Recovery of Online Redo Log: Thread 1 Group 1 Seq 14 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 42/42 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 331/421 = 0.8
----------------------------------------------
*** 2012-09-28 15:40:50.985
LIBRARY CACHE STATISTICS:
namespace           gets hit ratio      pins hit ratio    reloads   invalids
-------------- --------- --------- --------- --------- ---------- ----------
CRSR                2767     0.366     26067     0.935        371          0
TABL               10771     0.803     96081     0.972        242          0
BODY                 329     0.906       436     0.885         19          0
TRGR                  13     0.462       116     0.922          2          0
INDX                  38     0.000        38     0.000          0          0
CLST                 134     0.933       357     0.966          3          0
KGLT                   0     0.000         0     0.000          0          0
PIPE                   0     0.000         0     0.000          0          0
LOB                    0     0.000         0     0.000          0          0
DIR                    0     0.000         0     0.000          0          0
QUEU                   6     0.500        50     0.840          2          0
OBJG                   0     0.000         0     0.000          0          0
PROP                   0     0.000         0     0.000          0          0
JVSC                   0     0.000         0     0.000          0          0
JVRE                   0     0.000         0     0.000          0          0
ROBJ                   0     0.000         0     0.000          0          0
REIP                   0     0.000         0     0.000          0          0
CPOB                   0     0.000         0     0.000          0          0
EVNT                  65     0.938        67     0.896          3          0
SUMM                   0     0.000         0     0.000          0          0
DIMN                   0     0.000         0     0.000          0          0
CTX                    0     0.000         0     0.000          0          0
OUTL                   0     0.000         0     0.000          0          0
RULS                   1     0.000        20     0.900          1          0
RMGR                   8     0.625        14     0.571          2          0
XDBS                   7     0.429         7     0.000          0          0
PPLN                   0     0.000         0     0.000          0          0
PCLS                   0     0.000         0     0.000          0          0
SUBS                   9     0.778        22     0.864          1          0
LOCS                   0     0.000         0     0.000          0          0
RMOB                   0     0.000         0     0.000          0          0
RSMD                   0     0.000         0     0.000          0          0
JVSD                   0     0.000         0     0.000          0          0
STFG                   0     0.000         0     0.000          0          0
TRANS                  0     0.000         0     0.000          0          0
RELC                   0     0.000         0     0.000          0          0
RULE                   0     0.000         0     0.000          0          0
STRM                   0     0.000         0     0.000          0          0
REVC                   2     0.500         0     0.000          0          0
STAP                   0     0.000         0     0.000          0          0
RELS                   0     0.000         0     0.000          0          0
RELD                   0     0.000         0     0.000          0          0
IFSD                   0     0.000         0     0.000          0          0
XDBC                   1     0.000         1     0.000          0          0
USAG                   0     0.000         0     0.000          0          0
MVOBTBL                0     0.000         0     0.000          0          0
JSQI                   0     0.000         0     0.000          0          0
CDC                    0     0.000         0     0.000          0          0
MVOBIND                0     0.000         0     0.000          0          0
STBO                   0     0.000         0     0.000          0          0
HTSO                   0     0.000         0     0.000          0          0
JSGA                  33     0.909       818     0.991          4          0
JSET                   7     0.000         7     0.000          0          0
TABL_T                 0     0.000         0     0.000          0          0
CLST_T                 0     0.000         0     0.000          0          0
INDX_T                 0     0.000         0     0.000          0          0
NSCPD                  0     0.000         0     0.000          0          0
JSLV                   0     0.000         0     0.000          0          0
MODL                   0     0.000         0     0.000          0          0
CUMULATIVE         14191     0.719    124101     0.963        650          0
Permanent space allocated for Load Locks
LATCH:0  TOTAL SPACE: 4092
FREELIST CHUNK COUNT:93  OBJECT SIZE:44
Permanent space allocated for KGL pins
LATCH:0  TOTAL SPACE: 32928
FREELIST CHUNK COUNT:136  OBJECT SIZE:84
Permanent space allocated for KGL locks
LATCH:0  TOTAL SPACE: 81120
FREELIST CHUNK COUNT:220  OBJECT SIZE:104
Permanent space allocated for KGL S handles
LATCH:0  TOTAL SPACE: 423756
FREELIST CHUNK COUNT:0  OBJECT SIZE:316
Permanent space allocated for KGL M handles
LATCH:0  TOTAL SPACE: 581632
FREELIST CHUNK COUNT:0  OBJECT SIZE:512
Permanent space allocated for KGL L handles
LATCH:0  TOTAL SPACE: 142080
FREELIST CHUNK COUNT:0  OBJECT SIZE:768
Permanent space allocated for KGL A handles
LATCH:0  TOTAL SPACE: 150000
FREELIST CHUNK COUNT:0  OBJECT SIZE:200
Permanent space allocated for KGL objects
LATCH:0  TOTAL SPACE: 81732
FREELIST CHUNK COUNT:2  OBJECT SIZE:84
Permanent space allocated for KGL handle dependents
LATCH:0  TOTAL SPACE: 33024
FREELIST CHUNK COUNT:91  OBJECT SIZE:12
[oracle@redhat4 udump]$
LIBRARY CACHE STATISTICS这部分信息也就是v$librarycache中显示的.
SQL> desc v$librarycache;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAMESPACE                                          VARCHAR2(15)
 GETS                                               NUMBER
 GETHITS                                            NUMBER
 GETHITRATIO                                        NUMBER
 PINS                                               NUMBER
 PINHITS                                            NUMBER
 PINHITRATIO                                        NUMBER
 RELOADS                                            NUMBER
 INVALIDATIONS                                      NUMBER
 DLM_LOCK_REQUESTS                                  NUMBER
 DLM_PIN_REQUESTS                                   NUMBER
 DLM_PIN_RELEASES                                   NUMBER
 DLM_INVALIDATION_REQUESTS                          NUMBER
 DLM_INVALIDATIONS                                  NUMBER
SQL> select namespace,gets,gethitratio,pins,pinhitratio,reloads,invalidations from v$librarycache;

NAMESPACE             GETS GETHITRATIO       PINS PINHITRATIO    RELOADS INVALIDATIONS
--------------- ---------- ----------- ---------- ----------- ---------- -------------
SQL AREA              2885  .351819757      28191  .937781561        387             0
TABLE/PROCEDURE      10932  .801866081      96645  .971400486        256             0
BODY                   481  .931392931        620  .916129032         19             0
TRIGGER                 15  .533333333        148  .939189189          2             0
INDEX                   38           0         38           0          0             0
CLUSTER                135  .933333333        359  .966573816          3             0
OBJECT                   0           1          0           1          0             0
PIPE                     0           1          0           1          0             0
JAVA SOURCE              0           1          0           1          0             0
JAVA RESOURCE            0           1          0           1          0             0
JAVA DATA                0           1          0           1          0             0

11 rows selected.
RELOADS 是指：library cache中的recreate类型的对象被换出之后，又重新加载到内存library cache中的次/个数。
这些library cache的统计信息对诊断shared pool的各种问题有帮助。

2)Level =2 ,转储hash table概要
SQL> alter session set events 'immediate trace name library_cache level 2';
Session altered.
SQL> select spid from v$session s inner join v$process p on s.paddr=p.addr inner join v$mystat m on s.sid=m.sid and m.statistic#=0;
SPID
------------
28110
[oracle@redhat4 udump]$ cat jiagulun_ora_28110.trc
/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_28110.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      redhat4
Release:        2.6.9-89.ELsmp
Version:        #1 SMP Mon Apr 20 10:34:33 EDT 2009
Machine:        i686
Instance name: jiagulun
Redo thread mounted by this instance: 1
Oracle process number: 17
Unix process pid: 28110, image: oracle@redhat4 (TNS V1-V3)

*** 2012-09-28 16:00:45.489
*** SERVICE NAME:(SYS$USERS) 2012-09-28 16:00:45.487
*** SESSION ID:(145.102) 2012-09-28 16:00:45.487
Permanent space allocated for Load Locks
LATCH:0  TOTAL SPACE: 4092
FREELIST CHUNK COUNT:92  OBJECT SIZE:44
Permanent space allocated for KGL pins
LATCH:0  TOTAL SPACE: 32928
FREELIST CHUNK COUNT:120  OBJECT SIZE:84
Permanent space allocated for KGL locks
LATCH:0  TOTAL SPACE: 81120
FREELIST CHUNK COUNT:196  OBJECT SIZE:104
Permanent space allocated for KGL S handles
LATCH:0  TOTAL SPACE: 439556
FREELIST CHUNK COUNT:0  OBJECT SIZE:316
Permanent space allocated for KGL M handles
LATCH:0  TOTAL SPACE: 630784
FREELIST CHUNK COUNT:0  OBJECT SIZE:512
Permanent space allocated for KGL L handles
LATCH:0  TOTAL SPACE: 155904
FREELIST CHUNK COUNT:0  OBJECT SIZE:768
Permanent space allocated for KGL A handles
LATCH:0  TOTAL SPACE: 160600
FREELIST CHUNK COUNT:0  OBJECT SIZE:200
Permanent space allocated for KGL objects
LATCH:0  TOTAL SPACE: 80976
FREELIST CHUNK COUNT:30  OBJECT SIZE:84
Permanent space allocated for KGL handle dependents
LATCH:0  TOTAL SPACE: 37152
FREELIST CHUNK COUNT:271  OBJECT SIZE:12
LIBRARY CACHE HASH TABLE: size=131072 count=2962
Buckets with more than 20 objects:
NONE
Hash Chain Size     Number of Buckets
---------------     -----------------
              0                128144
              1                  2894
              2                    34
              3                     0
              4                     0
              5                     0
              6                     0
              7                     0
              8                     0
              9                     0
             10                     0
             11                     0
             12                     0
             13                     0
             14                     0
             15                     0
             16                     0
             17                     0
             18                     0
             19                     0
             20                     0
            >20                     0
[oracle@redhat4 udump]$
Oracle9i之后通过新的方式记录Library Cache的使用状况.
按不同的Hash Chain Size代表Library Cache中包含不同对象的个数.
0表示Free的Bucket,>20表示包含超过20个对象的Bucket的个数.

从以上列表中我们看到,包含一个对象的Buckets有2894个,包含2个对象的Buckets有34个，空的Bucket的有128144个。这个数据也说明了Oracle的hash算法效果很好，重复hash的值很少。

另外：

我们也看到了shared pool中的各种对象的信息：Load Locks,KGL pins,KGL locks,KGL S handles,KGL M handles,KGL L handles,KGL A handles,KGL objects,KGL handle dependents。这些信息对了解shared pool锁竞争有帮助。(KGL: Kernel Generic Library)

通过HASH TABLE算法的改进,Oracle Library Cache管理的效率大大提高.

参考：