Oracle 效能調優實戰：統計資訊跨環境遷移 (生產→測試) 完整步驟

痛點

測試環境資料量/分布與生產環境差異大，導致執行計劃不一致，生產慢 SQL 無法在測試複現。解法：**遷移生產統計資訊至測試環境**，使優化器「以為」資料分布相同，產生相同計劃。

完整操作腳本

生產環境 (Source)

-- 1. 收集最新統計 (Schema 級，採樣 10%)
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS('CITICTEST', ESTIMATE_PERCENT => 10);
END;
/

-- 2. 建立統計儲存表
BEGIN
    DBMS_STATS.CREATE_STAT_TABLE(OWNNAME => NULL, STATTAB => 'STATISTICS', TBLSPACE => NULL);
END;
/

-- 3. 匯出統計至儲存表
BEGIN
    DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME => 'CITICTEST', STATTAB => 'STATISTICS', STATID => NULL, STATOWN => NULL);
END;
/

-- 4. 使用 exp 導出儲存表
exp citictest/citictest@prod_db TABLES=STATISTICS FILE=statistics.dmp LOG=statistics_export.log

測試環境 (Target)

-- 1. 導入儲存表
imp test/test@test_db TABLES=STATISTICS FILE=statistics.dmp LOG=statistics_import.log IGNORE=Y

-- 2. 修正儲存表中的 Schema 名 (生產→測試用戶名)
UPDATE STATISTICS SET C5 = 'TEST';  -- C5 欄位存儲 OWNER 名稱
COMMIT;

-- 3. 匯入統計至資料字典
BEGIN
    DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME => 'TEST', STATTAB => 'STATISTICS', STATID => NULL, STATOWN => NULL, NO_INVALIDATE => NULL);
END;
/

-- 4. 驗證統計已生效
SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED FROM USER_TABLES ORDER BY TABLE_NAME;

關鍵細節

• CREATE_STAT_TABLE 建立的表結構為 Oracle 內部格式，**不可手工修改結構**，只能更新 C5 (Owner) 欄位。
• NO_INVALIDATE => NULL (預設 TRUE) 避免立即失效游標，平滑過渡；緊急需求可設 FALSE 強制重解析。
• 若生產/測試 **物理結構不同** (分區、索引缺失)，統計遷移後仍可能產生不同計劃，**必須先對齊 DDL 結構**。
• 11g+ 可用 DBMS_STATS.EXPORT/IMPORT_DICTIONARY_STATS 同步字典統計。

驗證成效

-- 對比執行計劃
EXPLAIN PLAN FOR <目標 SQL>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 對比 V$SQL_PLAN / V$SQLSTATS
-- 關鍵指標：COST、CARDINALITY、ACCESS PATH (FTS vs INDEX) 是否一致

總結

「收集 → 建表 → 匯出 → 導入 → 改 Owner → 匯入字典」六步驟，將生產統計資訊無損遷移至測試，是複現生產執行計劃、驗證調優方案的標準作法。