ORACLE的統計分析

統計分析主要包括產生表及索引的統計資訊

表的統計資訊主要包括表的行數，每行的平均長度（位元組），空閒塊，統計時間等資訊
索引的統計資訊主要包括行數、層數、葉塊數、統計時間等資訊。
另外ORACLE還可以統計列及資料不對稱資訊，9i還可以統計系統資訊（CPU，I/O）

ORACLE 執行成本分析時首先取出所應用表及索引的統計資料進行分析，其中資料行數是一個重要的引數，因為ORACLE 在分析表大小時行數為主要引數，如果進行兩個表聯合時，ORACLE會透過分析表的大小，決定應用小表進行全表查詢，而大表執行聯合查詢，這種效能明顯高於先大表進行全表掃描。索引的統計資訊對分析也產生比較大的影響，如ORACLE透過統計可以分析產生多個索引的優先順序及索引的實用性來確定最優的索引策略。ORACLE還可以統計列及資料對稱資訊以產生更精確的分析。如一個表有A欄位的索引，其中A共有兩種值1和0，共10000條記錄，為0的記錄有 10條，為1的記錄有9990條，這時如果沒有進行列資料不對稱的統計資訊，那麼ORACLE對A=0及A=1條件查詢都會進行索引，但實際應用對A=0 的索引效能得到了很大的提高，而A=1的索引反而使效能下降。所以說索引特徵值分析資訊對應用索引產生重大影響，精確的資訊使ORACLE不會使用不應該用的索引。

實際分析:
zl_cbqc和zl_yhjbqk都沒有建立統計資訊，執行如下兩個SQL ORACLE將產生不同的執行計劃。

1. select * from dlyx.zl_cbqc b, dlyx.zl_yhjbqk a where a.qc_bh=b.qc_bh

執行計劃:

SELECT STATEMENT, GOAL = CHOOSE

NESTED LOOPS

TABLE ACCESS FULL DLYX ZL_YHJBQK

TABLE ACCESS BY INDEX ROWID DLYX ZL_CBQC

INDEX UNIQUE SCAN DLYX

2. select * from dlyx.zl_yhjbqk a,dlyx.zl_cbqc b where a.qc_bh=b.qc_bh

執行計劃：

SELECT STATEMENT, GOAL = CHOOSE

NESTED LOOPS

TABLE ACCESS FULL DLYX ZL_CBQC

TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK

INDEX RANGE SCAN DLYX

在對兩個表進行了統計分析後

3. select * from dlyx.zl_yhjbqk a,dlyx.zl_cbqc b where a.qc_bh=b.qc_bh

執行計劃：

SELECT STATEMENT, GOAL = CHOOSE 159 72853 9689449

HASH JOIN 159 72853 9689449

TABLE ACCESS FULL DLYX ZL_CBQC 1 426 19596

TABLE ACCESS FULL DLYX ZL_YHJBQK 157 72853 6338211

4. select * from dlyx.zl_cbqc b,dlyx.zl_yhjbqk a where a.qc_bh=b.qc_bh

執行計劃：

SELECT STATEMENT, GOAL = CHOOSE 159 72853 9689449

HASH JOIN 159 72853 9689449

TABLE ACCESS FULL DLYX ZL_CBQC 1 426 19596

TABLE ACCESS FULL DLYX ZL_YHJBQK 157 72853 6338211

從以上測試可以明顯看出ORACLE的分析結果

第1條SQL與第2條SQL在沒有統計分析的資訊下進行，ORACLE無法進行判斷，只能以規則方法進行分析，根據表的出現先後順序有明顯關係。

第 3條SQL與第4條SQL在有統計分析的資訊下進行，ORACLE分析與表的出現先後順序無關，因為它已經知道了表的資料量並且已經確定返回的資料量基本上是兩個表所有的資料，所以對錶兩個表進行了HASH JOIN （同時取出兩個表的資料然後在記憶體中進行聯合產生返回結果）。

ORACLE資料庫的PL/SQL語句執行的最佳化器，有基於代價的最佳化器（CBO）和基於規則的最佳化器（RBO）。

RBO的最佳化方式，依賴於一套嚴格的語法規則，只要按照規則寫出的語句，不管資料表和索引的內容是否發生變化，不會影響PL/SQL語句的"執行計劃"。

CBO自ORACLE7版被引入，ORACLE自7版以來採用的許多新技術都是隻基於CBO的，如星型連線排列查詢，雜湊連線查詢，反向索引，索引表，分區表和並行查詢等。CBO計算各種可能"執行計劃"的"代價"，即cost，從中選用cost最低的方案，作為實際執行方案。各"執行計劃"的cost的計算根據，依賴於資料表中資料的統計分佈，ORACLE資料庫本身對該統計分佈是不清楚的，須要分析表和相關的索引，才能蒐集到CBO所需的資料。

CBO是ORACLE推薦使用的最佳化方式，要想使用好CBO，使SQL語句發揮最大效能，必須保證統計資料的及時性。

統計資訊的生成可以有完全計演算法和抽樣估演算法。SQL例句如下：

完全計演算法： analyze table abc compute statistics;
抽樣估演算法(抽樣20%)： analyze table abc estimate statistics sample 20 percent;

對錶作完全計算所花的時間相當於做全表掃描，抽樣估演算法由於採用抽樣，比完全計演算法的生成統計速度要快，如果不是要求要有精確資料的話，儘量採用抽樣分析法。建議對錶分析採用抽樣估算，對索引分析可以採用完全計算。

我們可以採用以下兩種方法，對資料庫的表和索引及簇表定期分析生成統計資訊，保證應用的正常效能。

1. 在系統設定定時任務，執行分析指令碼。

在資料庫伺服器端，我們以UNIX使用者oracle，執行指令碼analyze，在analyze中，我們生成待執行sql指令碼，並執行。（假設我們要分析scott使用者下的所有表和索引）

Analyze指令碼內容如下：

sqlplus scott/tiger << EOF
    set pagesize 5000
    set heading off
    SPOOL ANALYTAB.SQL
    SELECT "ANALYZE TABLE SCOTT."||TABLE_NAME||" ESTIMATE STATISTICS SAMPLE 20 PERCENT ;" FROM USER_TABLES；
    SPOOL OFF
    SPOOL ANALYIND.SQL
    SELECT "ANALYZE TABLE SCOTT."||TABLE_NAME||" ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;" FROM USER_TABLES；
    SPOOL OFF
    SPOOL ANALYZE.LOG
    @ANALYTAB.SQL
    @ANALYIND.SQL
    SPOOL OFF
    EXIT

在UNIX平臺上crontab加入，以上檔案，設定為每個月或合適的時間段執行。

或者將如下指令碼儲存成analyze.sql,然後在sqlplus裡面執行：

set pagesize 5000
set linesize 300
set trims on
set heading off
set feedback off
SPOOL analyTab.sql
SELECT 'ANALYZE TABLE ZFMI.'||TABLE_NAME||' COMPUTE STATISTICS ;'

FROM USER_TABLES;
SPOOL OFF
SPOOL analyIdx.sql
SELECT 'ANALYZE TABLE ZFMI.'||TABLE_NAME||' COMPUTE STATISTICS

FOR ALL INDEXES;' FROM USER_TABLES;
SPOOL OFF
SPOOL analyLog.log
@@analyTab.sql
@@analyIdx.sql
SPOOL OFF

2. 利用ORACLE提供的程式包（PACKAGE）對相關的資料庫物件進行分析。

有以下的程式包可以對錶，索引，簇表進行分析。

包中的儲存過程的相關引數解釋如下：

    TYPE可以是：TABLE，INDEX，CLUSTER中其一。
    SCHEMA為：TABLE，INDEX，CLUSTER的所有者，NULL為當前使用者。
    NAME為：相關物件的名稱。
    METHOD是：ESTIMATE，COMPUTE，DELETE中其一，當選用ESTIMATE，
    下面兩項，ESTIMATE_ROWS和ESTIMATE_PERCENT不能同時為空值。
    ESTIMATE_ROWS是：估算的抽樣行數。
    ESTIMATE_PERCENT是：估算的抽樣百分比。
    METHOD_OPT是：有以下選項，
    FOR TABLE /*只統計表*/
    [FOR ALL [INDEXED] COLUMNS] [SIZE N] /*只統計有索引的表列*/
    FOR ALL INDEXES /*只分析統計相關索引*/
    PARTNAME是：指定要分析的分割槽名稱。

1)

    DBMS_DDL.ANALYZE_OBJECT(
    TYPE VARCHAR2,
    SCHEMA VARCHAR2,
    NAME VARCHAR2,
    METHOD VARCHAR2,
    ESTIMATE_ROWS NUMBER DEFAULT NULL,
    ESTIMATE_PERCENT NUMBER DEFAULT NULL,
    METHOD_OPT VARCHAR2 DEFAULT NULL,
    PARTNAME VARCHAR2 DEFAULT NULL ) ;

該儲存過程可對特定的表，索引和簇表進行分析。例如，對SCOTT使用者的EMP表，進行50%的抽樣分析，引數如下：

DBMS_DDL.ANALYZE_OBJECT("TABLE", "SCOTT", "EMP", "ESTIMATE", NULL,50);

2)

    DBMS_UTILITY.ANALYZE_SCHEMA (
    SCHEMA VARCHAR2,
    METHOD VARCHAR2,
    ESTIMATE_ROWS NUMBER DEFAULT NULL,
    ESTIMATE_PERCENT NUMBER DEFAULT NULL,
    METHOD_OPT VARCHAR2 DEFAULT NULL ) ;
    DBMS_UTILITY.ANALYZE_DATABASE (
    METHOD VARCHAR2,
    ESTIMATE_ROWS NUMBER DEFAULT NULL,
    ESTIMATE_PERCENT NUMBER DEFAULT NULL,
    METHOD_OPT VARCHAR2 DEFAULT NULL );

其中，ANALYZE_SCHEMA用於對某個使用者擁有的所有TABLE，INDEX和CLUSTER的分析統計。ANALYZE_DATABASE用於對整個資料庫進行分析統計。

3) DBMS_STATS是在ORACLE8I中新增的程式包，它使統計資料的生成和處理更加靈活方便，並且可以並行方式生成統計資料。在程式包中的以下過程分別分析統計TABLE，INDEX，SCHEMA，DATABASE級別的資訊。

    DBMS_STATS.GATHER_TABLE_STATS
    DBMS_STATS.GATHER_INDEX_STATS
    DBMS_STATS.GATHER_SCHEMA_STATS
    DBMS_STATS.GATHER_DATABASE_STATS

在這裡，我們以資料庫JOB的方式，定時對資料庫中SCOTT模式下所有的表和索引進行分析：

在SQL*PLUS下執行：

    VARIABLE jobno number;
    BEGIN
    DBMS_JOBS.SUBMIT ( :jobno ,
    " dbms_utility.analyze_schema ( "scott", "estimate", NULL, 20) ; ",
    sysdate, "sysdate+30");
    commit;
    end;
    /
    Statement processed.
    Print jobno
    JOBNO
    -------------
    16

以上作業，每隔一個月用DBMS_UTILITY.ANALYZE_SCHEMA對使用者SCOTT的所有表，簇表和索引作統計分析。

dbms_stats包問世以後，Oracle專家可透過一種簡單的方式來為CBO收集統計資料。目前，已經不再推薦你使用老式的分析表和 dbms_utility方法來生成CBO統計資料。那些古老的方式甚至有可能危及SQL的效能，因為它們並非總是能夠捕捉到有關表和索引的高質量資訊。 CBO使用物件統計，為所有SQL語句選擇最佳的執行計劃。

dbms_stats能良好地估計統計資料（尤其是針對較大的分割槽表），並能獲得更好的統計結果，最終制定出速度更快的SQL執行計劃。

下邊給出了dbms_stats的一次示範執行情況，其中使用了options子句。

execdbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
options => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size repeat', -
degree => 15 -
)

為了充分認識dbms_stats的好處，你需要仔細體會每一條主要的預編譯指令（directive）。下面讓我們研究每一條指令，並體會如何用它為基於代價的SQL最佳化器收集最高質量的統計資料。

options引數
使用4個預設的方法之一，這個選項能控制Oracle統計的重新整理方式：

gather——重新分析整個架構（Schema）。
gather empty——只分析目前還沒有統計的表。
gather stale——只重新分析修改量超過10%的表（這些修改包括插入、更新和刪除）。
gather auto——重新分析當前沒有統計的物件，以及統計資料過期（變髒）的物件。注意，使用gather auto類似於組合使用gather stale和gather empty。
注意，無論gather stale還是gather auto，都要求進行監視。如果你執行一個alter table xxx monitoring命令，Oracle會用dba_tab_modifications檢視來跟蹤發生變動的表。這樣一來，你就確切地知道，自從上一次分析統計資料以來，發生了多少次插入、更新和刪除操作。

estimate_percent選項
以下estimate_percent引數是一種比較新的設計，它允許Oracle的dbms_stats在收集統計資料時，自動估計要取樣的一個segment的最佳百分比：
estimate_percent => dbms_stats.auto_sample_size

要驗證自動統計取樣的準確性，你可檢視dba_tables sample_size列。一個有趣的地方是，在使用自動取樣時，Oracle會為一個樣本尺寸選擇5到20的百分比。記住，統計資料質量越好，CBO做出的決定越好。

method_opt選項
dbms_stats的method_opt引數尤其適合在表和索引資料發生變化時重新整理統計資料。method_opt引數也適合用於判斷哪些列需要直方圖（histograms）。

某些情況下，索引內的各個值的分佈會影響CBO是使用一個索引還是執行一次全表掃描的決策。例如，假如在where子句中指定的值的數量不對稱，全表掃描就顯得比索引訪問更經濟。

如果你有一個高度傾斜的索引（某些值的行數不對稱），就可建立Oracle直方圖統計。但在現實世界中，出現這種情況的機率相當小。使用CBO時，最常見的錯誤之一就是在CBO統計中不必要地引入直方圖。根據經驗，只有在列值要求必須修改執行計劃時，才應使用直方圖。

為了智慧地生成直方圖，Oracle為dbms_stats準備了method_opt引數。在method_opt子句中，還有一些重要的新選項，包括 skewonly，repeat和auto：method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'

skewonly選項會耗費大量處理時間，因為它要檢查每個索引中的每個列的值的分佈情況。

假如dbms_stat發現一個索引的各個列分佈得不均勻，就會為那個索引建立直方圖，幫助基於代價的SQL最佳化器決定是進行索引訪問，還是進行全表掃描訪問。例如，在一個索引中，假定有一個列在50%的行中，如清單B所示，那麼為了檢索這些行，全表掃描的速度會快於索引掃描。-- *************************************************************
-- SKEWONLY option—Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;

重新分析統計資料時，使用repeat選項，重新分析任務所消耗的資源就會少一些。使用repeat選項（清單C）時，只會為現有的直方圖重新分析索引，不再搜尋其他直方圖機會。定期重新分析統計資料時，你應該採取這種方式。-- **************************************************************
-- REPEAT OPTION - Only reanalyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only reanalyze histograms for
-- indexes that have histograms.
--**************************************************************begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7
);
end;

使用alter table xxx monitoring;命令來實現Oracle表監視時，需要使用dbms_stats中的auto選項。如清單D所示，auto選項根據資料分佈以及應用程式訪問列的方式（例如透過監視而確定的一個列的工作量）來建立直方圖。使用method_opt=>’auto’類似於在dbms_stats 的option引數中使用gather auto。begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;

並行收集
Oracle允許以並行方式來收集CBO統計資料，這就顯著提高了收集統計資料的速度。但是，要想並行收集統計資料，你需要一臺安裝了多個CPU的SMP伺服器。

更快的執行速度
dbms_stats 是提高SQL執行速度的一種出色機制。透過使用dbms_stats來收集最高質量的統計資料，CBO能夠正確判斷執行任何SQL查詢時的最快途徑。 dbms_stats還在不斷地改進。目前，它的一些令人激動的新特性（自動樣本大小和自動直方圖生成）已經顯著簡化了Oracle專家的工作。

在使用DBMS_STATS分析表的時候，我們經常要儲存之前的分析，以防分析後導致系統效能低下然後進行快速恢復。
首先建立一個分析表，該表是用來儲存之前的分析值。
SQL> begin
2 dbms_stats.create_stat_table(ownname => 'TEST',stattab => 'STAT_TABLE');
3 end;
4 /
PL/SQL 過程已成功完成。
分析表資訊
SQL> BEGIN
2 --DBMS_STATS.delete_table_stats(ownname => 'TEST',tabname => 'A');
3 DBMS_STATS.gather_table_stats(ownname => 'TEST',tabname => 'A');
4 END;
5 /
PL/SQL 過程已成功完成。
匯出表分析資訊到stat_table中。
SQL> BEGIN
2 dbms_stats.export_table_stats(ownname => 'TEST',tabname => 'A',stattab => 'STAT_TABLE');
3 END;
4 /
PL/SQL 過程已成功完成。
SQL>
同理也有
EXPORT_COLUMN_STATS：匯出列的分析資訊
EXPORT_INDEX_STATS：匯出索引分析資訊
EXPORT_SYSTEM_STATS：匯出系統分析資訊
EXPORT_TABLE_STATS：匯出表分析資訊
EXPORT_SCHEMA_STATS：匯出方案分析資訊
EXPORT_DATABASE_STATS：匯出資料庫分析資訊
IMPORT_COLUMN_STATS：匯入列分析資訊
IMPORT_INDEX_STATS：匯入索引分析資訊
IMPORT_SYSTEM_STATS：匯入系統分析資訊
IMPORT_TABLE_STATS：匯入表分析資訊
IMPORT_SCHEMA_STATS：匯入方案分析資訊
IMPORT_DATABASE_STATS：匯入資料庫分析資訊
GATHER_INDEX_STATS：分析索引資訊
GATHER_TABLE_STATS：分析表資訊，當cascade為true時，分析表、列（索引）資訊
GATHER_SCHEMA_STATS：分析方案資訊
GATHER_DATABASE_STATS：分析資料庫資訊
GATHER_SYSTEM_STATS：分析系統資訊
SQL> select count(*) from stat_table;
COUNT(*)
----------
1
刪除分析資訊
SQL> BEGIN
2 DBMS_STATS.delete_table_stats(ownname => 'TEST',tabname => 'A');
3 END;
4 /
PL/SQL 過程已成功完成。
匯入分析資訊
SQL> BEGIN
2 DBMS_STATS.import_table_stats(ownname => 'TEST',tabname => 'A',stattab => 'STAT_TABLE');
3 END;
4 /
PL/SQL 過程已成功完成。

Oracel dbms_jobs包可以提交作業到作業佇列中，這個包中還提供其它一些函式管理以前提交的作業，如對作業進行修改、禁止或刪除等操作。

常用函式：

submit()，提交作業到作業佇列。

isumbit()，

remove()，從作業佇列中刪除作業。

change()，改變已有作業的引數。

what()，改變作業要執行的程式碼。

next_date()，改變作業下一次執行時間。

interval()，改變作業執行的時間間隔。

broken()，暫停執行作業。

run()，強制執行作業。

最常用的submit()函式：

dbms_jobs.submit(

job out binary_integer,

what in varchar2,

next_date in date default sysdate,

interval in varchar default null,

no_parse in boolean default false,

instance in binary_integer default any_instance,

force in boolean default false

);

submit函式引數：

job，作業的作業號，是個輸出引數，所以呼叫submit函式時，這個引數要指定一個已存在的變數。

what，作業要執行的程式碼，一般是儲存過程。

next_date，下一次作業執行的日期。

interval，作業執行的間隔時間，這個引數預設值是null，也就是what指定的程式碼只在next_date這一時間執行一次。

no_parse，預設值是false，當引數值是false，提交作業時就對what指定的程式碼進行語法分析；當引數值是true，第一次執行what指定程式碼時才進行語法分析。

instance，指明執行作業的資料庫例項。

force，如果是true,instance可以是任何正數；如果是false，instance指定的例項必須正在執行。

示例：

var jobno number;

begin

dbms_job.submit(:jobno,'p_xxx_taxis;',trunc(sysdate)+(18/24),'trunc(sysdate+1,''hh'')');

--每天18點執行

commit;

end;

上面這段話要在命令視窗下執行,如在pl/sql developer裡面的--&gt新鍵--&gt命令視窗

這樣就會在每天的18點執行儲存過程p_xxx_taxis。

[總結] Oracle表的分析統計

===========================================================

作者: tolywang(http://tolywang.itpub.net)
發表於:2008.09.03 15:19
分類: Oracle資料庫管理
出處：http://tolywang.itpub.net/post/48/470117
---------------------------------------------------------------

討論一： 使用dbms_stats 還是analyze

自從Oracle8.1.5引入dbms_stats包， Oracle及專家們就推薦使用dbms_stats取代analyze。理由如下：

1. dbms_stats可以並行分析

2. dbms_stats有自動分析的功能(alter table monitor )

3. analyze 分析統計資訊的有些時候不準確

第1,2比較好理解，且第2點實際上在VLDB(Very Large Database)中是最吸引人的；3以前比較模糊，看了metalink236935.1 解釋，analyze在分析Partition表的時候，有時候會計算出不準確的Global statistics 。原因是dbms_stats會實在的去分析表全域性統計資訊（當指定引數）；而analyze是將表分割槽（區域性）的statistics 彙總計算成表全域性statistics ，可能導致誤差。沒有分割槽表的情況下兩個都可以使用(看個人習慣，當然也可以分割槽表使用dbms_stats, 其他使用analyze )。

不過在一些論壇上也有看到dbms_stats 分析之後出現統計資料不準確的情況，而且確實有bug 在dbms_stats 上(可能和版本有關，有待查明)，應該是少數情況，需要我們注意。還有，一般不建議analyze 和dbms_stats 混用。實驗：如果在分割槽表上用dbms_stats統計後，再使用 analyze table 來統計，就會出現表資訊不被更新的問題。刪除統計資訊後再分析就更新了，或者直接用dbms_stats分析。 dbms_stats 目前有遇到的bug例子如下： http://www.itpub.net/viewthread.php?tid=959290&highlight=dbms%5C_stats

dbms_stats包可以分析table、Index或者整個使用者(schema)，資料庫，可以並行分析。

不同版本包有些不一樣， dbms_utility (8i以前的工具包)，dbms_stats (8i或以後提供的工具包) ，具體的dbms_stats 包的眾多功能介紹見後面。

對命令與工具包的一些總結:

1、對於分割槽表，建議使用DBMS_STATS，而不是使用Analyze語句。

a) 可以並行進行，對多個使用者，多個Table

b) 可以得到整個分割槽表的資料和單個分割槽的資料。

c) 可以在不同級別上Compute Statistics：單個分割槽，子分割槽，全表，所有分割槽

d) 可以匯出統計資訊

e) 可以使用者自動收集統計資訊(alter table monitor )

2、DBMS_STATS的缺點：

a) 不能Validate Structure (注意：validate structure 主要在於校驗物件的有效性. compute statistics在於統計相關的資訊) 。

b) 不能收集CHAINED ROWS(行連結), 不能收集CLUSTER TABLE(簇表)的資訊，這兩個仍舊需要使用Analyze語句。

c) DBMS_STATS 預設不對索引進行Analyze，因為預設Cascade是False，需要手工指定為True 。即GATHER_TABLE_STATS：分析表資訊，當cascade為true時，分析表、列（索引）資訊。

Analyze是同時更新表和索引的統計資訊，而dbms_stats會先更新表的統計資訊，然後再更新索引的統計資訊(預設Cascade是False)，這裡就有一個問題，就是當表的統計資訊更新後，而索引的統計資訊沒有被更新，這時候cbo就有可能選擇錯誤的plan 。

3、對於oracle 9裡面的External Table，Analyze不能使用，只能使用DBMS_STATS來收集資訊。

Analyze 命令語法如下 ：

ANALYZE

{ TABLE [ schema.]table

[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]

| INDEX [ schema. ]index

[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]

| CLUSTER [ schema. ]cluster

}

{ COMPUTE [ SYSTEM ] STATISTICS [for_clause]

| ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }]

| validation_clauses

| LIST CHAINED ROWS [ into_clause ]

| DELETE [ SYSTEM ] STATISTICS

} ;

dbms_stats所有的功能包如下：

GATHER_INDEX_STATS：分析索引資訊

GATHER_TABLE_STATS：分析表資訊，當cascade為true時，分析表、列（索引）資訊

GATHER_SCHEMA_STATS：分析方案資訊

GATHER_DATABASE_STATS：分析資料庫資訊

GATHER_SYSTEM_STATS：分析系統資訊

EXPORT_COLUMN_STATS：匯出列的分析資訊

EXPORT_INDEX_STATS：匯出索引分析資訊

EXPORT_SYSTEM_STATS：匯出系統分析資訊

EXPORT_TABLE_STATS：匯出表分析資訊

EXPORT_SCHEMA_STATS：匯出方案分析資訊

EXPORT_DATABASE_STATS：匯出資料庫分析資訊

IMPORT_COLUMN_STATS：匯入列分析資訊

IMPORT_INDEX_STATS：匯入索引分析資訊

IMPORT_SYSTEM_STATS：匯入系統分析資訊

IMPORT_TABLE_STATS：匯入表分析資訊

IMPORT_SCHEMA_STATS：匯入方案分析資訊

IMPORT_DATABASE_STATS：匯入資料庫分析資訊

討論二： analyze 的使用方法 (分割槽表建議使用dbms_stats)

可以參考

Analyze 的三大功能：

蒐集和刪除索引、表和簇的統計資訊驗證表、索引和簇的結構鑑定表和簇的行遷移(migrated rows)和行連結(chained rows)

CBO是Oracle推薦使用的最佳化方式，要想使用好CBO，使SQL語句發揮最大效能，必須保證統計資料的及時性。統計資訊的生成可以有完全計演算法和抽樣估演算法。SQL例句如下：

完全計演算法： analyze table abc compute statistics;
抽樣估演算法(抽樣20%)： analyze table abc estimate statistics sample 20 percent;

對錶作完全計算所花的時間相當於做全表掃描，抽樣估演算法由於採用抽樣，比完全計演算法的生成統計速度要快，如果不是要求要有非常精確的資料的話，儘量採用抽樣分析法。建議對錶分析採用抽樣估算，對索引分析可以採用完全計算。

Analyze 分析table, index等需要的許可權： 必須在你自己的Schema(方案)中或者有ANALYZE ANY 系統許可權。

比如： grant analyze any to tolywang ;

revoke analyze any from tolywang ;

Analyze 使用的侷限及改善：

Analyze 命令每次僅僅能影響到一個table(或index), 如果想透過analyze為整個schema或整個資料庫中的所有表生成統計數字。可以使用analyze的批處理方式(指令碼)。

Analyze 分析命令解析：

ANALYZE

{ TABLE [ schema.]table

[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]

| INDEX [ schema. ]index

[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]

| CLUSTER [ schema. ]cluster

}

{ COMPUTE [ SYSTEM ] STATISTICS [for_clause]

| ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }]

| validation_clauses

| LIST CHAINED ROWS [ into_clause ]

| DELETE [ SYSTEM ] STATISTICS

} ;

INDEX index：對索引進行分析，分析的結果會放在USER_INDEXES, ALL_INDEXES,或 DBA_INDEXES中。一般僅需要對索引進行統計時用到。

分析的內容：

Depth of the index from its root block to its leaf blocks (BLEVEL) 從索引的根塊到其葉塊的索引的深度(級數)。

Number of leaf blocks (LEAF_BLOCKS) 葉塊的數量，這些塊包括了指向表中及索引中行的指標。

Number of distinct index values (DISTINCT_KEYS) 不同索引值的數量。

Average number of leaf blocks for each index value (AVG_LEAF_BLOCKS_PER_KEY) 包括每一個值的記錄的葉塊的平均數。

Average number of data blocks for each index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY) 被一個索引值指向的資料塊的平均數量。

Clustering factor (how well ordered the rows are about the indexed values) (CLUSTERING_FACTOR) 一個簇因子，表明了表中的行的順序和索引中的順序相匹配的緊密程度。

LAST_ANALYZED 為索引生成的統計數字的日期。

TABLE table：對錶進行分析，分析的結果會放在USER_TABLES, ALL_TABLES 和DBA_TABLES檢視中，當為表收集統計數字時，除非以別的方式指明，否則Oracle也為那個表中的索引收集統計數字。還有，在分析表的時候，oracle也會分析基於函式的index所引用的表示式。

分析table產生的內容 (在上面的幾個檢視列中可以找到)：

Number of rows (NUM_ROWS) * 表中行的數量。

Number of data blocks below the high water mark (that is, the number of data blocks that have been formatted to receive data, regardless whether they currently contain data or are empty) (BLOCKS) 高水位一下的資料塊數量(不管是否現在有資料還是空的) 。

* Number of data blocks allocated to the table that have never been used (EMPTY_BLOCKS) 分配給表但未被資料使用的資料塊的數量。

Average available free space in each data block in bytes (AVG_SPACE) 在每一塊中自由空間數量的平均值(以位元組表示)。

Number of chained rows (CHAIN_CNT) 連結行的數量。

Average row length, including the row's overhead, in bytes (AVG_ROW_LEN) 在表中行的平均長度，以位元組表示。

LAST_ANALYZED : 為表生成統計資料的日期。

分析表的限制：

不可以分析資料字典表

不可以分析擴充套件表，但可以用DBMS_STATS來實現這個目的

不可以分析臨時表

不可以計算或估計下列欄位型別：REFs, varrays, nested tables, LOBs (LOBs are not analyzed, they are skipped), LONGs, or object types.

分析分割槽表最好使用DBMS_STATS來實現。

PARTITION | SUBPARTITION：對分割槽表或索引進行分析

CLUSTER cluster: 對簇進行分析，分析的結果會放在ALL_CLUSTERS, USER_CLUSTERS and DBA_CLUSTERS.

compute_statistics_clause

語法：COMPUTE [ SYSTEM ] STATISTICS [for_clause]

對分析對像進行精確的統計，然後把資訊儲存的資料字典中。可以選擇對錶或對欄位進行分析。computed和estimated這兩種方式的統計資料都被最佳化器用來影響sql的執行計劃

如果指定system選項就只統計系統產生的資訊。

for_clause ：

ORACLE的統計分析

ORACLE的統計分析

相關文章