36-Oracle Statistics Gathering（統計信息收集）

小伙伴們，有沒有因為統計信息不準，導致了業務卡頓，各種狀況頻出，這幾天在實踐和實操的過程中，時不時就需要進行統計信息的收集。同時統計信息收集的動作也是OCM必考內容。

數據庫中的數據是地圖，統計信息是導航儀，而優化器則是駕駛策略的制定者，CBO依賴統計信息做出最優成本和路徑選擇。

一、統計信息功能

在Oracle數據庫管理中，統計信息收集（Statistics Gathering）是非常非常重要的環節，直接影響著數據庫的性能優化和查詢效率。

優化查詢性能：

數據庫優化器使用統計信息來評估執行計劃的成本，選擇最優的執行路徑。可以根據列的分布、表的大小、索引的選擇性等信息來決定是全表掃描還是索引掃描。

自動調整執行計劃：

統計信息幫助數據庫自動調整執行計劃。隨著數據的變化，統計信息會更新，這可以確保數據庫持續使用最優的執行策略。避免因數據分布變化導致的執行計劃突變（Plan Flip）

提高查詢的準確性：

統計信息提供了關于數據的精確度，這對于估算查詢結果的行數非常關鍵。在執行聚合查詢（如COUNT、SUM等）時，準確的統計信息可以確保返回的結果更加準確。

改善成本估算：

數據庫優化器通過統計信息來估算各種操作的成本，如掃描行數、連接操作的開銷等。這些估算用于選擇最佳的查詢執行計劃。減少不必要的I/O和CPU消耗，降低全表掃描風險

支持分區和物化視圖：

對于使用分區表和物化視圖的數據庫設計，統計信息對于優化器的決策至關重要。分區表的選擇性統計可以幫助優化器更有效地決定使用哪個分區。

支持高級功能：

同樣支持高級功能，自動SQL調優、數據倉庫優化器這些也將依賴于準確的統計信息來提供最佳的性能。

二、收集方式

1. ?自動收集機制?

Oracle通過GATHER_STATS_JOB自動任務實現智能收集：

?觸發條件?：當DBA_TAB_MODIFICATIONS中記錄的DML量 > 表行數的10%時標記為陳舊（Stale）
?時間窗口?：默認工作日晚10點-早6點及周末全天
?優先級策略?：先處理缺失統計信息對象，再處理陳舊度高的對象

--啟用命令BEGINDBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation   => NULL,window_name => NULL);
END; 
/
--PL/SQL procedure successfully completed.
--直接用表名和用戶名可以做粗略收集，其他按照列等選項收集，需要細化
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('用戶名');
EXEC DBMS_STATS.GATHER_TABLE_STATS('用戶名', '表名');

2. ?手動收集原則?

以下場景需手動干預：

?ETL作業后?：避免優化器使用陳舊信息
?數據分布傾斜?：存在極端值的列需特殊處理
?性能敏感對象?：核心業務表結構變更后
導出到導入：必要收集統計信息

三、統計信息的要點

下面用SH Schema驗證，統計信息直接決定分區表SALES、大表CUSTOMERS的查詢效率：

?優化器決策依據?統計信息為CBO提供數據分布特征。舉例：

-- 查看SALES表統計信息（關鍵字段）
SELECT num_rows, blocks, avg_row_len 
FROM dba_tables 
WHERE owner='SH' AND table_name='SALES';
--NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- -----------0          0           0

若num_rows嚴重偏離實際值（如因數據遷移未更新），優化器可能錯誤選擇全表掃描而非分區裁剪。

?資源消耗優化?準確的索引統計信息（如CUSTOMERS_PK的聚簇因子）可避免低效索引掃描：

SELECT clustering_factor 
FROM dba_indexes 
WHERE index_name='CUSTOMERS_PK';
--
CLUSTERING_FACTOR
-----------------00

高聚簇因子（接近表塊數）表明索引效率低，需結合統計信息調整索引策略。?

四、舉例SH Schema的統計信息收集方法

自動收集策略優化

啟用增量收集降低分區表開銷：

-- 開啟SALES表增量統計
EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'INCREMENTAL', 'TRUE');
--PL/SQL procedure successfully completed.
-- 驗證設置
SELECT preference_value 
FROM dba_tab_stat_prefs 
WHERE owner='SH' AND table_name='SALES' AND preference_name='INCREMENTAL';
--效果?：僅收集數據變更的分區。
PREFERENCE_VALUE
--------------------------------------------------------------------------
TRUE

設置用戶SH自動收集任務?

BEGINDBMS_SCHEDULER.CREATE_JOB (job_name        => 'gather_stats_job',job_type        => 'PLSQL_BLOCK',job_action      => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''SH''); END;',start_date      => SYSTIMESTAMP,repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- 例如每天執行一次enabled         => TRUE,comments        => '自動收集統計信息');
END;
/

手動收集關鍵場景

?1. 直方圖精準控制?

對偏斜字段SALES.AMOUNT_SOLD收集等高直方圖：

BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname    => 'SH',tabname    => 'SALES',method_opt => 'FOR COLUMNS SIZE 254 AMOUNT_SOLD', -- 254桶數degree     => 4);
END;
/
--
PL/SQL procedure successfully completed.
SYS@FREE>
-- 驗證直方圖
SELECT column_name, histogram, num_buckets 
FROM dba_tab_cols 
WHERE owner='SH' AND table_name='SALES' AND column_name='AMOUNT_SOLD';
--
COLUMN_NAME    HISTOGRAM       NUM_BUCKETS
______________ ____________ ______________
AMOUNT_SOLD    NONE                      0

?判定?：若HISTOGRAM=HEIGHT BALANCED且NUM_BUCKETS>=100，則有效反映數據分布。?

?2. 分區級統計驗證?

檢測分區SALES_Q4_2001的陳舊狀態--提前確認有分區：?

SELECT partition_name, stale_stats, last_analyzed
FROM dba_tab_statistics
WHERE owner='SH' AND table_name='SALES'AND partition_name='SALES_Q4_2024'AND stale_stats='YES'; -- 陳舊狀態檢測

處理?：若返回記錄，需對該分區單獨收集：?

--提前檢測確認分區
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',partname=>'SALES_Q4_2024');

三、可驗證監控腳本（舉例SH Schema）

準確性驗證

對比COSTS表統計行數 vs 實際行數：

WITH actual AS (SELECT /*+ DYNAMIC_SAMPLING(4) */ COUNT(*) actual_rows FROM sh.costs
)
SELECT t.num_rows "統計行數",a.actual_rows "實際行數",ROUND(ABS((t.num_rows - a.actual_rows)/NULLIF(a.actual_rows,0))*100,2) diff_pct
FROM dba_tables t, actual a
WHERE t.owner='SH' AND t.table_name='COSTS'AND ABS(t.num_rows - a.actual_rows) > 10000; -- 差異>1萬行告警no rows selected

?閾值建議?：diff_pct > 5% 時需手動刷新統計。

自動任務健康監測

檢查自動任務狀態及失敗歷史：

SELECT job_name, enabled, last_start_date,(SELECT COUNT(*) FROM dba_scheduler_job_run_details WHERE job_name='GATHER_STATS_JOB' AND status='FAILED') fail_count
FROM dba_scheduler_jobs 
WHERE job_name='GATHER_STATS_JOB'
UNION ALL
-- 檢查SH模式下統計信息鎖定
SELECT 'STATS_LOCK', NULL, NULL, COUNT(*)
FROM dba_tab_statistics 
WHERE owner='SH' AND locked='YES';
JOB_NAME      ENABLED    LAST_START_DATE       FAIL_COUNT
_____________ __________ __________________ _____________
STATS_LOCK                                           4890

? 處理邏輯?：

fail_count > 0 → 檢查 dba_scheduler_job_log
STATS_LOCK > 0 → 使用 DBMS_STATS.UNLOCK_TABLE_STATS 解鎖。

四、實操體會

在舉例SH Schema中需重點關注：

?分區表增量統計?：降低90%收集開銷，尤其對時間分區字段（如SALES.TIME_ID）。
?偏斜字段直方圖?：對金額/數量等高基數列（AMOUNT_SOLD、QUANTITY_SOLD）定制桶數。
?混合收集策略?：

自動任務處理日常變更
ETL后對SALES/COSTS手動刷新
使用?PENDING STATS 測試：

EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','PUBLISH','FALSE');
--
PL/SQL procedure successfully completed.

TIPS：

通過統計信息收集，可精準驗證統計信息對查詢優化的實際影響，實現從理論到高效運維的閉環
在進行大量數據加載或數據修改后，及時重新收集統計信息是非常重要的，以確保優化器能基于最新數據做出正確的決策。
在生產環境中，建議定期監控統計信息的準確性和完整性，必要時進行手動或自動的調整和重新收集
過度頻繁地收集統計信息可能會影響系統性能，因為這會增加數據庫的負載。因此，應根據實際需要平衡收集頻率和系統性能。