【原創】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/，如需轉載，請註明出處，否則將追究法律責任。