朋友解決一個(gè)物化視圖刷新時(shí)碰到的問(wèn)題。
數(shù)據(jù)庫(kù)版本為10.2.0.4，一次本地聚集物化視圖的快速刷新執(zhí)行了3個(gè)小時(shí)后出現(xiàn)了臨時(shí)表空間不足的錯(cuò)誤：
ORA-12008: error in materialized view refresh path
ORA-01652: unable to extend temp segment by 32 in tablespace TEMP01
這個(gè)物化視圖以前的刷新是正常的，只是最近偶爾會(huì)出現(xiàn)這個(gè)錯(cuò)誤。上次出現(xiàn)這個(gè)錯(cuò)誤，tb通過(guò)添加臨時(shí)數(shù)據(jù)文件并重啟數(shù)據(jù)庫(kù)解決了問(wèn)題。目前數(shù)據(jù)庫(kù)的臨時(shí)表空間已經(jīng)超過(guò)1T的大小，如果不找到問(wèn)題的原因，僅靠通過(guò)添加臨時(shí)文件顯然是不現(xiàn)實(shí)的。
通過(guò)跟蹤刷新物化視圖的會(huì)話，發(fā)現(xiàn)問(wèn)題確實(shí)出在物化視圖的快速刷新操作上，而會(huì)話的等待事件主要集中在臨時(shí)表空間的寫操作上：direct path write temp。
做了一個(gè)awrsql報(bào)告，檢查了SQL語(yǔ)句和執(zhí)行計(jì)劃。由于這個(gè)SQL本身相對(duì)比較復(fù)雜，這就使得快速刷新的MERGE語(yǔ)句更加復(fù)雜，簡(jiǎn)單格式化后，語(yǔ)句長(zhǎng)度超過(guò)300行，這里就不列出來(lái)了。這個(gè)SQL的執(zhí)行計(jì)劃為：
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
從執(zhí)行計(jì)劃上看出一些疑問(wèn)，MLOG$_DW_YH_JBXX表的結(jié)果只有1行，而實(shí)際上這張表的大小超過(guò)100W。
在默認(rèn)情況下，收集SCHEMA的統(tǒng)計(jì)信息是不會(huì)收集物化視圖日志的，而且即使Oracle收集統(tǒng)計(jì)信息時(shí)可以收集物化視圖日志的統(tǒng)計(jì)信息，對(duì)于當(dāng)前的情況，也無(wú)濟(jì)于事。因?yàn)楫?dāng)前刷新的物化視圖是第一個(gè)嵌套物化視圖，它建立在其他兩個(gè)物化視圖的基礎(chǔ)上，也就是說(shuō)，只有刷新了其他兩個(gè)物化視圖之后，對(duì)應(yīng)的物化視圖日志中才會(huì)有記錄，而其他時(shí)候，物化視圖日志中的記錄都是0。
其實(shí)現(xiàn)在問(wèn)題已經(jīng)確定了，由于物化視圖日志沒(méi)有統(tǒng)計(jì)信息，Oracle認(rèn)為物化視圖日志中記錄很少，產(chǎn)生了一個(gè)最外層為NESTED LOOP的執(zhí)行計(jì)劃，導(dǎo)致刷新效率十分低下。對(duì)于這種情況，其實(shí)可以通過(guò)一次完全刷新來(lái)解決問(wèn)題，但是對(duì)于當(dāng)前的情況，仍然是不可行的。因?yàn)檫@個(gè)物化視圖是數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)中的一個(gè)中間結(jié)果表，下游還有很多物化視圖以及其他系統(tǒng)依賴于當(dāng)前物化視圖的增量數(shù)據(jù)。一旦這個(gè)物化視圖執(zhí)行了完全刷新，就會(huì)導(dǎo)致所有依賴當(dāng)前對(duì)象的下游物化視圖的增量刷新變成了完全刷新。
當(dāng)前快速刷新碰到的問(wèn)題其實(shí)就是Oracle的默認(rèn)策略認(rèn)為物化視圖日志中的數(shù)據(jù)量應(yīng)該遠(yuǎn)小于基表的數(shù)據(jù)量，這樣快速刷新才會(huì)有性能上的優(yōu)勢(shì)，但是當(dāng)前情況下，物化視圖日志的數(shù)據(jù)量和基表的數(shù)據(jù)量處于同一個(gè)數(shù)量級(jí)，因此缺少統(tǒng)計(jì)信息后，快速刷新的執(zhí)行計(jì)劃變得十分的低效。而如果采用完全刷新來(lái)解決當(dāng)前物化視圖的問(wèn)題，那么實(shí)際上是把這個(gè)問(wèn)題擴(kuò)大到下游所有有依賴關(guān)系的物化視圖上。
為了解決這個(gè)問(wèn)題，首先想到的是optimizer_dynamic_samping參數(shù)，通過(guò)設(shè)置會(huì)話級(jí)的參數(shù)，控制物化視圖日志刷新之前，進(jìn)行詳細(xì)的動(dòng)態(tài)統(tǒng)計(jì)信息采樣，使之可以得到一個(gè)適合的執(zhí)行計(jì)劃。
但是將optimizer_dynamic_samping設(shè)置為10后，發(fā)現(xiàn)對(duì)MLOG$_DW_YH_JBXX表不起任何作用，刷新的執(zhí)行計(jì)劃中，MLOG$_DW_YH_JBXX表的行數(shù)仍然為1。
看來(lái)沒(méi)有別的辦法，只有手工顯示的對(duì)物化視圖日志表執(zhí)行統(tǒng)計(jì)信息的收集工作，當(dāng)統(tǒng)計(jì)信息收集完成后，再次運(yùn)行物化視圖的快速刷新，結(jié)果用了不到10分鐘的時(shí)間，物化視圖就刷新成功了。
這次執(zhí)行計(jì)劃變?yōu)椋?/span>
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
可以看到，最外層的執(zhí)行計(jì)劃已經(jīng)變成了HASH JOIN OUTER，而且雖然物化視圖日志MLOG$_DW_YH_JBXX的統(tǒng)計(jì)信息仍然少了2個(gè)數(shù)量級(jí)，但是比原本的1條記錄要靠譜多了。
至此，問(wèn)題得以解決，不過(guò)為了避免這種情況的再次發(fā)生，最好的辦法是將物化視圖日志的統(tǒng)計(jì)信息收集工作放到物化視圖刷新之前進(jìn)行，這樣可以確保物化視圖的快速刷新可以得到最精確的統(tǒng)計(jì)信息，從而得到最優(yōu)的執(zhí)行計(jì)劃。