生產環境與測試環境的數據量、分佈特徵往往差異巨大,導致執行計劃嚴重偏離,無法在測試環境複現生產性能問題。Oracle 提供 DBMS_STATS 統計信息導出/導入機制,可將生產環境的優化器統計信息「克隆」至測試環境,實現執行計劃高度擬真。本文提供標準化遷移腳本與關鍵注意事項。
-- 1.1 收集目標 Schema 統計信息 (採樣 10%,可調整)
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'CITICTEST',
estimate_percent => 10,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => TRUE,
degree => DBMS_STATS.AUTO_DEGREE
);
END;
/
-- 1.2 建立統計信息儲存表
BEGIN
DBMS_STATS.CREATE_STAT_TABLE(
ownname => NULL, -- 當前用戶
stattab => 'STATISTICS', -- 統計表名
tblspace => NULL -- 默認表空間
);
END;
/
-- 1.3 將統計信息導出至統計表
BEGIN
DBMS_STATS.EXPORT_SCHEMA_STATS(
ownname => 'CITICTEST',
stattab => 'STATISTICS',
statid => NULL,
statown => NULL
);
END;
/
-- 1.4 導出轉儲文件 (使用 expdp 推薦,支援並行/壓縮)
expdp citictest/citictest@prod_db \
TABLES=STATISTICS \
DIRECTORY=DATA_PUMP_DIR \
DUMPFILE=stats_citictest_%U.dmp \
LOGFILE=stats_export.log \
PARALLEL=4 \
COMPRESSION=ALL
-- 2.1 導入轉儲文件至測試環境
impdp test/test@test_db \
TABLES=STATISTICS \
DIRECTORY=DATA_PUMP_DIR \
DUMPFILE=stats_citictest_%U.dmp \
LOGFILE=stats_import.log \
TABLE_EXISTS_ACTION=REPLACE \
REMAP_SCHEMA=CITICTEST:TEST
-- 2.2 關鍵:更新統計表中的擁有者名 (CITICTEST → TEST)
-- 統計表關鍵欄位:C1=OWNER, C2=TABLE_NAME, C3=PARTITION_NAME, C4=SUBPARTITION_NAME, C5=COLUMN_NAME...
-- 但 OWNER 存儲在 C1 (表級) 和 C5 (欄位級/索引級),需批量更新
UPDATE statistics SET c1 = 'TEST' WHERE c1 = 'CITICTEST';
UPDATE statistics SET c5 = 'TEST' WHERE c5 = 'CITICTEST';
COMMIT;
-- 2.3 將統計信息從統計表導入數據字典
BEGIN
DBMS_STATS.IMPORT_SCHEMA_STATS(
ownname => 'TEST',
stattab => 'STATISTICS',
statid => NULL,
statown => NULL,
no_invalidate => FALSE -- TRUE=不立即失效游標,FALSE=立即失效觸發重新解析
);
END;
/
-- 2.4 驗證統計信息已生效
SELECT table_name, num_rows, blocks, last_analyzed, sample_size
FROM user_tables
ORDER BY last_analyzed DESC;
| 風險點 | 影響 | 緩解措施 |
|---|---|---|
| 對象結構不一致 | 導入統計信息失敗或產生錯誤計劃 | 確保測試環境 DDL 與生產完全一致 (含分區、索引、約束) |
| 系統統計信息 (CPU/IO 速率) 差異 | 優化器成本模型計算偏差 | 同步導入系統統計信息:DBMS_STATS.EXPORT_SYSTEM_STATS / IMPORT_SYSTEM_STATS |
| 參數差異 (OPTIMIZER_MODE, OPTIMIZER_FEATURES_ENABLE) | 優化器行為不一致 | 對齊初始化參數,或使用 SQL Plan Baseline 固化計劃 |
| 統計信息過期 | 生產環境數據分佈漂移,舊統計誤導優化器 | 遷移前確認生產 LAST_ANALYZED 新鮮度,必要時重新收集 |
| 密碼文件中統計表擁有者硬編碼 | IMPDP REMAP_SCHEMA 不修改統計表內部 C1/C5 | **必須手動 UPDATE 統計表 C1/C5 欄位**,此為最常見遺漏 |
-- 對比生產與測試環境關鍵 SQL 的執行計劃
SET LINES 200 PAGES 1000
COL plan_table_output FORMAT A120
-- 生產環境執行
EXPLAIN PLAN FOR <關鍵 SQL>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 測試環境執行 (統計遷移後)
EXPLAIN PLAN FOR <關鍵 SQL>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 重點對比:OPERATION、OPTIONS、OBJECT_NAME、ACCESS PREDICATES、FILTER PREDICATES、COST、CARDINALITY
-- 鎖定統計信息防止自動收集任務覆蓋
BEGIN
DBMS_STATS.LOCK_SCHEMA_STATS('TEST');
END;
/
-- 解鎖
BEGIN
DBMS_STATS.UNLOCK_SCHEMA_STATS('TEST');
END;
/
-- 導出統計信息至歷史表 (支援回滾)
BEGIN
DBMS_STATS.EXPORT_SCHEMA_STATS('TEST', 'STATS_HISTORY_20240115', NULL, 'TEST');
END;
/