共享池失败率

1.

SQL> show parameter shared_pool_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
shared_pool_size                     big integer 0 

2. 如果共享池设置过小,则运行SQL,PL/SQL 程序所占用的时间会较长,影响数据库性能.对于共享池而言,要求某一个实体,如存储过程等,尽可能驻留内在区域,增大程序运行的效率,减少重新分析与编译的时间.

  查询数据字典v$rowcache了解共享池中数据字典快存的成功与失败次数:

SQL> select sum(gets)"dictionary gets",
  2  sum(getmisses)"dictionary cache getmisses"
  3  from v$rowcache;

dictionary gets dictionary cache getmisses
--------------- --------------------------
         435959                      12827

其中,GETS表示读取某一类数据字典时的成功次数.GETMISSES表示读取某一类数据字典时的失败次数.

  查询结果计算共享池中数据字典快存的成功率:

SQL> select parameter,gets,Getmisses,getmisses/(gets+getmisses)*100
  2  "miss ratio",
  3  (1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100 "Hit ratio"
  4  from v$rowcache
  5  where gets+getmisses<>0
  6  group by parameter,gets,getmisses;

PARAMETER                              GETS  GETMISSES miss ratio  Hit ratio
-------------------------------- ---------- ---------- ---------- ----------
dc_awr_control                          277          1  .35971223 99.6402878
dc_global_oids                        90308        135 .149265283 99.8507347
dc_tablespaces                        23011          9 .039096438 99.9609036
dc_sequences                             20          6 23.0769231 76.9230769
dc_histogram_data                     13228       1643 11.0483491 88.9516509
dc_segments                           10462       1477 12.3712204 87.6287796
dc_files                                 21          7         25         75
dc_object_ids                        119415       1713 1.41420646 98.5857935
outstanding_alerts                      636         74 10.4225352 89.5774648
dc_object_grants                         24          2 7.69230769 92.3076923
dc_histogram_data                      4492        238 5.03171247 94.9682875

PARAMETER                              GETS  GETMISSES miss ratio  Hit ratio
-------------------------------- ---------- ---------- ---------- ----------
dc_users                                378         14 3.57142857 96.4285714
dc_tablespace_quotas                     12          2 14.2857143 85.7142857
dc_users                             119444         20 .016741445 99.9832586
dc_objects                            17769       1511 7.83713693 92.1628631
dc_profiles                             260          1 .383141762 99.6168582
dc_rollback_segments                   2746         21 .758944705 99.2410553
dc_usernames                           4576         17 .370128456 99.6298715
dc_histogram_defs                     28982       5936 16.9998282 83.0001718

已选择19行。

查询数据字典v$LIBRARYCACHE,计算共享池中库缓存的失败率,结果应该小于1%:

SQL> select sum(pins)"Total Pins", sum(reloads)"Total Reloads",
  2  sum(reloads)/sum(pins)*100 libcache
  3  from v$librarycache;

Total Pins Total Reloads   LIBCACHE
---------- ------------- ----------
    237358          6449  2.7169929 

其中,Total Pins表示驻留内存次数;Total Reloads表示重新加载到内存的次数;LIBCACHE表示失败率