Oracle 效能調校：統計資訊跨環境遷移實作指南

測試環境無法完整模擬生產資料量分布，導致執行計畫偏差。將生產環境的最佳化統計資訊匯入測試環境，是重現效能問題、驗證調校方案的關鍵技術。本文提供標準作業流程。

遷移架構

生產環境 (Source)          測試環境 (Target)
     │                            │
     ▼                            ▼
GATHER_SCHEMA_STATS         IMP 匯入統計表
     │                            │
     ▼                            ▼
CREATE_STAT_TABLE    ←←←←  EXP 匯出統計表
     │                            │
     ▼                            ▼
EXPORT_SCHEMA_STATS         IMPORT_SCHEMA_STATS

生產環境操作步驟

-- 1. 收集統計資訊 (採樣 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. 使用 exp 匯出暫存表
$ exp citictest/citictest@colm2 tables=STATISTICS file=statistics.dmp log=statistics_export.log

測試環境操作步驟

-- 1. 使用 imp 匯入暫存表
$ imp test/test@colm2 tables=STATISTICS file=statistics.dmp log=statistics_import.log ignore=y

-- 2. 更新暫存表中的擁有者名稱 (關鍵步驟！)
UPDATE STATISTICS SET C5 = 'TEST';
COMMIT;

-- 3. 匯入統計資訊至資料字典
BEGIN
  DBMS_STATS.IMPORT_SCHEMA_STATS('TEST', 'STATISTICS', NULL, NULL, NULL);
END;
/

-- 4. 驗證統計資訊
SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED, BLOCKS, AVG_ROW_LEN
FROM USER_TABLES
ORDER BY TABLE_NAME;

關鍵注意事項

• 擁有者名稱同步：統計表 C5 欄位儲存 Schema 名稱，跨 Schema 匯入時**必須更新**，否則統計資訊不會生效
• 物件結構一致：目標環境表格結構（欄位、索引、分區）必須與來源一致，否則統計資訊可能導致更差計畫
• 版本相容性：跨大版本遷移（如 11g → 19c）需確認統計表結構相容，必要時用 DBMS_STATS.UPGRADE_STAT_TABLE
• 鎖定統計資訊：驗證無誤後，可對關鍵表執行 DBMS_STATS.LOCK_TABLE_STATS 防止自動收集覆蓋
• 系統統計資訊：同步匯入 DBMS_STATS.EXPORT_SYSTEM_STATS / IMPORT_SYSTEM_STATS，確保 CPU/IO 成本模型一致

自動化腳本建議

將上述步驟封裝為 Shell + PL/SQL 混合腳本，納入 CI/CD 流程，定期同步生產統計資訊至測試/UAT 環境，確保效能測試的代表性。

統計資訊遷移是「無資料重現效能問題」的核心技術，掌握此技能能大幅提升 SQL 調校驗證的準確度。