Oracle 21c XE嵌套物化视图刷新后查询变慢问题排查
2026-7-17
问题描述
在Oracle 21c XE环境中使用嵌套物化视图（MV），核心MV MajorView 的依赖关系如下：
MajorView依赖MV A、C、D- MV C依赖MV A、基础表B
- MV D依赖MV C
- MV E依赖
MajorView
所有MV创建仅需数秒，MajorView 包含约40万行数据，正常情况下查询Q1耗时约0.4秒。但执行以下刷新语句后，Q1查询耗时增至约40秒，性能下降100倍：
Begin DBMS_MVIEW.refresh ('A,B,C,D,MajorView,E','??????',NULL,TRUE,FALSE,1,0,0,TRUE,TRUE); End;
查询USER_MVIEWS显示所有MV状态为FRESH、COMPILE_STATE为VALID，重建MajorView后Q1性能恢复至0.4秒，但再次刷新后问题复现。
执行计划对比
慢查询场景（耗时59秒）
Plan hash value: 1025316596 ----------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 55 |00:00:18.76 | 5543 | | | | |* 1 | FILTER | | 1 | | 55 |00:00:18.76 | 5543 | | | | | 2 | HASH GROUP BY | | 1 | 20 | 28003 |00:00:18.78 | 5543 | 7679K| 2050K| 7329K (0)| |* 3 | HASH JOIN | | 1 | 22988 | 18M|00:00:18.69 | 5543 | 7346K| 2119K| 7790K (0)| |* 4 | HASH JOIN | | 1 | 1050 | 28003 |00:00:00.07 | 3834 | 838K| 838K| 1358K (0)| |* 5 | HASH JOIN | | 1 | 25 | 55 |00:00:00.01 | 56 | 924K| 924K| 1367K (0)| |* 6 | HASH JOIN | | 1 | 25 | 55 |00:00:00.01 | 23 | 1236K| 1236K| 1180K (0)| | 7 | NESTED LOOPS OUTER | | 1 | 9 | 13 |00:00:00.01 | 8 | | | | |* 8 | TABLE ACCESS FULL | CARTEIRA | 1 | 9 | 13 |00:00:00.01 | 6 | | | | | 9 | TABLE ACCESS BY INDEX ROWID| PLGITEMS | 13 | 1 | 1 |00:00:00.01 | 2 | | | | |* 10 | INDEX UNIQUE SCAN | IDX_PLGITEMS_PLGITEM_ID | 13 | 1 | 1 |00:00:00.01 | 1 | | | | |* 11 | TABLE ACCESS FULL | CARTEIRAATIVO | 1 | 820 | 820 |00:00:00.01 | 15 | | | | | 12 | TABLE ACCESS FULL | ATIVO | 1 | 2356 | 2356 |00:00:00.01 | 33 | | | | |* 13 | MAT_VIEW ACCESS FULL | MVW_CARTEIRAATIVOHIST | 1 | 42 | 268K|00:00:00.23 | 3778 | | | | |* 14 | INDEX FAST FULL SCAN | MVW_CARTEIRAATIVOHIST_IDX_GERAL | 1 | 268K| 268K|00:00:00.22 | 1709 | | | | -----------------------------------------------------------------------------------------------------------------------------------------------------------
重建MV后（耗时0.870秒）
Plan hash value: 4205125506 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 55 |00:00:00.15 | 5257 | | | | |* 1 | HASH JOIN | | 1 | 1 | 55 |00:00:00.15 | 5257 | 873K| 873K| 1362K (0)| |* 2 | HASH JOIN | | 1 | 1 | 55 |00:00:00.10 | 5224 | 951K| 951K| 1293K (0)| | 3 | NESTED LOOPS | | 1 | 17 | 55 |00:00:00.04 | 1665 | | | | |* 4 | HASH JOIN | | 1 | 25 | 55 |00:00:00.01 | 23 | 1572K| 1572K| 1159K (0)| | 5 | NESTED LOOPS OUTER | | 1 | 9 | 13 |00:00:00.01 | 8 | | | | |* 6 | TABLE ACCESS FULL | CARTEIRA | 1 | 9 | 13 |00:00:00.01 | 6 | | | | | 7 | TABLE ACCESS BY INDEX ROWID | PLGITEMS | 13 | 1 | 1 |00:00:00.01 | 2 | | | | |* 8 | INDEX UNIQUE SCAN | IDX_PLGITEMS_PLGITEM_ID | 13 | 1 | 1 |00:00:00.01 | 1 | | | | |* 9 | TABLE ACCESS FULL | CARTEIRAATIVO | 1 | 820 | 820 |00:00:00.01 | 15 | | | | | 10 | VIEW PUSHED PREDICATE | VW_SQ_1 | 55 | 1 | 55 |00:00:00.06 | 1642 | | | | |* 11 | FILTER | | 55 | | 55 |00:00:00.06 | 1642 | | | | | 12 | SORT AGGREGATE | | 55 | 1 | 55 |00:00:00.06 | 1642 | | | | |* 13 | MAT_VIEW ACCESS BY INDEX ROWID BATCHED| MVW_CARTEIRAATIVOHIST | 55 | 1 | 28003 |00:00:00.09 | 1642 | | | | | 14 | BITMAP CONVERSION TO ROWIDS | | 55 | | 33267 |00:00:00.07 | 878 | | | | | 15 | BITMAP AND | | 55 | | 55 |00:00:00.05 | 878 | | | | | 16 | BITMAP CONVERSION FROM ROWIDS | | 55 | | 55 |00:00:00.03 | 487 | | | | |* 17 | INDEX RANGE SCAN | MVW_CARTEIRAATIVOHIST_IDX_CART_ID | 55 | 1083 | 182K|00:00:00.14 | 487 | | | | | 18 | BITMAP CONVERSION FROM ROWIDS | | 55 | | 55 |00:00:00.02 | 391 | | | | |* 19 | INDEX RANGE SCAN | MVW_CARTEIRAATIVOHIST_IDX_ATV | 55 | 1083 | 136K|00:00:00.10 | 391 | | | | |* 20 | MAT_VIEW ACCESS FULL | MVW_CARTEIRAATIVOHIST | 1 | 1 | 268K|00:00:00.22 | 3559 | | | | | 21 | TABLE ACCESS FULL | ATIVO | 1 | 1 | 2356 |00:00:00.01 | 33 | | | | -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
核心与相关问题
- 核心问题：为何
MajorView的Q1查询在刷新后变慢？ - 相关问题：
- 该刷新方法与语法是否正确？是否存在刷新层级要求？
- 用MV作为其他MV的数据源是否存在问题？
- Oracle 21c XE是否支持物化视图？我已在使用，但文档未提及。
分析与解答
核心问题原因
对比两个执行计划，慢查询场景中，优化器对MVW_CARTEIRAATIVOHIST采用全表扫描+哈希连接的方式，处理了18M行数据；而重建MV后的快查询场景，优化器使用位图索引组合扫描（BITMAP AND）+批量行ID访问，仅处理必要的28003行数据。
问题根源在于：
- 统计信息失效：刷新操作仅更新MV数据，未自动收集最新统计信息。优化器基于旧统计信息生成低效执行计划，而重建MV时会自动收集统计信息，因此执行计划恢复最优。
- 刷新顺序错误：当前刷新顺序
A,B,C,D,MajorView,E违反依赖层级，正确顺序应为从底层到上层：B→A→C→D→MajorView→E。错误顺序可能导致MV刷新过程中使用未完全更新的依赖数据，虽最终状态为FRESH，但会干扰优化器对数据分布的判断，生成错误执行计划。
相关问题解答
刷新方法与语法：
- 第二个参数
'??????'是无效值，DBMS_MVIEW.refresh的刷新方法参数合法值包括'C'（完全刷新）、'F'（快速刷新）等，无效参数会触发默认行为，引发不可预期问题。 - 必须遵循依赖层级顺序刷新：先刷新被依赖的底层对象，再刷新上层依赖对象，否则会导致中间状态数据异常，影响优化器决策。
- 第二个参数
MV作为其他MV数据源：
Oracle完全支持嵌套物化视图用法，但需满足：底层MV支持快速刷新（若上层MV需快速刷新）、所有MV定义符合嵌套语法要求、刷新严格遵循依赖顺序。该用法本身无问题，问题出在刷新方式和统计信息上。Oracle 21c XE对物化视图的支持：
Oracle Database XE 21c完全支持物化视图功能，属于标准版核心功能范畴，官方文档未明确强调不代表不支持，可正常使用。
内容的提问来源于stack exchange，提问作者JRG
相关产品推荐
相关产品推荐
