Oracle對錶、索引和簇的分析

分析Oracle的表、索引或簇，可以採集其有關的資料，或者校驗其儲存格式的合法性。還可以分析這些模式物件以便收集或更新指定物件的統計資料。當釋出DDL語句時，參照物件的統計資料被用於確定該語句的最有效的執行方案。即使用CBO就需要有足夠的分析資料。

 

一、使用表、索引、簇的統計表

    使用ANALYZE語句來收集統計資訊到資料字典中。當使用CBO來執行SQL時，就會利用這些統計資料以得出結論。
 
    COMPUTER STATISTICS

 
    當計算統計數字時，掃描整個物件，收集關於此物件的資料。Oracle用這些資料來計算此物件的精確統計資料。在這些計算出的統計資料中，整個物件的微小變化都被計算出來。因為為了收集計算統計資料的資訊要掃描整個物件，所以物件的體積越大，收集所有資訊所要做的工作就越多。

    ESTIMATE STATISTICS

 
    當估計統計資料時，Oracle收集物件的各部分有代表性的資訊。該資訊的子集提供了有關該物件合理的、估計的統計資料。估計出的統計出具的精確度取決於Oracle所使用的樣例的代表性。因為收集估計統計資料只是掃描物件的幾個部分，因此能快速得分析一個物件，也可以隨意得指定Oracle在做估計時所使用的行的數量和百分比。

 
    注意：在計算表或簇的統計資料時，需要有足夠的臨時空間。但分析索引時不需要臨時空間。

二、使用ANALYZE語句計算統計資料

    ANALYZE TABLE emp COMPUTE STATISTICS; --完全統計
    ANALYZE TABLE emp ESTIMATE STATISTICS; --預設的1064行統計樣例
    ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 2000 ROWS; --用2000行做統計樣例
    ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 33 PERCENT; --用33%做統計樣例

    統計得到的資訊有以下這些(帶*號的表示資料是精確的)

    表：
    ● 行數
    ● 已經使用的資料塊數*
    ● 從未使用的資料塊數
    ● 平均可用的空閒空間
    ● 連結行的數目
    ● 平均行長度
    ● 列中不同的值的數目
    ● 列的下限值*
    ● 列的上限值*

 
    索引：
    ● 索引層次*
    ● 葉子資料塊的數目
    ● 不同的鍵的數目
    ● 每個鍵的葉子資料塊的平均數目
    ● 每個鍵的資料塊的平均數目
    ● 分簇因子

    注意：若一個索引已標記為UNUSABLE，則在分析時報錯，必須刪除或重建後才能分析。

 
    簇：
    ● 簇鍵鏈的平均長度

    注：當分析簇的統計資料時，簇中的表盒索引的統計資訊會被自動收集

 
三、操作物件的統計資料

1、檢視統計資訊
    DBA|ALL|USER_INDEXES
    DBA|ALL|USER_TABLES
    DBA|ALL|USER_TAB_COLUMNS
    注意：這些表中的上面所列統計資訊，如果不ANALYZE的話，是一直不變的。

2、刪除統計資訊
    ANALYZE TABLE emp DELETE STATISTICS;

    刪除後可以防止table再使用CBO

3、其他的統計方法
    使用PLSQL包也可以對錶進行資料統計分析

    DBMS_STATS：這個當然是最強大的分析包了
    DBMS_UTILITY.ANALYZE_SCHEMA：可直接分析SCHEMA中所有物件
    DBMS_DDL.ANALYZE_OBJECT：收集物件的的統計資訊
 

四、校驗表、索引、簇和物化檢視

    為了校驗表、索引、簇和物化檢視的結構的完整性，使用帶有VALIDATE STRUCTURE選項的ANALYZE語句，如果返回錯誤訊息，則說明該物件已損壞。如果物件損壞，則需要刪除並重建。如果是物化檢視，則僅需要重新完全重新整理一遍

    校驗的語句如下：
    ANALYZE TABLE emp VALIDATE STRUCTURE;

    如果需要校驗與某物件有關聯的所有物件是否有效，則使用CASCADE子句：
    ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;

    再加入聯機結構校驗：
    ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE ONLINE;
 

五、列出表和簇的連結行

    可使用LIST CHAINED ROWS選項的ANALYZE語句，檢視錶或簇中連結的或遷移的行。這條語句的執行結果儲存在制定的表中，該表被明確得建立，以便直接接收由LIST CHAINED ROWS子句返回的值。
 

1、建立CHAINED_ROWS表
    建立用於接收由ANALYZE LIST CHAINED ROWS語句返回的資料的表，執行'D:\oracle\ora92\rdbms\admin\utlchain.sql'(這個指令碼其實就是一個簡單的table建立語句)。

    建立之後，使用ANALYZE語句的語法如下：
    ANALYZE CLUSTER emp_dept CHAINED ROWS INTO CHAINED_ROWS;

2、刪除表中的遷移或連結行
    使用CHAINED_ROWS表中的資訊，可減少或刪除現存表中的遷移或連結行，步驟如下：

    ①使用ANALYZE語句收集遷移或連結行資訊
    ANALYZE TABLE order_hist LIST CHAINED ROWS;

    ②查詢輸出表
    SELECT * FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST';
    在輸出結果中會顯示遷移或者連結的所有行

    ③如果輸出表顯示出有許多遷移或連結行，則開始執行刪除遷移行： 

    ④建立一個與現存表相同列的中間表，以便保留遷移或連結行
    CREATE TABLE int_order_hist AS SELECT * FROM order_hist WHERE ROWID IN
           (SELECT HEAR_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST');

    ⑤從現存的表中刪除遷移或連結行
    DELETE FROM order_hist WHERE ROWID IN
           (SELECT HEAR_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST');

    ⑥把中間表中的行插入到現存表中
    INSERT INTO order_hist SELECT * FROM int_order_hist;

    ⑦刪除中間表
    DROP TABLE int_order_hist;

    ⑧從輸出表中刪除步驟1所收集的資訊
    DELETE FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST';

    ⑨再次使用ANALYZE語句，查詢輸出表

    ⑩再次輸出表中的所有行都是連結行，通過增加資料塊大小就能消除連結行。但是很多情況下，連結問題不可避免。

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