分区表放入keep pool,recycle pool的问题及解析

因为生产环境的性能瓶颈，经过诊断，给出的结论是需要把几个表和索引放入keep pool,几个索引放入recycle pool.
其实放入keep pool 确实对于频繁访问的数据，而且数据量不大的情况下，性能有一定的改善。避免了大量的物理读。大家的大体感觉都是把表放入keep pool,其实在一定的情况下，把索引放入keep pool也有一定的道理，某些较大的索引，可能已经走了索引但是还是因为索引占用的数据块较多，段太大，还是会走大量的物理读，放入keep pool也有一定的改善。
至于recycle pool的使用，对于数据量很大的表来说，如果数据访问不是很频繁但是因为性能考虑，还是需要做些什么的时候，可以考虑把表或者索引放入recycle pool，让它尽管去不停的刷那一部分缓存，不会带来太多的性能抖动。

闲话少说，放入keep pool,recycle pool,可以使用两个ddl语句就能简单完成。
alter table xxxx storage(buffer_pool keep);
alter table xxx storage(buffer_pool recycle);
对于索引也是类似,如果要取消，只需要制定为默认的pool就可以了
alter index xxxx storage(buffer_pool default).
都是修改storage属性。

当然了，这些操作都要确保db_recycle_cache_size，db_keep_cache_size都得指定一定空间。
这次操作的是几个分区表和分区索引，很快就执行了语句，然后自己验证的时候突然发现，有的没起作用。很是奇怪。

先来看看模拟的数据，两个表,一个是分区表，一个是普通heap表。它们默认都在default pool中。
SQL> select table_name,buffer_pool from user_tables;
TABLE_NAME                     BUFFER_POOL
------------------------------ ---------------------
TEST                               DEFAULT
PARTITION_TEST                 DEFAULT

把普通表和分区表都放入keep pool中。
SQL> alter table test storage(buffer_pool keep);
Table altered.

SQL> alter table partition_test storage(buffer_pool keep);
Table altered.

查看它们的存储情况。发现一个在keep pool，一个却还在default pool.
SQL> select table_name,buffer_pool from user_tables;
TABLE_NAME                     BUFFER_POOL
------------------------------ ---------------------
TEST                                   KEEP
PARTITION_TEST                 DEFAULT

想是不是放入keep pool还需要一定的时间，然后又执行了一遍。
SQL> alter table partition_test storage(buffer_pool keep);
Table altered.

然后再次查看结果还是一样。
SQL>  select table_name,buffer_pool from user_tables;


TABLE_NAME                     BUFFER_POOL
------------------------------ ---------------------
TEST                                  KEEP
PARTITION_TEST                 DEFAULT

翻看metalink，对于分区表的keep pool，确实有一些bug,但是bug已经在11.2.0.2.0版本已经修复了，我这个环境都是11.2.0.3.0的环境了。
其中一个bug（1704527）， （ ID 17040527.8），给出的workaround是刷新shared_pool,然后再次尝试。
SQL> alter system flush shared_pool;
System altered.

SQL> select table_name,buffer_pool from user_tables;
TABLE_NAME                     BUFFER_POOL
------------------------------ ---------------------
TEST                                   KEEP
PARTITION_TEST                 DEFAULT

最后发现，需要在分区表的视图中查看才能看到。
SQL> select table_name,buffer_pool from user_tab_partitions where table_name='PARTITION_TEST';
TABLE_NAME                     BUFFER_POOL
------------------------------ ---------------------
PARTITION_TEST                 KEEP

最后重新放回default pool
SQL> ALTER TABLE PARTITION_TEST STORAGE(BUFFER_POOL DEFAULT);
Table altered.

可以看到又回到了default池。
SQL>  select table_name,buffer_pool from user_tab_partitions where table_name='PARTITION_TEST';
TABLE_NAME                     BUFFER_POOL
------------------------------ ---------------------
PARTITION_TEST                 DEFAULT
最后问题会到原点，为什么分区表的buffer_pool显示就这么特别呢。
用一个例子来说明。分区表中有很多分区，可以在创建分区表的时候就指定哪些分区放到keep pool哪些放到default pool.
我把一个分区表中的两个分区放到了keep pool,剩下的都放到了default pool.如下所示。

TABLE_NAME                     PARTITION_NAME                                                                          BUFFER_POOL
------------------------------ ------------------------------------------------------------------------------------------ ---------------------
PARTITION_TEST2              PMAXVALUE                                                                                  DEFAULT
PARTITION_TEST2              PR001_P1                                                                                   KEEP
PARTITION_TEST2              PR002_P2                                                                                   DEFAULT
PARTITION_TEST2              PR003_P3                                                                                   KEEP
PARTITION_TEST2              PR004_P4                                                                                   DEFAULT
PARTITION_TEST2              PR005_P5                                                                                   DEFAULT
PARTITION_TEST2              PR006_P6                                                                                   DEFAULT
PARTITION_TEST2              PR007_P7                                                                                   DEFAULT
PARTITION_TEST2              PR008_P8                                                                                   DEFAULT
PARTITION_TEST2              PR009_P9                                                                                   DEFAULT
PARTITION_TEST2              PR010_P10                                                                                  DEFAULT
11 rows selected.

面对这种情况，在user_tables中是显示default好呢，还是keep好呢我觉得还是default好。想看具体的细节还是得到分区数据字典里。
SQL> select buffer_pool,table_name from user_tables where table_name='PARTITION_TEST2';


BUFFER_POOL           TABLE_NAME
--------------------- ------------------------------
DEFAULT               PARTITION_TEST2

如果在创建分区表之后想对某些分区指定存储特性，可以使用如下的语句。
SQL> alter table PARTITION_TEST2 modify partition "PR009_P9" storage(buffer_pool keep);
Table altered.