Oracle 实时物化视图未按预期工作

你好，

我有一个问题Real-time Materialized View ‘ON QUERY COMPUTATION’功能。

我的实时MV都启用了QUERY REWRITE & ON QUERY COMPUTATION。

据我了解，当MV新鲜时，我们会获得MAT_VIEW重写访问完全运行，这不是因为查询计算而是QUERY REWRITE option which was already there prior to Oracle 12c as well。 This situation work fine for me。

But when the MV is not FRESH, it does a full table scan instead of reading MV + MV Log (& table)。 Due to this, performance becomes bad at this situation。

分析跟踪文件时，在带有文本的跟踪开始处可见错误"Error encountered: ORA-10980"。 This error is not thrown to client & query operation succeeds any how。 However, this error is present only when I set ON QUERY COMPUTATION option for the MV, i。e。 when the MV becomes a Real-time MV。

Also apart from the main select statement, there is another query is visible for this run as below which is related to the corresponding MV Log。

select dmltype$$, max(snaptime$$) from “IFSAPP”。”MLOG$_CUSTOMER_ORDER_LINE_TAB” where snaptime$$ <= :1 group by dmltype$$

我的参数设置:
----------------------
查询 _ 重写 _ 完整性 = 强制执行
查询 _ 重写 _ 启用 = 真

物化视图日志定义:
---------------------------------
使用ROWID (order_no，qty_invoiced，part_price) 在customer_order_line_tab上创建包含新值的物化视图日志;

物化视图定义:
--------------------------
创建物化视图客户 _ 订单 _ 行 _ mv
按需快速刷新
启用查询重写
开启查询计算
作为
SELECT t。order_no, SUM(t。qty_invoiced) 作为 sum_qt, SUM(t。part_price) 作为 sum_pr
从客户 _ 订单 _ 行 _ 标签t
GROUP BY t。order_no;

执行的查询:
--------------
SELECT t。order_no, SUM(t。qty_invoiced) 作为 sum_qt, SUM(t。part_price) 作为 sum_pr
从客户 _ 订单 _ 行 _ 标签t
GROUP BY t。order_no;

请在设置查询计算时找到附件的源跟踪文件 (LiveSQL)

请建议我们在跟踪文件中遇到的错误，为什么查询计算不适用于我的场景？

非常感谢，
纳文思

这仍然是优化器的成本决策，例如

SQL> create table customer_order_line_tab
  2  ( order_no int not null,
  3    qty_invoiced int not null,
  4    part_price number(6,2) not null,
  5    cust_id int not null,
  6    data char(20)
  7  );

Table created.

SQL>
SQL> insert /*+ append */ into customer_order_line_tab
  2  select mod(rownum,1000),
  3         trunc(dbms_random.value(1,10)),
  4         dbms_random.value(1,50),
  5         mod(rownum,20),
  6         rownum
  7  from dual connect by level <= 1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON customer_order_line_tab WITH ROWID (order_no,qty_invoiced,part_price) INCLUDING

Materialized view log created.

SQL>
SQL> drop MATERIALIZED VIEW customer_order_line_mv ;

Materialized view dropped.

SQL>
SQL> CREATE MATERIALIZED VIEW CUSTOMER_ORDER_LINE_MV
  2  REFRESH FAST ON DEMAND
  3  ENABLE QUERY REWRITE
  4  ENABLE ON QUERY COMPUTATION
  5  AS
  6  SELECT t.order_no, SUM(t.qty_invoiced) AS sum_qt, SUM(t.part_price) AS sum_pr--, count(*) as c
  7  FROM customer_order_line_tab t
  8  GROUP BY t.order_no;

Materialized view created.

SQL>
SQL>
SQL> set autotrace traceonly explain
SQL> SELECT t.order_no, SUM(t.qty_invoiced) AS sum_qt, SUM(t.part_price) AS sum_pr
  2  FROM customer_order_line_tab t
  3  GROUP BY t.order_no;

Execution Plan
----------------------------------------------------------
Plan hash value: 2678166370

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |  1000 | 11000 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| CUSTOMER_ORDER_LINE_MV |  1000 | 11000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

SQL> set autotrace off
SQL>
SQL> insert into customer_order_line_tab values (5,7,123.43,5,'x');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> set autotrace traceonly explain
SQL> SELECT t.order_no, SUM(t.qty_invoiced) AS sum_qt, SUM(t.part_price) AS sum_pr
  2  FROM customer_order_line_tab t
  3  GROUP BY t.order_no;

Execution Plan
----------------------------------------------------------
Plan hash value: 1664781565

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                         |  1000 | 11000 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY     |                         |  1000 | 11000 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMER_ORDER_LINE_TAB |  1000 | 11000 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
复制

所以看起来它没有重写... 让我们看看为什么

SQL> begin
  2    DBMS_MVIEW.EXPLAIN_REWRITE('SELECT t.order_no, SUM(t.qty_invoiced) AS sum_qt, SUM(t.part_price) AS sum_pr '||
  3          'FROM customer_order_line_tab t GROUP BY t.order_no','CUSTOMER_ORDER_LINE_MV','x');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> select message from rewrite_table;

MESSAGE
------------------------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01091: cost based optimizer found query rewrite is more expensive
QSM-01209: query rewritten with materialized view, CUSTOMER_ORDER_LINE_MV, using text match algorithm
QSM-01357: query rewritten with a stale materialized view, CUSTOMER_ORDER_LINE_MV, by on query computation
QSM-01029: materialized view, CUSTOMER_ORDER_LINE_MV, is stale in ENFORCED integrity mode

复制

你可以看到-“基于成本的优化器发现查询重写更昂贵”。

让我们用一个更大的源表重复这个练习

SQL> create table customer_order_line_tab
  2  ( order_no int not null,
  3    qty_invoiced int not null,
  4    part_price number(6,2) not null,
  5    cust_id int not null,
  6    data char(20)
  7  );

Table created.

SQL>
SQL> insert /*+ append */ into customer_order_line_tab
  2  select mod(rownum,1000),
  3         trunc(dbms_random.value(1,10)),
  4         dbms_random.value(1,50),
  5         mod(rownum,20),
  6         rownum
  7  from dual connect by level <= 100000;

100000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON customer_order_line_tab WITH ROWID (order_no,qty_invoiced,part_price) INCLUDING NEW VALU

Materialized view log created.

SQL>
SQL> drop MATERIALIZED VIEW customer_order_line_mv ;

Materialized view dropped.

SQL>
SQL> CREATE MATERIALIZED VIEW CUSTOMER_ORDER_LINE_MV
  2  REFRESH FAST ON DEMAND
  3  ENABLE QUERY REWRITE
  4  ENABLE ON QUERY COMPUTATION
  5  AS
  6  SELECT t.order_no, SUM(t.qty_invoiced) AS sum_qt, SUM(t.part_price) AS sum_pr--, count(*) as c
  7  FROM customer_order_line_tab t
  8  GROUP BY t.order_no;

Materialized view created.

SQL>
SQL>
SQL> set autotrace traceonly explain
SQL> SELECT t.order_no, SUM(t.qty_invoiced) AS sum_qt, SUM(t.part_price) AS sum_pr
  2  FROM customer_order_line_tab t
  3  GROUP BY t.order_no;

Execution Plan
----------------------------------------------------------
Plan hash value: 2678166370

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |  1000 | 13000 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| CUSTOMER_ORDER_LINE_MV |  1000 | 13000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

SQL> set autotrace off
SQL>
SQL> insert into customer_order_line_tab values (5,7,123.43,5,'x');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> set autotrace traceonly explain
SQL> SELECT t.order_no, SUM(t.qty_invoiced) AS sum_qt, SUM(t.part_price) AS sum_pr
  2  FROM customer_order_line_tab t
  3  GROUP BY t.order_no;

Execution Plan
----------------------------------------------------------
Plan hash value: 3236903439

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                                |  2001 | 78039 |    19  (16)| 00:00:01 |
|   1 |  VIEW                             |                                |  2001 | 78039 |    19  (16)| 00:00:01 |
|   2 |   UNION-ALL                       |                                |       |       |            |          |
|*  3 |    VIEW                           | VW_FOJ_0                       |  1000 | 42000 |     7  (15)| 00:00:01 |
|*  4 |     HASH JOIN FULL OUTER          |                                |  1000 | 54000 |     7  (15)| 00:00:01 |
|   5 |      VIEW                         |                                |     1 |    24 |     4  (25)| 00:00:01 |
|   6 |       HASH GROUP BY               |                                |     1 |    48 |     4  (25)| 00:00:01 |
|*  7 |        TABLE ACCESS FULL          | MLOG$_CUSTOMER_ORDER_LINE_TAB  |     1 |    48 |     3   (0)| 00:00:01 |
|   8 |      VIEW                         |                                |  1000 | 30000 |     3   (0)| 00:00:01 |
|   9 |       MAT_VIEW ACCESS FULL        | CUSTOMER_ORDER_LINE_MV         |  1000 | 13000 |     3   (0)| 00:00:01 |
|* 10 |    VIEW                           | VW_FOJ_1                       |  1000 | 68000 |     7  (15)| 00:00:01 |
|* 11 |     HASH JOIN FULL OUTER          |                                |  1000 | 53000 |     7  (15)| 00:00:01 |
|  12 |      VIEW                         |                                |     1 |    45 |     4  (25)| 00:00:01 |
|  13 |       HASH GROUP BY               |                                |     1 |    48 |     4  (25)| 00:00:01 |
|* 14 |        TABLE ACCESS FULL          | MLOG$_CUSTOMER_ORDER_LINE_TAB  |     1 |    48 |     3   (0)| 00:00:01 |
|  15 |      VIEW                         |                                |  1000 |  8000 |     3   (0)| 00:00:01 |
|  16 |       MAT_VIEW ACCESS FULL        | CUSTOMER_ORDER_LINE_MV         |  1000 | 13000 |     3   (0)| 00:00:01 |
|  17 |    NESTED LOOPS                   |                                |     1 |    81 |     5  (20)| 00:00:01 |
|  18 |     VIEW                          |                                |     1 |    68 |     4  (25)| 00:00:01 |
|  19 |      HASH GROUP BY                |                                |     1 |    48 |     4  (25)| 00:00:01 |
|* 20 |       TABLE ACCESS FULL           | MLOG$_CUSTOMER_ORDER_LINE_TAB  |     1 |    48 |     3   (0)| 00:00:01 |
|  21 |     MAT_VIEW ACCESS BY INDEX ROWID| CUSTOMER_ORDER_LINE_MV         |     1 |    13 |     1   (0)| 00:00:01 |
|* 22 |      INDEX UNIQUE SCAN            | I_SNAP$_CUSTOMER_ORDER_LINE_MV |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("AV$0"."OJ_MARK" IS NULL)
   4 - access(SYS_OP_MAP_NONNULL("SNA$0"."ORDER_NO")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
   7 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-05-01 09:13:04', 'syyyy-mm-dd hh24:mi:ss'))
  10 - filter("SNA$0"."SNA_OJ_MARK" IS NULL)
  11 - access(SYS_OP_MAP_NONNULL("SNA$0"."ORDER_NO")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))
  14 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-05-01 09:13:04', 'syyyy-mm-dd hh24:mi:ss'))
  20 - filter("MAS$"."SNAPTIME$$">TO_DATE(' 2017-05-01 09:13:04', 'syyyy-mm-dd hh24:mi:ss'))
  22 - access(SYS_OP_MAP_NONNULL("ORDER_NO")=SYS_OP_MAP_NONNULL("AV$0"."GB0"))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan
复制

给你...