Oracle表與索引的分析及索引重建

標籤：style   使用   io   strong   for   資料   問題   div   amp   

1.分析表與索引（analyze 不會重建索引）

 analyze table tablename compute statistics 
等同於 analyze table tablename compute statistics for table for all indexes for all columns

for table 的統計資訊存在於視圖：user_tables 、all_tables、dba_tables

for all indexes 的統計資訊存在於視圖: user_indexes 、all_indexes、dba_indexes

for all columns 的統計資訊存在於視圖：user_tab_columns、all_tab_columns、dba_tab_columns

註：分析 表與 索引見 AnalyzeAllTable預存程序

2、一般來講可以採用以下三種方式來手工分析索引。
analyze index idx_t validate structure：
analyze index idx_t compute statistics：
analyze index idx_t estimate statistics sample 10 percent

1）analyze index idx_t validate structure：
這段分析語句是用來分析索引的block中是否有壞塊兒，那麼根據分析我們可以得到索引的結構資料，這些資料會保留到
index_stats中，來判斷這個索引是否需要rebuild. 需要注意的是這樣的分析是不會收集索引的統計資訊的。

2）validate structure有二種模式： online, offline， 一般來講預設的方式是offline。
當以offline的模式analyze索引時，會對table加一個表級共用鎖定，對目前table的一些即時DMl操作會產生一定的影響。
而以online模式分析時候，則不會加任何lock，但在index_stats中是看不到任何資訊的。

3）analyze index idx_t compute statistics：
用來統計索引的統計資訊(全分析)，主要為CBO服務。

4）analyze index idx_t estimate statistics sample 10 percent
主要是用來指定比例進行抽樣分析，也是為CBO服務. 例中是抽樣10%

3.重建索引
alter index index_name rebuild tablespace tablespace_name 
alter index index_name rebuild tablespace tablespace_name 加入表空間名，會將指定的索引移動到指定的表空間當中。

註：
analyze 操作只是統計資訊，並將統計資訊存放起來供日後分析SQL使用，不進行重建之類的具體實施性操作,因此要重建索引的話
還是要用 alter index index_name rebuild

4、其他的統計方法

1）DBMS_STATS：這個當然是最強大的分析包了
--建立統計資訊曆史保留 表
exec dbms_stats.create_stat_table(ownname => ‘scott‘,stattab => ‘stat_table‘);

--匯出整個scheme的統計資訊
exec dbms_stats.export_schema_stats(ownname => ‘scott‘,stattab => ‘stat_table‘);

--分析scheme
Exec dbms_stats.gather_schema_stats(ownname => ‘test‘,options => ‘GATHER AUTO‘,
                                       estimate_percent => dbms_stats.auto_sample_size,
                                       method_opt => ‘for all indexed columns‘,
                                       degree => 6 );

--分析 表
exec dbms_stats.gather_table_stats(ownname => ‘TEST‘,tabname => ‘sm_user‘,estimate_percent => 10,method_opt=> ‘for all indexed columns‘) ;

--分析 索引
exec dbms_stats.gather_index_stats(ownname => ‘TEST‘,indname => ‘pk_user_index‘,estimate_percent => ‘10‘,degree => ‘4‘) ;

--如果發現執行計畫走錯，刪除 表的統計資訊
exec dbms_stats.delete_table_stats(ownname => ‘TEST‘,tabname => ‘SM_USER‘) ;

--匯入 表的曆史統計資訊
exec dbms_stats.import_table_stats(ownname => ‘TEST‘,tabname => ‘SM_USER‘,stattab => ‘stat_table‘) ;

--如果進行分析後，大部分 表的執行計畫都走錯，需要導回整個scheme的統計資訊
exec dbms_stats.import_schema_stats(ownname => ‘TEST‘,stattab => ‘SM_USER‘);

--匯入 索引的統計資訊
exec dbms_stats.import_index_stats(ownname => ‘TEST‘,indname => ‘PK_USER_INDEX‘,stattab => ‘stat_table‘)


analyze和dbms_stats不同的地方:
analyze是同時更新 表和 索引的統計資訊，而dbms_stats會先更新 表的統計資訊，然後再更新 索引的統計資訊，
這裡就有一個問題，就是當 表的統計資訊更新後，而 索引的統計資訊沒有被更新，這時候cbo就有可能選擇錯誤的plan

2）DBMS_UTILITY.ANALYZE_SCHEMA：可直接分析SCHEMA中所有對象
   如：EXEC DBMS_UTILITY.ANALYZE_SCHEMA (‘LTTFM‘,‘COMPUTE‘);

3）DBMS_DDL.ANALYZE_OBJECT：收集對象的的統計資訊

Oracle表與索引的分析及索引重建