什麼是統計資訊

統計資訊主要是描述資料庫中表，索引的大小，規模，資料分佈狀況等的一類資訊。比如，表的行數，塊數，平均每行的大小，索引的leaf blocks，索引欄位的行數，不同值的大小等，都屬於統計資訊。CBO正是根據這些統計資訊資料，計算出不同訪問路徑下，不同join 方式下，各種計劃的成本，最後選擇出成本最小的計劃。

統計資訊是存放在資料欄位表中的，如dba_tab_statistics

如何蒐集統計資訊

統計資訊蒐集也是有多種方法，推薦大家使用DBMS_STATS 表來進行統計資訊蒐集及進行一般的統計資訊維護工作。

DBMS-STATS 包，主要提供了蒐集，刪除，匯出，匯入，修改統計資訊的方法，分別對應於gather系列，delete系列，export 系列，import系列，set系列的子過程。一般可能主要是使用統計資訊的蒐集，以及匯出匯入這樣的功能。具體來說，主要會使用到如下幾個子過程：

GATHER_INDEX_STATS Procedure

Gathers index statistics.

GATHER_TABLE_STATS Procedure

Gathers table and column (and index) statistics.

CREATE_STAT_TABLE Procedure

Creates a table with name stattab in ownname's schema which is capable of holding statistics.

EXPORT_TABLE_STATS Procedure

Retrieves statistics for a particular table and stores them in the user stat table.

EXPORT_SCHEMA_STATS Procedure

Retrieves statistics for all objects in the schema identified by ownname and stores them in the user stat table identified by stattab.

IMPORT_INDEX_STATS Procedure

Retrieves statistics for a particular index from the user stat table identified by stattab and stores them in the dictionary.

IMPORT_TABLE_STATS Procedure

Retrieves statistics for a particular table from the user stat table identified by stattab and stores them in the dictionary.

IMPORT_SCHEMA_STATS Procedure

Retrieves statistics for all objects in the schema identified by ownname from the user stat table and stores them in the dictionary.

對於統計資訊的蒐集，談談個人的幾點理解：

? 統計資訊預設是存放在資料字典表中的，也只有資料字典中的統計資訊，才會影響到CBO。

? DBMS_STATS 提供的CREATE_STAT_TABLE 過程，只是生成一個使用者自定義的特定格式的表，用來存放統計資訊罷了，這個表中的統計資訊是不會影響到統計資訊的。

? GATHER 系列過程中，如果指定stattab，statid，statown 引數（也可以不指定），則是蒐集的統計資訊除了更新到資料字典外，還在statown 使用者下的stattab 表中存放一份，標示為 statid;

? EXPORT和IMPORT 系列的過程中,stattab,statid,statown 引數不能為空，分別表示把資料字典中的當前統計資訊匯出到使用者自定義的表中，以及把使用者表中的統計資訊匯入到資料字典中，很明顯可以看出，這裡的匯入操作和上面GATHER 操作會改變統計資訊，可能會引起執行執行計劃的改變，因此要慎重操作。

? 每次統計資訊蒐集前，將舊的統計資訊備份起來是很有必要的；特別是保留一份或多份系統在穩定時期的統計資訊也是很有必要的。

? 多長時間蒐集一次統計資訊，對於統計資訊如何備份和保留，蒐集統計資訊時如何選擇合適的取樣，並行，直方圖設定等都比較重要，需要設計一個較好的統計資訊蒐集策略。

統計資訊包括下面幾類：

表統計：包括記錄數、block數和記錄平均長度。

列統計：列中不同值的數量（NVD）、空值的數量和資料分佈（HISTOGRAM）。

索引統計：索引葉塊的數量、索引的層數和聚集因子（CLUSTERING FACTOR）。

系統統計：I/O效能和利用率和CPU效能和利用率。

生成統計資訊：

統計資訊生成技術包括三種：

基於資料取樣的估計方式

精確計算方式

使用者自定義的統計資訊收集方式

其中採用估算方式可以指定總記錄數的估算百分比或者總塊數的估算百分比。

分割槽表的統計資訊分為幾級：分割槽表的整體資訊、分割槽的統計資訊和子分割槽的統計資訊。

最常用的收集統計資訊的方式包括：DBMS_STATS包和ANALYZE語句，Oracle推薦使用DBMS_STATS包來收集統計資訊。

DBMS_STATS包中用於收集統計資訊的過程包括：

dbms_stats.gather_table_stats 收集表、列和索引的統計資訊；

dbms_stats.gather_schema_stats 收集SCHEMA下所有物件的統計資訊；

dbms_stats.gather_index_stats 收集索引的統計資訊；

dbms_stats.gather_system_stats 收集系統統計資訊。

dbms_stats.delete_table_stats 刪除表的統計資訊

dbms_stats.export_table_stats 輸出表的統計資訊

dbms_stats.create_state_table

dbms_stats.set_table_stats 設定表的統計

dbms_stats.auto_sample_size

dbms_stats.gather_database_stats：收集資料庫中所有物件的統計資訊；

在CREATE INDEX和ALTER INDEX REBUILD時可以指定COMPUTE STATISTICS語句，對於非分割槽表重建索引時會收集表、列和索引的統計資訊。對於分割槽表，只收集索引資訊，不會收集表和列資訊。

可以在將METHOD_OPT引數設定為“FOR ALL HIDDEN COLUMNS SIZE N”來收集函式索引的索引表示式資訊。

Oracle根據下列條件來決定使用哪些索引：

索引中的記錄數；

索引中不同鍵值的數量；

索引的層數；

索引中的葉塊數；

聚集因子；

每個鍵值平均葉塊數；

如果兩個索引的選擇性、查詢代價和集勢都相同，那麼最佳化器會根據索引名稱的字母順序選

使用analyze命令收集oracle統計資訊

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是：估算的抽樣百分比。

當ESTIMATE_PERCENT引數是手動指定的，如果手動指定的引數過小，不能收集到足夠的資訊，那麼DBMS_STATS可能會自動增長ESTIMATE_PERCENT的值，這樣就能確保收集到足夠的統計資訊。

控制取樣的引數是ESTIMATE_PERCENT，取樣的引數可以設定任意值(當然要在範圍內)，不過ORACLE公司推薦設定ESTIMATE_PERCENT為DBMS_STATS.AUTO_SAMPLE_SIZE。

AUTO_SAMPLE_SILE可以讓ORACLE自己決定最好的取樣值，因為不同型別(table,index,column)的統計資訊有不同的需求。

METHOD_OPT是：有以下選項，

FOR TABLE /*只統計表*/

[FOR ALL [INDEXED] COLUMNS] [SIZE N] /*只統計有索引的表列*/

FOR ALL INDEXES /*只分析統計相關索引*/

引數METHOD_OPT控制柱狀圖的收集。ORACLE公司推薦設定METHOD_OPT為FOR ALL COLUMNS SIZE AUTO。這樣設定過後ORACLE會自動的判斷哪一列需要收集柱狀圖，並且自動的設定柱狀圖

的bucket。你同樣可以手動的設定哪一列需要收集柱狀圖，以及柱狀圖的bucket。

PARTNAME是：指定要分析的分割槽名稱。

DEGREE:控制DBMS_STATS是否使用並行特徵。

ORACLE公司推薦將DEGREE引數設定為DBMS_STATS.AUTO_DEGREE。這樣設定過後，ORACLE就能夠根據OBJECT的SIZE，以及與並行有關的init引數來決定一個恰當的並行度

收集統計資訊。注意:cluster index,domain index,bitmap join index不能使用並行特徵。

補充

對於分割槽表和分割槽索引，DBMS_STATS既可以單獨的收集分割槽統計資訊，也可以收集整個表/索引的統計資訊。對於組合分割槽，DBMS_STATS也能夠收集子分割槽，分割槽，以及整個表/索引的統計資訊。引數GRANULARITY控制分割槽統計資訊的收集。因為分割槽統計資訊，全域性統計資訊對於大多數系統來說都是非常重要的，所以ORACLE公司推薦將其設定為AUTO來收集分割槽，以及全域性的統計資訊。

當對錶收集統計資訊的時候，DBMS_STATS會收集列的資料分佈資訊。資料分佈最基本的統計資訊就是這個列的最大值與最小值。如果這一列是傾斜的，那麼最佳化器僅僅根據列最大值與最小值是無法制定出準確的執行計劃的。對於傾斜的資料分佈，我們可以收集列的直方圖/柱狀圖統計資訊，這樣可以讓最佳化器制定出更加準確的執行計劃。

為了知道統計資訊是否過期，ORACLE提供了表監控功能。將init引數STATISTICS_LEVEL設定為ALL或者TYPICAL(預設)，就開啟了表監控的功能(10g已經不需要alter table monitor了)。表監控功能跟蹤表的insert,update,delete，truncate，操作，並且記錄在DBA_TAB_MODIFICATIONS檢視裡面。

我們在查詢DBA_TAB_MODIFICATIONS檢視的時候有可能查詢不到結果，或者查詢的結果不準確，這個時候需要用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO過程將記憶體中的資訊重新整理到該檢視中。

OPTIONS引數設定為GATHER STALE或者GATHER AUTO，就會讓DBMS_STATS判斷表的統計資訊是否過期

(注意GATHER_TABLE_STATS中沒有這個引數，只有GATHER_DATABASE_STATS,GATHER_SCHEMA_STATS過程中有這個引數)。

判斷表的統計資訊是否過期的依據是是否有10%以上的資料被修改過，如果被修改過了，那麼ORACLE就認為之前的統計資訊過期了，ORACLE會重新收集統計資訊。

在我們建立了函式索引之後，我們要為列收集統計資訊，這個時候我們需要設定引數METHOD_OPT為FOR ALL HIDDEN COLUMNS。

例子

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的所有表，簇表和索引作統計分析。

收集統計資訊的策略

通常情況下，我們會將ORACLE自動收集統計資訊功能給關閉，我們會採用手動的方式給資料庫收集統計資訊。至於收集統計資訊的策略需要根據系統來確定。下面說說幾種常見的情況：

如果你係統中的表的資料是增量(有規律)的增加，也就是說你幾乎不做任何的批次處理操作，比如批次刪除，批次載入操作。對於這樣的表收集統計資訊是非常簡單的。你可以透過檢視DBA_TAB_MODIFICATIONS檢視來觀察表的變化情況，觀察表中資料量的變化是否超過了10%,並且記錄下天數。這樣你就可以每隔這樣的時間間隔對其收集一次統計資訊。你可以用CRONTAB,或者JOB呼叫GATHER_SCHEMA_STATS或者GATHER_TABLE_STATS過程來收集統計資訊。

對於經常批次操作的表，那麼表的統計資訊就必須在批次操作之後對其收集統計資訊。

對於分割槽表，通常只有一個分割槽被修改，這種情況下可以只收集單獨分割槽的統計資訊，不過收集整個表的統計資訊還是非常有必要的。

Oracle收集統計資訊

相關文章