Oracle統計資訊

Oracle10g的線上文件《Managing Optimizer Statistics》這一章寫的比較經典，雖然關鍵詞、知識點很多，但整個文章結構條理化非常清晰，特別是沒有複雜的英文單詞，總結一下以備後用。

1. 統計資訊

Oracle10g裡的統計資訊按照object和system分類如下:

• Object statistics
  • Table statistics
  • Column statistics
  • Index statistics
• System statistics
  • I/O performance and utilization
  • CPU performance and utilization

2. Object的統計資訊

Oracle裡每個object的統計資訊型別如下：

• Table statistics
  • Number of rows
  • Number of blocks
  • Average row length
• Column statistics
  • Number of distinct values (NDV) in column
  • Number of nulls in column
  • Data distribution (histogram)
• Index statistics
  • Number of leaf blocks
  • Levels
  • Clustering factor

系統級別的統計資訊主要是效能相關的資訊，如下：

• System statistics
  • I/O performance and utilization
  • CPU performance and utilization

3. 獲得統計資訊的途徑

可以通過以下表或檢視查詢統計資訊

• DBA_TABLES
• DBA_OBJECT_TABLES
• DBA_TAB_STATISTICS
• DBA_TAB_COL_STATISTICS
• DBA_TAB_HISTOGRAMS
• DBA_INDEXES
• DBA_IND_STATISTICS
• DBA_CLUSTERS
• DBA_TAB_PARTITIONS
• DBA_TAB_SUBPARTITIONS
• DBA_IND_PARTITIONS
• DBA_IND_SUBPARTITIONS
• DBA_PART_COL_STATISTICS
• DBA_PART_HISTOGRAMS
• DBA_SUBPART_COL_STATISTICS
• DBA_SUBPART_HISTOGRAMS

上面這些記憶性的東西是OCP認證考試喜歡挑選的一些知識點！死記硬背確實有點多，平時用的時候查一下資料就行了。

4. 自動收集統計資訊

Oracle的GATHER_STATS_JOB會自動收集所有object的統計資訊，建立資料庫的時候自動建立GATHER_STATS_JOB。

Oracle是否自動收集統計資訊涉及到一個重要引數STATISTICS_LEVEL，需要注意：

• STATISTICS_LEVEL=TYPICAL/ALL，Oracle自動檢測統計資訊過期並收集
• STATISTICS_LEVEL=BASIC，Oracle不再自動收集統計資訊
• 預設設定為TYPICAL，設定ALL收集所有相關的統計資訊

設定statistics_level引數後，當object一下狀態時，GATHER_STATS_JOB會自動收集統計資訊:

• object沒有統計資訊
• object統計資訊過舊

預設，GATHER_STATS_JOB定製每天晚上22點至凌晨6點執行，週末2天全天執行。stop_on_window_close屬性控制JOB的視窗執行狀態，預設關閉執行視窗，JOB停止收集統計資訊。

常用命令：
---------------------------------------------------------------------------------------------------
檢視GATHER_STATS_JOB
SQL> SELECT owner, job_name, enabled, state FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

停止GATHER_STATS_JOB
SQL>EXEC DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

開啟GATHER_STATS_JOB
SQL>EXEC DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
---------------------------------------------------------------------------------------------------

5. 手工收集統計資訊

一般來說，對於溫和增長的資料變化，Oracle的自動收集統計資訊功能足以滿足需要。什麼時候需要收手動集統計資訊呢？當出現以下情況時，都需要重新收集statistics:

• 某些表在自動統計後被delete、truncate或是rebuild
• 資料增量超過10%，舊的統計資訊將不再有效

簡單地說就是當資料發生明顯改變時，不建議使用automagic statistics gathering，需要根據業務壓力和需要手工進行統計資訊的更新。

常用命令。使用DBMS_STATS包，實現各個Object統計資訊的update、delete、export、import。

Procedure	Collects Statistics
GATHER_INDEX_STATS	Index statistics
GATHER_TABLE_STATS	Table, column, and index statistics
GATHER_SCHEMA_STATS	Statistics for all objects in a schema
GATHER_DICTIONARY_STATS	Statistics for all dictionary objects
GATHER_DATABASE_STATS	Statistics for all objects in a database

6. USER_TAB_MODIFICATIONS

USER_TAB_MODIFICATIONS表記錄了資料庫裡被監控表裡的DML操作次數，記錄更新不夠及時，可以使用Oracle提供的儲存過程進行重新整理。
---------------------------------------------------------------------------------------------------
檢視table的DML操作次數
sql>select table_name,inserts,updates,deletes from user_tab_modifications;

重新整理user_tab_modifications的統計資訊
sql>exec dbms_stats.flush_database_monitoring_info;

清空user_tab_modifications裡表zhangp的統計資訊
sql>analyze table zhangp compute statistics;
---------------------------------------------------------------------------------------------------

這個之前不太瞭解，也是今天才從別人的blog上學習到的。對於效能調優或是測試來說，也可以作為監控熱表的一個途徑。

Oracle10g的統計資訊功能並不是很穩定。我們的生產資料庫從9i升級到10g後，就是沒有充分考慮到gather statistics的功能和策略上的變化，導致OLTP系統晚上做報表分析時速度巨慢，sql語句的執行計劃異常，影響到正常的資料分析。所以對於採用Oracle10g版本的資料庫使用者，無論採用哪種方式統計資訊時一定要慎重測試。