记一次使用keep buffer改善free buffer wait等待

接到前端人员反映，系统很慢，wait event发现很多free buffer waits

因为这是考勤系统，主要是考勤档已经累计一年的数据，非常臃肿，之前已经跟前端维护人员讲要考虑分表了，但因为各种原因，一直未分
由于只有月底计薪时才慢，而且只访问特定的几个表，于是有了使用keep buffer的思路,table太大，index可否keep?
1.先看看最热的object有哪些

select obj,sum(tch) total_tch from x$bh group by obj order by total_tch asc
      OBJ  TOTAL_TCH
---------- ----------
     60932     341774
    189256     399407
    155089     423242
    189254     627441
    217034     830432
    189258    1174341
    189256    2326405
    217034    2776202
    217032    4348308
    217031   12984898

2.取touch次数top 5的object，分别是217034,217032,217031,189256,189258,查看他们的资讯
select owner,segment_name,bytes/1024/1024 seg_size from dba_segments where (owner,segment_name) in (
SELECT owner,object_name FROM DBA_OBJECTS WHERE DATA_OBJECT_ID IN (217034,217032,217031,189256,189258)
) order by seg_size desc

3.show parameter sga

4.由于sga只有16G,Table的size太大，显然不合适，IDX_FACT_NO1、IDX_EM_DAYWK、PK_EM_DAYWK这3个index 总的大小7519M,可以试一试
5.调整db_keep_cache_size到略大于刚刚计算的总大小

6.将这三个index逐一的放入keep pool

alter index IDHRIS.IDX_FACT_NO1 storage(buffer_pool KEEP)
alter index IDHRIS.IDX_EM_DAYWK storage(buffer_pool KEEP)
alter index IDHRIS.PK_EM_DAYWK storage(buffer_pool KEEP)

7.验证一下：
SELECT OWNER,INDEX_NAME,BUFFER_POOL FROM DBA_INDEXES WHERE BUFFER_POOL=‘KEEP’

在这里插入代码片

SELECT COMPONENT,CURRENT_SIZE/1024/1024 CURRENT_SIZE FROM V$SGA_DYNAMIC_COMPONENTS where COMPONENT='KEEP buffer cache'

8.结果,free buffer wait event大减，用户反馈回来的效果更好
可惜oracle是standard版，要是enterprise版，配合partition table，应该效果更好