ORACLE調優之–多緩衝池命中率（摘自文平書）

評估資料快取效率的指標是命中率。該命中率通過查詢V$SYSSTAT 和$BUFFER_POOL_STATISTICS視圖可以得到。這個視圖可以
查詢每個緩衝池各自的命中率。緩衝池的命中率的計算採用下面的公式:

   1-(physical_reads/(db_block_gets+consistent_gets))

   下面的SQL語句常用來查詢當前各個緩衝池的命中率情況:
    SQL>select name,physical_reads,db_block_gets,consistent_gets,
        1-(physical_reads/(db_block_gets+consistent_gets))  "命中率"
        from v$buffer_pool_statistics;

SQL> select name,physical_reads,db_block_gets,consistent_gets,
  2          1-(physical_reads/(db_block_gets+consistent_gets))  "命中率"
  3          from v$buffer_pool_statistics;

NAME                 PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS     ??????
-------------------- -------------- ------------- --------------- ----------
DEFAULT                        3806          1541           44202 .916796013

 

通過對V$BH視圖的查詢可以獲得當前存放在SGA中的所有資料區塊的塊ID，下面列出了兩種方法，用來找出資料庫物件（表、索引等
段對象）佔用資料緩衝的狀態，或者某個段使用緩衝的情況。下列的查詢語句列出了當前資料快取中存放的所有資料區塊的段名，
以及該段在資料緩衝中資料區塊的計數:

  SQL>select o.object_name OBJECT_NAME,count(*) number_of_blocks
        from dba_objects o,v$BH bh
        where o.data_object_id=bh.objd
        and o.owner  not in('SYS','SYSMAN')
        group by o.object_name
        order by count(*);

||||||||||||||||||||||||||||||||||

 

另一種查詢可以從對象的角度查詢快取使用方式。下面的SQL語句查詢了表MY_TABLE 使用了多少緩衝塊。

SQL>select count(*) "BUFFERS_BLOCKS",count(*)*8192/1024/1024 "BUFFERS_USED(M)"
    from V$BH
    where OBJD =
    (select data_object_id from dba_objects where object_name ='MYTABLE');

  BUFFERS_BLOCKS     BUFFERS_USED(M)
-----------------    --------------
983                  7.6796875

 

下面的語句查詢了當前資料庫系統多緩衝池的配置情況:
SQL>select name "BUFFER POOL",BLOCK_SIZE ,SUM(BUFFERS) "TOTAL BUFFERS"
    from v$BUFFER_POOL
    GROUP BY NAME,BLOCK_SIZE
    HAVING SUM(BUFFERS)>0;
SQL> select name "BUFFER POOL",BLOCK_SIZE ,SUM(BUFFERS) "TOTAL BUFFERS"
  2      from v$BUFFER_POOL
  3      GROUP BY NAME,BLOCK_SIZE
  4      HAVING SUM(BUFFERS)>0;

BUFFER POOL          BLOCK_SIZE TOTAL BUFFERS
-------------------- ---------- -------------
DEFAULT                    8192         24451

 

保留池和迴圈池

   顯然，最常用的資料應該長期保留在記憶體結構中。保留池的作用就是長期存放某些“熱”的資料區塊，也就是說，你應該想辦法找出你的系統中那些“熱”的表，並將其綁定在保留池中。
   系統參數DB_KEEP_CACHE_SIZE定義了保留池的空間大小，其資料區塊尺寸為本系統標準的資料區塊尺寸。應該注意的是，保留池並不是
預設池的一個子集。因此配置時要注意其大小不應超出作業系統可以提供的容量。
   當遇到一個工程上的難題：業務資料庫系統中有成百上千張表，還有成千上萬個索引，究竟哪些表或索引應該放入保留池呢？

遴選規則如下:
該表中資料並不是很大
該表在SQL操作中被頻繁調用
該表往往是資料庫實體關聯圖中的中間表或參照表

和保留池的意義敲好相反，一些鮮有SQL操作的大表，其資料區塊並無必要長期保留在資料緩衝中。
這時，我們可以通過設定迴圈池，將這些對象放入迴圈池。

迴圈池通過初始化參數DB_RECYBLE_CACHE_SIZE指定。同樣地，迴圈池不是預設池的一個子集。下面的命令設定DB_KEEP_CACHE_SIZE初始化
參數，將保留池尺寸定位120MB：
SQL>alter system set db_keep_cache_size=120M scope=both;