某次，运营商客户的计费库反应其入库程序很慢，应用方通过监控程序发现主要慢在对于几个表的插入操作上。按照我们的通常理解，insert应该是极快的，为什么会很慢呢？难道是大量的redo日志产生或者undo不足？
据客户反馈，在反应之前应用程序都是正常的，这个问题是突然出现的，而且是每个月中下旬开始出现慢的情况。这让我百思不得其解。 通过检查event也并没有发现什么奇怪的地方，于是我通过10046 跟踪了应用的入库程序，如下即是应用方反应比较慢的表的插入操作，经过验证确实非常慢。
INSERT INTO XXXX_EVENT_201605C (ROAMING_NBR,.....,OFFER_INSTANCE_ID4) VALUES (:ROAMING_NBR,.....:OFFER_INSTANCE_ID4) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 17 0.00 0.00 0 0 0 0 Execute 18 1.06 27.41 4534 518 33976 4579 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 35 1.06 27.41 4534 518 33976 4579 …… Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 4495 0.03 24.02 gc current grant 2-way 2301 0.00 0.77 SQL*Net more data from client 795 0.00 0.02 ...... latch: gcs resource hash 1 0.00 0.00
以上分析可以发现，插入了4579条数据，一共花了27.41秒。其中有24.02秒消耗在db file sequential read上。很明显这是索引的读取操作，实际上检查10046 trace 裸文件，发现等待的对象确实是该表上的2个索引。同时从上面10046 trace可以看出，该SQL执行之所以很慢，主要是因为存在了大量的物理读，其中4579条数据的插入，物理读为4534。这说明什么问题呢？ 这说明，每插入一条数据大概产生一个物理读，而且都是index block的读取。 如下是10046 trace跟踪产生的裸文件内容。
很明显，通过将该index cache移到keep 池可以解决该问题。 实际上也确实如此，通过cache后，应用反馈程序快了很多。 那么对该问题，这里其实有几个疑问，为什么这里的SQL insert时物理读如此之高？这里我进一步来检查者2个Index的情况，如下所示。
通过以上数据可以发现，顺序读等待最高的2个恰好其clustering_factor比较高。有没有可能跟这个有关系呢？
要解释这个问题，其实并不容易。首先这里我们需要理解Oracle B-tree Index的结构，如图13-4所示。
图13-4
当Oracle向index branch block或leaf block中插入数据时，其实都是等价的，Oracle这里其实并不需要进行代价的估算。随机选择branch block或者leaf block，让其插入键值即可。所以这里其实也就跟我们前面提到的Index 聚簇因子没有任何关系了。实际上我通过创建测试表，分别模拟聚簇因子大和小的情况，进行多次插入以及10046 trace跟踪分析，发现时间几乎一致。从测试来看也可以排除其影响。
不过Index 聚簇因子也是一直表现形式。当聚簇因子与表的行数非常接近时，说明数据分散程度比较高。假设现在现在有一个表Test，表上创建了一个Index IDX1。当向表中插入数据时，Oracle会进行index的同步维护，也就是需要同时向index block中插入键值。如果此时IDX1 索引的离散读非常高，那么也就是说每次插入数据时Oracle 在cache 中命中index block的概率可能就非常低，这样就不得不进行物理读操作，进而影响插入的效率。
想到这一点，我相信大多数人已经知道如何处理该问题了。最简单的方式就是将整个Index 缓存到内存中。这样就可以避免在插入数据时进行大量物理读操作了。
那么如何解释客户说的每个月中下旬这个应用模块就开始慢的问题呢？ 结合前面的分析，其实这一点我们很容易解释。每个月初客户都会创建类似的空表，当数据量很小的情况下，Index 也是非常小的。即使index 键值非常离散，那么index block在cache 中缓存的可能性都比较大。因为索引比较小，而SGA的buffer cache 够大。然而随着时间的推移，数据量越来越大，index也会越拉越大，这也就降低了index block被cache的几率。
根据客户的业务特点，每个月都会创建类似的表和Index，因此我部署了一个脚本具体如下所示。
#!/usr/bin/sh
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=/oracle/app/oracle/product/102
export ORACLE_SID=xx
export ORACLE_TERM=xterm
export NLS_LANG=American_america.zhs16gbk
export PATH=$ARCH_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/urs/ccs/bin:/bin:/usr/bin:$PATH
C_DATE=`date +%Y%m`
SQLplus "/as sysdba" << EOF
alter index BILL.I1_ILE_DATA_EVENT_${C_DATE}A storage (buffer_pool keep);
alter index BILL.I1_ILE_DATA_EVENT_${C_DATE}B storage (buffer_pool keep);
alter index BILL.I1_ILE_DATA_EVENT_${C_DATE}C storage (buffer_pool keep);
alter index BILL.I1_ILE_DATA_EVENT_${C_DATE}D storage (buffer_pool keep);
alter index BILL.I1_ILE_DATA_EVENT_${C_DATE}E storage (buffer_pool keep);
alter index BILL.I2_ILE_DATA_EVENT_${C_DATE}A storage (buffer_pool keep);
alter index BILL.I2_ILE_DATA_EVENT_${C_DATE}B storage (buffer_pool keep);
alter index BILL.I2_ILE_DATA_EVENT_${C_DATE}C storage (buffer_pool keep);
alter index BILL.I2_ILE_DATA_EVENT_${C_DATE}D storage (buffer_pool keep);
alter index BILL.I2_ILE_DATA_EVENT_${C_DATE}E storage (buffer_pool keep);
EXIT;
EOF
通过这样的方式临时解决了该问题。而且从2016年5月解决之后至今客户没有再反馈过该业务模块有问题，看来是彻底解决了这个问题。
其实这里还有一个问题。一般来讲，物理内存都是有限的，而且SGA的keep 池不可能设置得过大。同时如果缓存的Index 比较多，比较大，那么Oracle的keep pool对于缓存对象的处理机制是如何的？
通过以下测试来探索一下Keep池对于缓存的对象的处理机制。
（1）创建2个测试表，并创建好相应的index，如下所示。
SQL> conn roger/roger SQL> create table t_insert as select * from sys.dba_objects where 1=1; SQL> create index idx_name_t on t_insert(object_name); SQL> analyze table t_insert compute statistics for all indexed columns; SQL> select INDEX_NAME,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,CLUSTERING_FACTOR,NUM_ROWS from dba_indexes where table_name='T_INSERT'; INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS ------------- ---------- ----------- ------------- ----------------- ---------- IDX_NAME_T 1 246 29808 24664 49859 SQL> alter system set db_keep_cache_size=4m; SQL> create table t_insert2 as select * from sys.dba_objects where 1=1; SQL> create index idx_name_t2 on t_insert2(object_name); SQL> insert into t_insert select * from sys.dba_objects; ……多次insert SQL> commit;
从前面的信息我们可以看出，object_name上的index聚簇因子比较高，说明其数据分布比较离散。
（2）我们现在将index都cache 到keep 池中，如下所示。
SQL> alter index idx_name_t storage (buffer_pool keep); SQL> alter index idx_name_t2 storage (buffer_pool keep); SQL> alter system flush buffer_cache;
这里需要注意的是，仅仅执行alter 命令是不够的，还需要手工将index block读取到keep池中，如下所示。
SQL> conn /as sysdba SQL> @get_keep_pool_obj.SQL no rows selected SQL> select /*+ index(idx_name_t,t_insert) */ count(object_name) from roger.t_insert; COUNT(OBJECT_NAME) ------------------ 99721 SQL> @get_keep_pool_obj.SQL SUBCACHE OBJECT_NAME BLOCKS ------------ ------------------------------ ---------- KEEP IDX_NAME_T 499 DEFAULT T_INSERT 431 SQL> select /*+ index(idx_name_t2,t_insert2) */ count(object_name) from roger.t_insert2; COUNT(OBJECT_NAME) ------------------ 99723 SQL> @get_keep_pool_obj.SQL SUBCACHE OBJECT_NAME BLOCKS ------------ ------------------------------ ---------- KEEP IDX_NAME_T 40 KEEP IDX_NAME_T2 459 DEFAULT T_INSERT2 522 DEFAULT T_INSERT 431 SQL> select /*+ index(idx_name_t,t_insert) */ count(object_name) from roger.t_insert; COUNT(OBJECT_NAME) ------------------ 99721 SQL> @get_keep_pool_obj.SQL SUBCACHE OBJECT_NAME BLOCKS ------------ ------------------------------ ---------- KEEP IDX_NAME_T 467 KEEP IDX_NAME_T2 32 DEFAULT T_INSERT2 522 DEFAULT T_INSERT 431
通过上述的多次简单测试可以看出，keep pool对于缓存的block的清除机制是先进先出原则。这相对于Oracle Buffer Cache 的机制来讲，要简单许多。
最后针对这个问题，可以总结一下，产生类似问题的原因可能有哪些呢? 实际上可能有很多种，如下所示。
（1）Index 碎片过于严重。
（2）Index next extent 分配问题。
（3）Buffer Cache 设置偏小。
（4）Index分区方式不合理。
（5）Redo、undo相关问题
上述方面内容都可能产生本文中提到的问题，只不过表现形式可能不同而已。这是一个常规而又隐秘的问题，我们通常容易忽视它，但是又很容易出现产生一些问题。这些问题会对业务系统性能产生重大影响，希望能够引起大家的重视。
