Oracle 按主鍵刪除極慢（>5秒）根因追踪：物化視圖 ON COMMIT 刷新導致級聯刪除風暴

故障現象

PL/SQL 執行 DELETE FROM t_table1 WHERE id = 23242342; COMMIT; 耗時 **>5 秒**。執行計劃顯示走主鍵索引，Cost 僅 4，但提交階段極慢。

排查過程：10046 事件跟踪定位

1. 開啟會話級跟踪：

   ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

2. 執行目標 DELETE + COMMIT。
3. 關閉跟踪：

   ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

4. 定位 trc 文件：

   SELECT p.value || '/' || i.instance_name || '_ora_' || p.spid || '.trc'
   FROM v$session s, v$process p, v$parameter v, v$instance i
   WHERE s.paddr = p.addr AND s.sid = USERENV('sid')
     AND v.name = 'user_dump_dest';

5. 下載分析 trc（或用 tkprof 格式化）。

鐵證：trc 中出現大量物化視圖刷新語句

/* MV_REFRESH (DEL) */ DELETE FROM "INMS31"."MV_BAND_PORT_REL_AREA"
/* MV_REFRESH (INS) */ INSERT INTO "INMS31"."MV_BAND_PORT_REL_AREA" SELECT ... FROM "TB_BAND_USER_PORT_REL" ...
/* MV_REFRESH (DEL) */ DELETE FROM "INMS31"."MV_BAND_FTTH_REL_AREA"
/* MV_REFRESH (INS) */ INSERT INTO "INMS31"."MV_BAND_FTTH_REL_AREA" SELECT ... FROM ...

**原來：基表刪除觸發兩個物化視圖 ON COMMIT 快速刷新**，每個刷新涉及多表關聯聚合，單次刷新數秒，提交階段同步執行，導致主鍵刪除提交極慢。

解決方案

1. 確認業務：該物化視圖已無查詢依賴（改用其他方式獲取數據）。
2. 刪除物化視圖：

   DROP MATERIALIZED VIEW "INMS31"."MV_BAND_PORT_REL_AREA";
   DROP MATERIALIZED VIEW "INMS31"."MV_BAND_FTTH_REL_AREA";

3. 驗證：刪除後同樣操作耗時 **0.003 秒**，問題徹底解決。

教訓總結

1. 物化視圖慎用 ON COMMIT FAST REFRESH：基表高頻 DML 會嚴重拖慢提交，應改為 ON DEMAND + 定時 Job（低峰期刷新）。
2. PL/SQL 簡單刪除超 1 秒即可疑：必須跟踪會話（10046 / sql_trace / AUTOTRACE）定位等待事件。
3. Oracle 會話跟踪工具箱：
   • AUTOTRACE：SET AUTOTRACE ON 快速看計劃與統計。
   • SQL_TRACE：ALTER SESSION SET SQL_TRACE=TRUE。
   • 10046 EVENT：ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'（含綁定變量、等待事件）。
   • DBMS_SYSTEM.SET_EV：跟踪指定 SID/SERIAL# 會話（DBA 權限）。
   • TKPROF：格式化 trc 為可讀報告。