兩隻小虎,一隻豬

在Oracle 的運作中有一個很重要的觀念,就是盡量減少不必要的I/O,

因此當使用者查詢資料時,Oracle首先會到Database Buffer Cache內去尋找資料是否存在,如果存在就直接將資料傳回給使用者,這個動作稱為Logical Read
反之如果資料不存在Database Buffer Cache 內,那麼Oracle會將資料從Data file讀出放入Database Buffer Cache,然後傳回給使用者,這個動作稱為Physical Read

在Oracle 8以後的版本,Database Buffer Cache 又進一步分成了Defaule Pool,Keep Pool, Recycle Pool 三種pool
在Performance 的考量下, 我們可以將一些使用頻率較高的table放入Keep Pool內來減少Physical Read 的情況發生

相關工作如下
1. Monitor Buffer busy wait status.
2. Move table to keep pool
3. Monitor Keep pool size

1. Monitor Buffer busy wait status.
在將table 搬到keep pool 之前,首先我們需要知道哪些table 需要被搬到keep pool , 我們可以透過V$SEGMENT_STATISTICS 這個view來查詢segment的統計資料

STATISTIC_NAME 包含了下面統計資訊,找出STATISTIC_NAME 為buffer busy waits,且VALUE欄位較高的table 放入keep pool

1. logical reads
2. buffer busy waits
3. db block changes
4. physical reads
5. physical writes
6. physical reads direct
7. physical writes direct
8. global cache cr blocks served
9.global cache current blocks served
10. ITL waits
11. row lock waits

我們透過一個簡單的Procedure 來定期收集這些資料,並將這些資料寫入Table中

●　Create Monitor Table

CREATE TABLE MON_BUFFER_BUSY_WAIT
(
  MON_DATE     DATE,
  OWNER        VARCHAR2(30 BYTE),
  OBJECT_NAME  VARCHAR2(30 BYTE),
  OBJECT_TYPE  VARCHAR2(18 BYTE),
  VALUE        NUMBER
)

● Create Monitor Procedure

CREATE OR REPLACE PROCEDURE  PROC_BUFFER_BUSY_MONITOR  AS
BEGIN
   delete from mon_buffer_busy_wait where mon_date < sysdate -30;
   commit;
   INSERT INTO mon_buffer_busy_wait (mon_date, owner, object_name, object_type, value)
      (select sysdate,owner,object_name as segment_name,object_type, value as total_buff_busy_waits
          from v$segment_statistics
          where statistic_name in ('buffer busy waits') and object_type like 'TABLE%'
          and  owner in ('HR' ) and value >10);
    commit;
END;
/

● Execute monitor procedure by schedule

. /etc/profile.d/oracle.sh
. /home/oracle/.bash_profile

$ORACLE_HOME/bin/sqlplus ID/PASSWORD
exec PROC_BUFFER_BUSY_MONITOR

這樣子完成了簡單的monitor 工作

2. Move table to keep pool

在還沒決定哪些table要搬到keep pool 之前,先看看SGA中db_keep_cache_size的大小

SQL> show parameter keep                                                       
                                                                               
NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string                                    
control_file_record_keep_time        integer     7                             
db_keep_cache_size                   big integer 0                             
                                                             

查詢v$parameter也可以

SQL> col NAME format A22
SQL> col VALUE formate A12
SQL> select name,value from v$parameter where name like 'db%size%';

NAME                   VALUE
---------------------- --------------------
db_block_size          8192
db_keep_cache_size     32212254720
db_recycle_cache_size  0
db_2k_cache_size       0
db_4k_cache_size       0
db_8k_cache_size       0
db_16k_cache_size      0
db_32k_cache_size      0
db_cache_size          1073741824

9 rows selected.

db_keep_cache_size=0表示keep pool size=0,調整keep pool size

SQL> alter system set db_keep_cache_size=10m;

接下來就可以將MON_BUFFER_BUSY_WAIT 中VALUE較高的Table 搬到keep pool 中

# alter table from keep pool to default;
alter table SCHEMA.TABLE_NAME modify partition PARTITION_NAME storage (buffer_pool default);
alter table SCHEMA.TABLE_NAME storage (buffer_pool default);

# alter table from default pool to keep pool 
alter table SCHEMA.TABLE_NAME modify partition PARTITION_NAME storage (buffer_pool keep);
alter table SCHEMA.TABLE_NAME storage (buffer_pool keep);

3. Monitor Keep Pool Size

隨著資料的增加,Keep pool 可能會不夠用,因此必須每天監控 Keep pool size,

select a.name , a.sga_size_MB, b.table_size_MB , (sga_size_MB - table_size_MB) as free_size
 from 
(
    select name,value/1024/1024  as sga_size_MB 
    from v$parameter 
    where name ='db_keep_cache_size'
) a ,
(
    select sum(bytes)/1024/1024 as table_size_MB
    from dba_segments 
    where buffer_pool='KEEP'
) b

當Keep pool 不夠時,可以透過下面SQL 來決定哪些Table 要移除keep pool

select * from (
   select a.owner,segment_name , segment_type, tablespace_name , MB , 
         nvl( b.busy_cnt,0) as busy_cnt, NVL(c.maxvalue ,0) as maxvalue
   from (
       select owner , segment_name  , segment_type , tablespace_name , sum(bytes)/1024/1024  as MB 
       from dba_segments where buffer_pool='KEEP'
       group by owner , segment_name  , segment_type , tablespace_name
   ) a left join ( 
       select  owner ,  object_name , count(*) as busy_cnt  
       from PERFSTAT.MON_BUFFER_BUSY_WAIT
       group by  owner ,  object_name
   ) b on a.owner = b.owner and a.segment_name= b.object_name  
   left join (
       select   owner ,  object_name , max(value) as maxvalue  
       from PERFSTAT.MON_BUFFER_BUSY_WAIT
       group by    owner ,  object_name
   ) c on a.owner = c.owner and a.segment_name= c.object_name 
) 
where busy_cnt = 0 order by mb desc

Reference :

http://www.oradbpedia.com/wiki/Using_the_KEEP_and_RECYCLE_Pool