Oracle 統計資訊跨環境遷移實戰：生產庫導出至測試庫標準化流程

場景：為何需要遷移統計資訊？

測試環境資料量與分佈往往與生產環境差異巨大，導致 CBO 產生錯誤執行計劃。將生產庫統計資訊遷移至測試庫，可在不複製海量資料的前提下，讓測試庫優化器「看見」生產級資料特徵，驗證 SQL 計劃穩定性。

標準化操作步驟 (Data Pump / DBMS_STATS)

第一階段：生產庫採集與導出

-- 1. 採集 Schema 統計資訊 (10% 採樣)
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS('CITICTEST', 10);
END;
/

-- 2. 建立統計資訊暫存表
BEGIN
  DBMS_STATS.CREATE_STAT_TABLE(NULL, 'STATISTICS', NULL);
END;
/

-- 3. 導出統計資訊至暫存表
BEGIN
  DBMS_STATS.EXPORT_SCHEMA_STATS('CITICTEST', 'STATISTICS', NULL, NULL);
END;
/

-- 4. 使用 Data Pump 導出暫存表
expdp citictest/citictest@prod tables=STATISTICS directory=DATA_PUMP_DIR dumpfile=statistics.dmp logfile=statistics_export.log

第二階段：測試庫導入與套用

-- 1. 導入暫存表
impdp test/test@test tables=STATISTICS directory=DATA_PUMP_DIR dumpfile=statistics.dmp logfile=statistics_import.log

-- 2. 修正暫存表中的 Schema 名稱 (關鍵步驟)
UPDATE statistics SET c5 = 'TEST';  -- c5 欄位存儲 schema 名
COMMIT;

-- 3. 將統計資訊從暫存表匯入數據字典
BEGIN
  DBMS_STATS.IMPORT_SCHEMA_STATS('TEST', 'STATISTICS', NULL, NULL, NULL);
END;
/

-- 4. 驗證匯入結果
SELECT table_name, num_rows, last_analyzed
FROM user_tables
ORDER BY last_analyzed DESC;

關鍵注意事項

1. 版本相容性：源/目標資料庫版本需相容 (同主版本最佳)，跨版本遷移統計資訊可能失效。
2. 物件結構一致：目標 Schema 必須存在同名表/索引/欄位，否則匯入報錯或產生無效統計。
3. 系統統計資訊：DBMS_STATS.GATHER_SYSTEM_STATS (CPU/IO 校準) 亦需同步遷移 (EXPORT/IMPORT_SYSTEM_STATS)，否則成本計算基準不同。
4. 直方圖：採集時指定 METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO' 保留直方圖，遷移後才能處理傾斜資料。
5. 鎖定統計：遷移後可執行 DBMS_STATS.LOCK_SCHEMA_STATS('TEST') 防止自動採集覆蓋，測試完成後 UNLOCK。

進階：表級/索引級/欄位級遷移

-- 表級
DBMS_STATS.EXPORT_TABLE_STATS / IMPORT_TABLE_STATS

-- 索引級
DBMS_STATS.EXPORT_INDEX_STATS / IMPORT_INDEX_STATS

-- 欄位級
DBMS_STATS.EXPORT_COLUMN_STATS / IMPORT_COLUMN_STATS

適用於僅部分需同步統計的精細控制場景。