Oracle 收集統計資訊

 什麼是統計資訊 

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

統計資訊是存放在資料字段表中的，如tab$。

一般我們從資料字段視圖中察看統計資訊狀況，

table statistics

DBA_TABLES and DBA_TAB_[SUB]PARTITIONS

Used to determine:

Table and (sub)partition access cost

 Join cardinality 

 Join order

Some of the statistics generated are:

 Row count (NUM_ROWS)

 Block count (BLOCKS) Exact

index statistics

Used to decide:

Full table scan versus index scan

Statistics generated are:

B*-tree level (BLEVEL) Exact

Leaf block count (LEAF_BLOCKS)

Clustering factor (CLUSTERING_FACTOR)

Distinct keys (DISTINCT_KEYS)

column statistics

DBA_TAB_COL_STATISTICS and DBA_TAB_HISTOGRAMS

DBA_[SUB]PART_COL_STATISTICS

DBA_[SUB]PART_HISTOGRAMS

 Count of distinct values of the column 

(NUM_DISTINCT)

 Low value (LOW_VALUE) Exact

High value (HIGH_VALUE) Exact

Number of NULLS (NUM_NULLS)

system statistics

Informs the CBO about the relative CPU and I/O

system statistics are stored in the aux_stats$ table

characteristics of the system

dbms_stats package:

gather_system_stats

get_system_stats

set_system_stats

delete_system_stats

import_system_stats

export_system_stats

--補充

--dba_tables 

NUM_ROWS* NUMBER Number of rows in the table 

BLOCKS* NUMBER Number of used data blocks in the table 

EMPTY_BLOCKS* NUMBER Number of empty (never used) data blocks in the table 

AVG_SPACE* NUMBER Average amount of free space, in bytes, in a data block allocated to the table 

CHAIN_CNT* NUMBER Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID 

AVG_ROW_LEN* NUMBER Average length of a row in the table in bytes 

LAST_ANALYZED DATE Date on which this table was most recently analyzed 

--dba_indexes 

BLEVEL* NUMBER B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same. 

LEAF_BLOCKS* NUMBER Number of leaf blocks in the index 

DISTINCT_KEYS* NUMBER Number of distinct indexed values. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table (USER_TABLES.NUM_ROWS) 

AVG_LEAF_BLOCKS_PER_KEY* NUMBER Average number of leaf blocks in which each distinct value in the index appears, rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always 1. 

AVG_DATA_BLOCKS_PER_KEY* NUMBER Average number of data blocks in the table that are pointed to by a distinct value in the index rounded to the nearest integer. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns. 

CLUSTERING_FACTOR* NUMBER Indicates the amount of order of the rows in the table based on the values of the index. 

• If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks. 

• If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks 

NUM_ROWS NUMBER Number of rows in the index 

SAMPLE_SIZE NUMBER Size of the sample used to analyze the index 

LAST_ANALYZED DATE Date on which this index was most recently analyzed 

如何搜集統計資訊 

統計資訊搜集也是有多種方法，推薦大家使用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 ANALYZE.LOG

    @ANALYTAB.SQL

    @ANALYIND.SQL

    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 analyLog.log

@@analyTab.sql

@@analyIdx.sql

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 ( 

    METHOD_OPT VARCHAR2 DEFAULT NULL ) ; 

    DBMS_UTILITY.ANALYZE_DATABASE ( 

    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過程來收集統計資訊。

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

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

鎖住/解鎖統計資訊

·         LOCK_SCHEMA_STATS

·         LOCK_TABLE_STATS

·         UNLOCK_SCHEMA_STATS

·         UNLOCK_TABLE_STATS