朋友解决一个物化视图刷新时碰到的问题。
数据库版本为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条记录要靠谱多了。
至此，问题得以解决，不过为了避免这种情况的再次发生，最好的办法是将物化视图日志的统计信息收集工作放到物化视图刷新之前进行，这样可以确保物化视图的快速刷新可以得到最精确的统计信息，从而得到最优的执行计划。