【原创】cache buffer chains的一次解决过程

环境：AIX 5.2

数据库版本：10.2.0.2

一个生产数据库的cache buffer chains等待比较严重，考虑对其进行优化，下面是对这个问题的一次解决过程（主要是加大热表的pctfree）：

$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Tue May 27 20:21:29 2008

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

原来已经通过x$bh找到了几张最热的表，并且将这几张表放在了keep池中
SQL> select table_name from dba_tables where buffer_pool='KEEP';

TABLE_NAME
------------------------------
TD_MESSAGE
TS_PHRASE
TD_PLAN_LEG

查看一下keep池的大小是不是合适
SQL> show parameter keep

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
buffer_pool_keep                     string
control_file_record_keep_time        integer                          7
db_keep_cache_size                   big integer                      64M
SQL> select sum(blocks) from dba_tables where buffer_pool='KEEP';

SUM(BLOCKS)
-----------
      11821

SQL>select 11821*8/1024 from dual;

11821*8/1024
------------
  92.3515625

可以看出keep池目前64m的配置已经不能满足要求了，将它扩展为100m

SQL> alter system set db_keep_cache_size=100m;

System altered.

查看热表的pctfree，发现都是默认的10
SQL> select table_name,pct_free from dba_tables where buffer_pool='KEEP';

TABLE_NAME                       PCT_FREE
------------------------------ ----------
TD_MESSAGE                             10
TS_PHRASE                              10
TD_PLAN_LEG                            10

将最热的td_plan_leg表的pctfree改为40，尽量将数据分散在多个块中
SQL> alter table fgos.td_plan_leg pctfree 40;

Table altered.

为了避免行迁移，将表td_message的pctfree改为20
SQL> alter table fgos.td_message pctfree 20;

Table altered.

SQL> select table_name,pct_free,avg_row_len from dba_tables where buffer_pool='KEEP';

TABLE_NAME                       PCT_FREE AVG_ROW_LEN
------------------------------ ---------- -----------
TD_MESSAGE                             20         223
TS_PHRASE                              10         108
TD_PLAN_LEG                            40         480

修改完成后move 表td_plan_leg和td_message，使修改在已有的块上生效
SQL>alter table fgos.TD_PLAN_LEG move;

Table altered.

SQL>alter table fgos.TD_MESSAGE move;

Table altered.

将这两张表上的index rebuild
SQL> select 'alter index fgos.'||index_name||' rebuild online;' from dba_indexes where table_name in ('TD_PLAN_LEG','TD_MESSAGE');

'ALTERINDEXFGOS.'||INDEX_NAM
----------------------------------------------------------------------------------------------
alter index fgos.XTD_MESSAGE_OPTM rebuild online;
alter index fgos.TM_TYPEAD_INDEX rebuild online;
alter index fgos.TM_SUB_TYPE_INDEX rebuild online;
alter index fgos.TM_SND_TM_INDEX rebuild online;
alter index fgos.TM_MSG_TYPE_INDEX rebuild online;
alter index fgos.TM_FLTNO_INDEX rebuild online;
alter index fgos.SYS_C0027692 rebuild online;
alter index fgos.SYS_IL0000081505C00010$$ rebuild online;
alter index fgos.SYS_C0012101 rebuild online;
alter index fgos.XTD_PLAN_LEG_THRARR rebuild online;
alter index fgos.XTD_PLAN_LEG_THRDEP rebuild online;
alter index fgos.XTD_PLAN_LEG_OPTM rebuild online;
alter index fgos.XTD_PLAN_LEG_FLTNO rebuild online;
alter index fgos.XTD_PLAN_LEG_ALTW rebuild online;
alter index fgos.XTD_PLAN_LEG_PUBFLAG rebuild online;
alter index fgos.XTD_PLAN_FLT_ID rebuild online;

16 rows selected.

重新收集两张表的统计值
SQL> exec dbms_stats.gather_table_stats('fgos','td_plan_leg');

PL/SQL procedure successfully completed.

SQL>  exec dbms_stats.gather_table_stats('fgos','td_message');

PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/498744/viewspace-327293/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/498744/viewspace-327293/