oracle統計資訊(一)

1.     理解什麼是統計資訊
    優化器統計資訊就是一個更加詳細描述資料庫和資料庫物件的集合，這些統計資訊被用於查詢優化器，讓其為每條SQL語句選擇最佳的執行計劃。優化器統計資訊包括:

 

表的統計資訊

行數
Block數

行平均長度
列的統計資訊

列中不同值的數量
列中null的數量

資料分佈(柱狀圖/直方圖)

索引的統計資訊

葉子塊的數量
索引的高度

聚簇因子(clustering factor)

系統的統計資訊

I/O效能和利用

CPU效能和利用

 

優化器統計資訊儲存在下列資料字典中
·         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

·         INDEX_STATS              儲存ANALYZE ..VALIDATE STRUCTURE統計資訊

·         AUX_STATS$               儲存CPU統計資訊

·         X$KCFIO                  儲存I/O統計資訊

 

因為資料庫中的物件會經常的變化，所以統計資訊必須有規律的更新以便更加準確的描述這些資料庫物件。統計資訊預設是由ORACLE自動維護的，不過我們也可以用DBMS_STATS包手動收集統計資訊。DBMS_STATS包同樣提供了過程來維護統計資訊。關於DBMS_STATS包更詳細的描述請參閱官方文件PL/SQL Packages and Types Reference部分。
2. 自動收集統計資訊
    Oracle10g中，在安裝Oracle的時候，就預設建立了一個名為GATHER_STATS_JOB的job來自動收集優化器統計資訊。這個job收集資料庫中所有物件的統計資訊。預設的情況下這個job是週一到週五每天晚上10點到第二天早上6點以及整個週末來收集統計資訊。

Job資訊記錄在下列檢視中：

DBA_SCHEDULER_JOBS DBA_SCHEDULER_PROGRAMS

DBA_SCHEDULER_WINDOWS

DBA_SCHEDULER_JOB_RUN_DETAILS

自動收集過期的統計資訊依賴於表監控特徵，在Oracle10g中表監控預設是開啟的，同時它也依賴STATISTICS_LEVEL引數的值，10g中預設為typical,只有將STATISTICS_LEVEL引數設定為ALL或者TYPICAL才能讓ORACLE識別過期的統計資訊。

3. 關閉自動收集統計資訊
在某些情況下，我們想關閉自動收集統計資訊那麼我們可以利用如下方法:

   BEGIN

      DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

   END;

   /

4. 何時該手動收集統計資訊

有時候自動收集統計並不合適，因為自動收集統計資訊是在午夜執行的，然而由於物件是在白天被修改了，導致導致的統計資訊變得陳舊

 

這裡有2種這類物件:

白天經常被delete,或者truncated之後又rebuild的表(經常變化的表)

批量操作之後有10%或者以上的資料被更改的表(批量處理的表)

對於經常變化的表，可以將其統計資訊設定為null，當ORACLE遇到一個表沒有統計資訊，ORACLE會動態取樣以便為查詢優化器收集必要的統計資訊。

 

動態取樣這個特徵受到引數optimizer_dynamic_sampling的控制，它的預設值為2，同時呢optimizer_mode也能控制動態取樣，可將其設定為all.

 

以SCOTT使用者下的DEPT表為例，將一個表的統計資訊設定為null的方法如下：

BEGIN 

DBMS_STATS.DELETE_TABLE_STATS('SCOTT','DEPT'); 

DBMS_STATS.LOCK_TABLE_STATS('SCOTT','DEPT');

END;

/

 

我們也可以在表具有典型的，代表性的時候收集統計資訊，並且鎖住其統計資訊，因為在夜晚自動收集的統計資訊未必適用於白天的負載，而典型的統計資訊具有代表意義，所以這個時候採取lock其典型的統計資訊更能讓CBO選擇更優的執行計劃。

至於上面的兩種方法用哪種，這個還需要根據業務，實際情況分析之。

對於批量處理的表 ，應該在批量處理完成的時候立即對其收集統計資訊，可以將收集統計資訊的指令碼繫結到批量處理的指令碼中。

對於外部表，只能通過gather_table_stats過程來收集統計資訊，並且外部表不支援取樣，所以需要把gather_table_stats中的estimate_percent設定為null。

系統的統計資訊也需要手動收集，因為這些資訊是不會自動收集的。

對於固定物件，比如說動態效能表，需要手動的執行gather_fixed_objects_stats過程來收集。固定的物件反映了當前資料庫的活動。當資料庫活動處於具有代表性的時候，就應該收集這類統計資訊。