Oracle數據庫字典表優化小技巧

一、
字典表一般是比較常用的，可以設置這些表使用keep池。
先分析一下方案中的字典表，以便計算空間，簡單起見，所有表都分析一下，調用存儲過程：
dbms_stats.gather_schema_stats(ownname=>'Schema名稱')
計算一下所需的空間：
select sum(blocks)*8 from usertables where name like 'DICT\%' escape '\';
注：8是數據塊的大小，8k，有的是4k。假定字典表是以DICT_開頭的表。
得到所需空間的大小，單位為k，根據統計結果設置一下keep池的大小。keep池默認是0，不啟用。
alter system set db_keep_cache_size=80m;

二、
一般系統里的字典表都是比較穩定的，不常修改的，因此可以設置pctfree為0，可節省空間、提高訪問速度。

針對以上兩個優化思路，寫了個簡單的存儲過程，僅在測試庫上測試了一下，如果數據量巨大、存儲空間緊張，請謹慎使用。
CREATE OR REPLACE procedure KeepDictTab is
/**
2012-01-04設置小表的pctfree為0、且使用keep池，提高效率
**/
begin
    for rec_tab in (select table_name from user_tables where tablename like 'DICT\%' escape '\') loop
        execute immediate 'alter table ' || rec_tab.table_name ||' pctfree 0 storage (buffer_pool keep)';
        execute immediate 'alter table ' || rec_tab.table_name || ' move tablespace tbs2';
        --切換表空間，可使用新存儲特性保存數據，切換后，重建一下索引，不然移不回來了。
        for rec_idx in (select index_name from user_indexes where table_name =rec_tab.table_name) loop
            execute immediate 'alter index '||rec_idx.index_name ||' rebuild';
        end loop;
        --切換回原來的表空間，再重建一下索引       
        execute immediate 'alter table ' || rec_tab.table_name || ' move tablespace tbs1';
        for rec_idx in (select index_name from user_indexes where table_name =rec_tab.table_name) loop
            execute immediate 'alter index '||rec_idx.index_name ||' rebuild';
        end loop;
        --分析表
        execute immediate 'analyze table ' || rec_tab.table_name || ' estimate statistics';
    end loop;

    for rec_idx in (select index_name from user_indexes where tablename like 'DICT\%' escape '\') loop
        --分析相關的索引
        execute immediate 'analyze index '||rec_idx.index_name ||' estimate statistics';
    end loop;
end KeepDictTab;
/

設置完成后，可以用執行計劃看一下，讀取的數據塊有所減少，COST有所降低、速度有一點點兒變快。

轉自： http://wallimn.iteye.com/blog/1335280