朋友解決一個物化視圖刷新時碰到的問題。
數據庫版本為10.2.0.4，一次本地聚集物化視圖的快速刷新執行了3個小時后出現了臨時表空間不足的錯誤：
ORA-12008: error in materialized view refresh path
ORA-01652: unable to extend temp segment by 32 in tablespace TEMP01
這個物化視圖以前的刷新是正常的，只是最近偶爾會出現這個錯誤。上次出現這個錯誤，tb通過添加臨時數據文件并重啟數據庫解決了問題。目前數據庫的臨時表空間已經超過1T的大小，如果不找到問題的原因，僅靠通過添加臨時文件顯然是不現實的。
通過跟蹤刷新物化視圖的會話，發現問題確實出在物化視圖的快速刷新操作上，而會話的等待事件主要集中在臨時表空間的寫操作上：direct path write temp。
做了一個awrsql報告，檢查了SQL語句和執行計劃。由于這個SQL本身相對比較復雜，這就使得快速刷新的MERGE語句更加復雜，簡單格式化后，語句長度超過300行，這里就不列出來了。這個SQL的執行計劃為：
Execution Plan
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
0 MERGE STATEMENT 129K(100)
1 MERGE MV_NW_KHXX_YDKH_ALL
2 VIEW
3 NESTED LOOPS OUTER 4 1592 129K (1) 00:38:46
4 VIEW 4 1084 129K (1) 00:38:46
5 TEMP TABLE TRANSFORMATION
6 LOAD AS SELECT
7 VIEW 33 3300 123 (1) 00:00:03
8 WINDOW SORT 33 6600 123 (1) 00:00:03
9 TABLE ACCESS FULL MLOG$_MV_NW_KHXX_YDKH_2 33 6600 122 (0) 00:00:03
10 LOAD AS SELECT
11 VIEW 19151 2244K 3533 (1) 00:01:04
12 WINDOW SORT 19151 4114K 9376K 3533 (1) 00:01:04
13 TABLE ACCESS FULL MLOG$_MV_NW_KHXX_YDKH_1 19151 4114K 3169 (1) 00:00:58
14 SORT GROUP BY 4 1040 125K (1) 00:37:40
15 VIEW 4 1040 125K (1) 00:37:40
16 UNION-ALL
17 HASH JOIN 1 289 21023 (1) 00:06:19
18 HASH JOIN 1 220 20984 (1) 00:06:18
19 TABLE ACCESS FULL MLOG$_DW_YH_JBXX 1 171 20982 (1) 00:06:18
20 VIEW 33 1617 2 (0) 00:00:01
21 TABLE ACCESS FULL SYS_TEMP_0FD9D6744_E9EB0662 33 2013 2 (0) 00:00:01
22 VIEW 19151 1290K 38 (0) 00:00:01
23 TABLE ACCESS FULL SYS_TEMP_0FD9D6745_E9EB0662 19151 1514K 38 (0) 00:00:01
24 HASH JOIN ANTI 1 281 41338 (1) 00:12:25
25 HASH JOIN 1 267 41300 (1) 00:12:24
26 HASH JOIN 1 220 20984 (1) 00:06:18
27 TABLE ACCESS FULL MLOG$_DW_YH_JBXX 1 171 20982 (1) 00:06:18
28 VIEW 33 1617 2 (0) 00:00:01
29 TABLE ACCESS FULL SYS_TEMP_0FD9D6744_E9EB0662 33 2013 2 (0) 00:00:01
30 MAT_VIEW ACCESS FULL MV_NW_KHXX_YDKH_1 9008K 403M 20279 (1) 00:06:06
31 VIEW 19151 261K 38 (0) 00:00:01
32 TABLE ACCESS FULL SYS_TEMP_0FD9D6745_E9EB0662 19151 1514K 38 (0) 00:00:01
33 HASH JOIN ANTI 1 284 21437 (1) 00:06:26
34 HASH JOIN 1 270 21435 (1) 00:06:26
35 HASH JOIN 1 240 21020 (1) 00:06:19
36 TABLE ACCESS FULL MLOG$_DW_YH_JBXX 1 171 20982 (1) 00:06:18
37 VIEW 19151 1290K 38 (0) 00:00:01
38 TABLE ACCESS FULL SYS_TEMP_0FD9D6745_E9EB0662 19151 1514K 38 (0) 00:00:01
39 MAT_VIEW ACCESS FULL MV_NW_KHXX_YDKH_2 283K 8293K 413 (2) 00:00:08
40 VIEW 33 462 2 (0) 00:00:01
41 TABLE ACCESS FULL SYS_TEMP_0FD9D6744_E9EB0662 33 2013 2 (0) 00:00:01
42 HASH JOIN ANTI 1 276 41753 (1) 00:12:32
43 HASH JOIN ANTI 1 262 41714 (1) 00:12:31
44 HASH JOIN 1 248 41711 (1) 00:12:31
45 HASH JOIN 1 201 21396 (1) 00:06:26
46 TABLE ACCESS FULL MLOG$_DW_YH_JBXX 1 171 20982 (1) 00:06:18
47 MAT_VIEW ACCESS FULL MV_NW_KHXX_YDKH_2 283K 8293K 413 (2) 00:00:08
48 MAT_VIEW ACCESS FULL MV_NW_KHXX_YDKH_1 9008K 403M 20279 (1) 00:06:06
49 VIEW 33 462 2 (0) 00:00:01
50 TABLE ACCESS FULL SYS_TEMP_0FD9D6744_E9EB0662 33 2013 2 (0) 00:00:01
51 VIEW 19151 261K 38 (0) 00:00:01
52 TABLE ACCESS FULL SYS_TEMP_0FD9D6745_E9EB0662 19151 1514K 38 (0) 00:00:01
53 MAT_VIEW ACCESS BY INDEX ROWID MV_NW_KHXX_YDKH_ALL 1 127 2 (0) 00:00:01
54 INDEX UNIQUE SCAN I_SNAP$_MV_NW_KHXX_YDKH_AL 1 1 (0) 00:00:01
從執行計劃上看出一些疑問，MLOG$_DW_YH_JBXX表的結果只有1行，而實際上這張表的大小超過100W。
在默認情況下，收集SCHEMA的統計信息是不會收集物化視圖日志的，而且即使Oracle收集統計信息時可以收集物化視圖日志的統計信息，對于當前的情況，也無濟于事。因為當前刷新的物化視圖是第一個嵌套物化視圖，它建立在其他兩個物化視圖的基礎上，也就是說，只有刷新了其他兩個物化視圖之后，對應的物化視圖日志中才會有記錄，而其他時候，物化視圖日志中的記錄都是0。
其實現在問題已經確定了，由于物化視圖日志沒有統計信息，Oracle認為物化視圖日志中記錄很少，產生了一個最外層為NESTED LOOP的執行計劃，導致刷新效率十分低下。對于這種情況，其實可以通過一次完全刷新來解決問題，但是對于當前的情況，仍然是不可行的。因為這個物化視圖是數據倉庫系統中的一個中間結果表，下游還有很多物化視圖以及其他系統依賴于當前物化視圖的增量數據。一旦這個物化視圖執行了完全刷新，就會導致所有依賴當前對象的下游物化視圖的增量刷新變成了完全刷新。
當前快速刷新碰到的問題其實就是Oracle的默認策略認為物化視圖日志中的數據量應該遠小于基表的數據量，這樣快速刷新才會有性能上的優勢，但是當前情況下，物化視圖日志的數據量和基表的數據量處于同一個數量級，因此缺少統計信息后，快速刷新的執行計劃變得十分的低效。而如果采用完全刷新來解決當前物化視圖的問題，那么實際上是把這個問題擴大到下游所有有依賴關系的物化視圖上。
為了解決這個問題，首先想到的是optimizer_dynamic_samping參數，通過設置會話級的參數，控制物化視圖日志刷新之前，進行詳細的動態統計信息采樣，使之可以得到一個適合的執行計劃。
但是將optimizer_dynamic_samping設置為10后，發現對MLOG$_DW_YH_JBXX表不起任何作用，刷新的執行計劃中，MLOG$_DW_YH_JBXX表的行數仍然為1。
看來沒有別的辦法，只有手工顯示的對物化視圖日志表執行統計信息的收集工作，當統計信息收集完成后，再次運行物化視圖的快速刷新，結果用了不到10分鐘的時間，物化視圖就刷新成功了。
這次執行計劃變為：
Execution Plan
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
0 MERGE STATEMENT 141K(100)
1 MERGE MV_NW_KHXX_YDKH_ALL
2 VIEW
3 HASH JOIN OUTER 16997 6606K 4704K 141K (1) 00:42:19
4 VIEW 16997 4498K 129K (1) 00:38:50
5 TEMP TABLE TRANSFORMATION
6 LOAD AS SELECT
7 VIEW 33 3300 123 (1) 00:00:03
8 WINDOW SORT 33 6600 123 (1) 00:00:03
9 TABLE ACCESS FULL MLOG$_MV_NW_KHXX_YDKH_2 33 6600 122 (0) 00:00:03
10 LOAD AS SELECT
11 VIEW 19151 2244K 3533 (1) 00:01:04
12 WINDOW SORT 19151 4114K 9376K 3533 (1) 00:01:04
13 TABLE ACCESS FULL MLOG$_MV_NW_KHXX_YDKH_1 19151 4114K 3169 (1) 00:00:58
14 SORT GROUP BY 16997 4315K 125K (1) 00:37:44
15 VIEW 16997 4315K 125K (1) 00:37:44
16 UNION-ALL
17 HASH JOIN 267 50196 21078 (1) 00:06:20
18 HASH JOIN 191 26549 21076 (1) 00:06:20
19 TABLE ACCESS FULL MLOG$_DW_YH_JBXX 11859 810K 21037 (1) 00:06:19
20 VIEW 19151 1290K 38 (0) 00:00:01
21 TABLE ACCESS FULL SYS_TEMP_0FD9D674D_E9EB0662 19151 1514K 38 (0) 00:00:01
22 VIEW 33 1617 2 (0) 00:00:01
23 TABLE ACCESS FULL SYS_TEMP_0FD9D674C_E9EB0662 33 2013 2 (0) 00:00:01
24 HASH JOIN 16188 2845K 41394 (1) 00:12:26
25 VIEW 33 1617 2 (0) 00:00:01
26 TABLE ACCESS FULL SYS_TEMP_0FD9D674C_E9EB0662 33 2013 2 (0) 00:00:01
27 HASH JOIN RIGHT ANTI 11594 1483K 41391 (1) 00:12:26
28 VIEW 19151 261K 38 (0) 00:00:01
29 TABLE ACCESS FULL SYS_TEMP_0FD9D674D_E9EB0662 19151 1514K 38 (0) 00:00:01
30 HASH JOIN 11594 1324K 41352 (1) 00:12:25
31 TABLE ACCESS FULL MLOG$_DW_YH_JBXX 11859 810K 21037 (1) 00:06:19
32 MAT_VIEW ACCESS FULL MV_NW_KHXX_YDKH_1 9008K 403M 20279 (1) 00:06:06
33 HASH JOIN ANTI 9 1647 21492 (1) 00:06:27
34 HASH JOIN 9 1521 21490 (1) 00:06:27
35 HASH JOIN 191 26549 21076 (1) 00:06:20
36 TABLE ACCESS FULL MLOG$_DW_YH_JBXX 11859 810K 21037 (1) 00:06:19
37 VIEW 19151 1290K 38 (0) 00:00:01
38 TABLE ACCESS FULL SYS_TEMP_0FD9D674D_E9EB0662 19151 1514K 38 (0) 00:00:01
39 MAT_VIEW ACCESS FULL MV_NW_KHXX_YDKH_2 283K 8293K 413 (2) 00:00:08
40 VIEW 33 462 2 (0) 00:00:01
41 TABLE ACCESS FULL SYS_TEMP_0FD9D674C_E9EB0662 33 2013 2 (0) 00:00:01
42 HASH JOIN ANTI 533 93275 41808 (1) 00:12:33
43 HASH JOIN RIGHT ANTI 533 85813 41769 (1) 00:12:32
44 VIEW 33 462 2 (0) 00:00:01
45 TABLE ACCESS FULL SYS_TEMP_0FD9D674C_E9EB0662 33 2013 2 (0) 00:00:01
46 HASH JOIN 533 78351 41766 (1) 00:12:32
47 HASH JOIN 11594 1324K 41352 (1) 00:12:25
48 TABLE ACCESS FULL MLOG$_DW_YH_JBXX 11859 810K 21037 (1) 00:06:19
49 MAT_VIEW ACCESS FULL MV_NW_KHXX_YDKH_1 9008K 403M 20279 (1) 00:06:06
50 MAT_VIEW ACCESS FULL MV_NW_KHXX_YDKH_2 283K 8293K 413 (2) 00:00:08
51 VIEW 19151 261K 38 (0) 00:00:01
52 TABLE ACCESS FULL SYS_TEMP_0FD9D674D_E9EB0662 19151 1514K 38 (0) 00:00:01
53 MAT_VIEW ACCESS FULL MV_NW_KHXX_YDKH_ALL 1701K 206M 3888 (2) 00:01:10
可以看到，最外層的執行計劃已經變成了HASH JOIN OUTER，而且雖然物化視圖日志MLOG$_DW_YH_JBXX的統計信息仍然少了2個數量級，但是比原本的1條記錄要靠譜多了。
至此，問題得以解決，不過為了避免這種情況的再次發生，最好的辦法是將物化視圖日志的統計信息收集工作放到物化視圖刷新之前進行，這樣可以確保物化視圖的快速刷新可以得到最精確的統計信息，從而得到最優的執行計劃。