分割槽表放入keep pool,recycle pool的問題及解析

531968912發表於2017-02-15
因為生產環境的效能瓶頸,經過診斷,給出的結論是需要把幾個表和索引放入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.




來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25462274/viewspace-2133599/,如需轉載,請註明出處,否則將追究法律責任。

相關文章