Skip to Main Content
  • Questions
  • Real-time materialized view not working as expected

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Navinth.

Asked: April 21, 2017 - 2:19 pm UTC

Last updated: January 19, 2021 - 4:18 am UTC

Version: Oracle 12.2.0.1 Enterprise Edition

Viewed 1000+ times

You Asked

Hello,

I have a problem with Real-time Materialized View ‘ON QUERY COMPUTATION’ functionality.

My Real-time MV is enabled for both QUERY REWRITE & ON QUERY COMPUTATION.

As I understand, when the MV is fresh, we get a MAT_VIEW REWRITE ACCESS FULL run which is not because ON QUERY COMPUTATION but 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.

When the trace file is analyzed, an error is visible at the beginning of the trace with text "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$$

My Parameter Settings:
----------------------
query_rewrite_integrity = ENFORCED
query_rewrite_enabled = TRUE

Materialized View Log Definition:
---------------------------------
CREATE MATERIALIZED VIEW LOG ON customer_order_line_tab WITH ROWID (order_no,qty_invoiced,part_price) INCLUDING NEW VALUES;

Materialized View Definition:
--------------------------
CREATE MATERIALIZED VIEW customer_order_line_mv
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
ENABLE ON QUERY COMPUTATION
AS
SELECT t.order_no, SUM(t.qty_invoiced) AS sum_qt, SUM(t.part_price) AS sum_pr
FROM customer_order_line_tab t
GROUP BY t.order_no;

Query executed:
--------------
SELECT t.order_no, SUM(t.qty_invoiced) AS sum_qt, SUM(t.part_price) AS sum_pr
FROM customer_order_line_tab t
GROUP BY t.order_no;

Please find the attached Source trace file (LiveSQL) when ON QUERY COMPUTATION is set)

Please advice us on the error I'm having in trace file & why ON QUERY COMPUTATION is not working for my scenario?

Many Thanks,
Navinth


with LiveSQL Test Case:

and we said...

It is still a costing decision by the optimizer, eg

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 |
----------------------------------------------------------------------------------------------


So that looks like it did not rewrite...lets see why

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



and you can see - "cost based optimizer found query rewrite is more expensive".

Let's repeat the exercise with a much bigger source table

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


and there you go ...

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Navinth Bakmeedeniya, May 01, 2017 - 8:04 am UTC

Many thanks for the reply Connor.

BTW is it compulsory to have a count(*) column in a real-time materialized view to rewrite when it is in STALE. In other words, can a materialized views with query rewrite & on query computation enabled but no count(*) column in it's SELECT statement, ever be qualified for rewriting a query when it is STALE?


Connor McDonald
May 02, 2017 - 2:49 am UTC

No, you can see in my test case it was commented out ... I originally put it in because my first hypothesis was that it would be needed. But in *this* case it was not.

Navinth Bakmeedeniya, May 02, 2017 - 11:58 am UTC

Hi Connor,

While Real-time MV is defined with a count(*) statement, an ora-00600 error is fired when running a simple count operation against the base table customer_order_line_tab. This seems happening when MV is stale.

Real-time MV Definition:

CREATE MATERIALIZED VIEW test_order_detail_mv
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
ENABLE ON QUERY computation
AS
SELECT ord_no, SUM(price) AS sum_price, COUNT(*)
FROM test_order_detail_tab
GROUP BY ord_no;


When the MV is stale, try to run:
SELECT COUNT(1) from test_order_detail_tab t;

The below error is fired.
"ORA-00600: internal error code, arguments:[kkqvmRmViewFromLst1], [], [], [], [], [],......."

Additional observation: At this point, when the below statement is executed, optimizer uses a test plan combining the mv & the mv log thus ON QUERY COMPUTATION works fine with the optimizer.
SELECT ord_no, SUM(price) AS tot_price FROM test_order_detail_tab GROUP BY ord_no;

Further investigations showed that, this error is fired not only for count(1) but it is fired for count function with any column parameter other than "*" (count(*)) in the running SELECT statement.

I suspect this a bug in Oracle Real-time Materialized Views functionality in Oracle 12 Release 2 ?

Many Thanks,
Navinth
Connor McDonald
May 03, 2017 - 12:55 am UTC

What happens if you alias the column ?

Navinth Bakmeedeniya, May 02, 2017 - 3:55 pm UTC

A correction: Name of the base table I used in my last example should be test_order_detail_tab & not customer_order_line_Tab. (Actually no change in the definition but just a different name)

Navinth Bakmeedeniya, May 03, 2017 - 5:01 am UTC

Tried aliasing the count(*) column in MV but still the error is thrown.
Connor McDonald
May 04, 2017 - 1:19 am UTC

Looks like a bug (as pretty much any ora-600 is).

Navinth Bakmeedeniya, May 03, 2017 - 7:52 am UTC

Further more, this works fine when I forcefully stop query rewrite.

SELECT /*+ NO_REWRITE */ COUNT(1) from test_order_detail_tab t;

Navinth Bakmeedeniya, May 04, 2017 - 3:16 am UTC

Thanks for the input Connor.
I will report this to Oracle support then.

on FAST REFRESH MVIEW

Rajeshwaran, Jeyabal, November 02, 2020 - 10:12 am UTC

Team:

Here is my testcase used for the below demo. this was from 18c database.

Questions:

Q1 - why this error "ORA-10980" is reported in this trace file, what was the problem with my testcase ?
Q2 - all three delete statements having the predicate like " where rid1/rid2 in (...) " is not using the index on either of the columns why ?
Q3 - please see the " insert into T1_T2_MV..." where it joins mlog$_t2 with T2 - with the hint HASH_SJ -
optimizer is still not accessing T2 based on ROWID on nested loops for card=2,
instead it make use of HASH join here. what else could be done here to avoid the full scan on T2.


create table t1 as select a.*,rownum r from all_objects a, all_users where rownum <=2000000;
create table t2 as select * from t1;

alter table t1 add constraint t1_pk primary key(r);
alter table t2 add constraint t2_pk primary key(r);

create materialized view log on t1 with primary key,rowid,sequence 
(object_type, object_name,created,last_ddl_time,timestamp,status)
including new values;

create materialized view log on t2 with primary key,rowid,sequence 
(object_type, object_name,created,last_ddl_time,timestamp,status)
including new values;

create materialized view t1_t2_mv 
build immediate
refresh fast on demand
enable query rewrite 
as
select t1.object_type, 
    t1.object_name,
    t1.created,
    t1.last_ddl_time,
    t1.timestamp,
    t1.status,
    1 as umarker,
    t1.rowid as rid1,
    t1.rowid as rid2
from t1 
where owner in ('PUBLIC','APEX_200100','ORDSYS','MDSYS','XDB','SYSTEM','CTXSYS')
union all
select t1.object_type, 
    t1.object_name,
    t1.created,
    t2.last_ddl_time,
    t2.timestamp,
    t1.status,
    2 as umarker,
    t1.rowid as rid1,
    t2.rowid as rid2
from t1 , t2
where t1.r = t2.r
and t1.owner ='SYS';

create index t1_t2_mv_idx_01 on t1_t2_mv( rid1 ) nologging;
create index t1_t2_mv_idx_02 on t1_t2_mv( rid2 ) nologging;

update t2 set object_type = lower(object_type) where rownum =1;
delete from t1 where rownum <=5;
commit;

exec dbms_stats.gather_table_stats(user,'mlog$_t1',no_invalidate=>false);
exec dbms_stats.gather_table_stats(user,'mlog$_t2',no_invalidate=>false);

demo@XEPDB1> select count(*) from mlog$_t1;

  COUNT(*)
----------
         5

demo@XEPDB1> select count(*) from mlog$_t2;

  COUNT(*)
----------
         2

demo@XEPDB1> @tkfilename.sql
D:\APP\VNAMEIT\ORA18C_XE\diag\rdbms\xe\xe\trace\xe_ora_8468.trc
demo@XEPDB1> @tktrace.sql

PL/SQL procedure successfully completed.

demo@XEPDB1> set timing on
demo@XEPDB1> exec dbms_mview.refresh('T1_T2_MV','F');

PL/SQL procedure successfully completed.

Elapsed: 00:02:19.47
demo@XEPDB1> exit


and the TKPROF show's this:


The following statements encountered a error during parse:

select t1.object_type,
    t1.object_name,
    t1.created,
    t1.last_ddl_time,
    t1.timestamp,
    t1.status,
    1 as umarker,
    t1.rowid as rid1,
    t1.rowid as rid2
from t1
where owner in ('PUBLIC','APEX_200100','ORDSYS','MDSYS','XDB','SYSTEM','CTXSYS')
union all
select t1.object_type,
    t1.object_name,
    t1.created,
    t2.last_ddl_time,
    t2.timestamp,
    t1.status,
    2 as umarker,
    t1.rowid as rid1,
    t2.rowid as rid2
from t1 , t2
where t1.r = t2.r
and t1.owner ='SYS'

Error encountered: ORA-10980
--------------------------------------------------------------------------------
select t1.object_type,
    t1.object_name,
    t1.created,
    t1.last_ddl_time,
    t1.timestamp,
    t1.status,
    1 as umarker,
    t1.rowid as rid1,
    t1.rowid as rid2
from t1
where owner in ('PUBLIC','APEX_200100','ORDSYS','MDSYS','XDB','SYSTEM','CTXSYS')
union all
select t1.object_type,
    t1.object_name,
    t1.created,
    t2.last_ddl_time,
    t2.timestamp,
    t1.status,
    2 as umarker,
    t1.rowid as rid1,
    t2.rowid as rid2
from t1 , t2
where t1.r = t2.r
and t1.owner ='SYS' /* 1 */

Error encountered: ORA-10980
--------------------------------------------------------------------------------
select t1.object_type,
    t1.object_name,
    t1.created,
    t2.last_ddl_time,
    t2.timestamp,
    t1.status,
    2 as umarker,
    t1.rowid as rid1,
    t2.rowid as rid2
from t1 , t2
where t1.r = t2.r
and t1.owner ='SYS' /* 1 */
CLOSE #1969337992160:
Error encountered: ORA-10980
--------------------------------------------------------------------------------
select t1.object_type,
    t1.object_name,
    t1.created,
    t1.last_ddl_time,
    t1.timestamp,
    t1.status,
    1 as umarker,
    t1.rowid as rid1,
    t1.rowid as rid2
from t1
where owner in ('PUBLIC','APEX_200100','ORDSYS','MDSYS','XDB','SYSTEM','CTXSYS')

CLOSE #1969337992160:
Error encountered: ORA-10980
********************************************************************************

SQL ID: f4ddn3wdm1x7p Plan Hash: 3218733037

DELETE FROM "DEMO"."T1_T2_MV" SNA$ 
WHERE
 "RID2" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ * FROM (SELECT  
  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "DEMO"."MLOG$_T2" "MAS$"   
  WHERE "MAS$".SNAPTIME$$ > :B_ST0 ) AS OF SNAPSHOT(:B_SCN) MAS$)  AND 
  "UMARKER" = 2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      1.14      25.16      31134      31148          7           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.14      25.16      31134      31148          7           1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  T1_T2_MV (cr=31150 pr=31134 pw=0 time=25158726 us starts=1)
         1          1          1   HASH JOIN RIGHT SEMI (cr=31150 pr=31130 pw=0 time=25104765 us starts=1 cost=8574 size=50 card=1)
         2          2          2    TABLE ACCESS FULL MLOG$_T2 (cr=8 pr=0 pw=0 time=308 us starts=1 cost=6 size=54 card=2)
   1582660    1582660    1582660    MAT_VIEW ACCESS FULL T1_T2_MV (cr=31142 pr=31130 pw=0 time=5580008 us starts=1 cost=8561 size=22571165 card=981355)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                        260        0.95         23.99
  db file sequential read                         4        0.02          0.05
********************************************************************************

SQL ID: 8kczt72cxm02s Plan Hash: 1175496436

INSERT  /*+ NOAPPEND */ INTO "DEMO"."T1_T2_MV" SELECT /*+ NO_MERGE("JV$") */ 
  "MAS$1"."OBJECT_TYPE","MAS$1"."OBJECT_NAME","MAS$1"."CREATED",
  "JV$"."LAST_DDL_TIME","JV$"."TIMESTAMP","MAS$1"."STATUS",2,"MAS$1".ROWID,
  "JV$"."RID$" FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM 
  "DEMO"."T2" "MAS$" WHERE ROWID IN (SELECT  /*+ HASH_SJ */  
  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "DEMO"."MLOG$_T2" "MAS$"   
  WHERE "MAS$".SNAPTIME$$ > :B_ST0 ))  AS OF SNAPSHOT(:B_SCN) "JV$", "T1" AS 
  OF SNAPSHOT(:B_SCN)  "MAS$1" WHERE "MAS$1"."R"="JV$"."R" AND 
  "MAS$1"."OWNER"='SYS'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.93      30.07        794      41278          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.93      30.07        794      41278          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  T1_T2_MV (cr=41284 pr=794 pw=0 time=30069483 us starts=1)
         0          0          0   NESTED LOOPS  (cr=41284 pr=794 pw=0 time=30069472 us starts=1 cost=41668 size=124 card=1)
         0          0          0    NESTED LOOPS  (cr=41284 pr=794 pw=0 time=30069470 us starts=1 cost=41668 size=124 card=1)
         1          1          1     VIEW  (cr=41279 pr=794 pw=0 time=30069211 us starts=1 cost=41666 size=45 card=1)
         1          1          1      HASH JOIN RIGHT SEMI (cr=41279 pr=794 pw=0 time=30069209 us starts=1 cost=41666 size=73 card=1)
         2          2          2       TABLE ACCESS FULL MLOG$_T2 (cr=9 pr=0 pw=0 time=393 us starts=1 cost=6 size=54 card=2)
   2000000    2000000    2000000       TABLE ACCESS FULL T2 (cr=41270 pr=794 pw=0 time=32481109 us starts=1 cost=41645 size=92000000 card=2000000)
         0          0          0     INDEX UNIQUE SCAN T1_PK (cr=5 pr=0 pw=0 time=254 us starts=1 cost=1 size=0 card=1)(object id 81046)
         0          0          0    TABLE ACCESS BY INDEX ROWID T1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=79 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       332        0.89         15.45
  db file scattered read                        231        0.73         13.46
********************************************************************************

SQL ID: 4q0ry8txa6qtx Plan Hash: 3204021098

DELETE FROM "DEMO"."T1_T2_MV" SNA$ 
WHERE
 "RID1" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ * FROM (SELECT  
  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "DEMO"."MLOG$_T1" "MAS$"   
  WHERE "MAS$".SNAPTIME$$ > :B_ST1 ) AS OF SNAPSHOT(:B_SCN) MAS$)  AND 
  "UMARKER" = 2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.31       0.32          0      31149         30           4
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.31       0.32          0      31149         30           4

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  T1_T2_MV (cr=31151 pr=0 pw=0 time=318898 us starts=1)
         4          4          4   HASH JOIN RIGHT SEMI (cr=31151 pr=0 pw=0 time=505387 us starts=1 cost=8574 size=50 card=1)
         5          5          5    TABLE ACCESS FULL MLOG$_T1 (cr=9 pr=0 pw=0 time=296 us starts=1 cost=6 size=135 card=5)
   1582659    1582659    1582659    MAT_VIEW ACCESS FULL T1_T2_MV (cr=31142 pr=0 pw=0 time=208866 us starts=1 cost=8561 size=22571165 card=981355)

********************************************************************************

SQL ID: 93wzg8q13azk3 Plan Hash: 3204021098

DELETE FROM "DEMO"."T1_T2_MV" SNA$ 
WHERE
 "RID2" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ * FROM (SELECT  
  CHARTOROWID("MAS$"."M_ROW$$") RID$     FROM "DEMO"."MLOG$_T1" "MAS$"   
  WHERE "MAS$".SNAPTIME$$ > :B_ST0 ) AS OF SNAPSHOT(:B_SCN) MAS$)  AND 
  "UMARKER" = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.09       0.10          0      31149          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.09       0.11          0      31149          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  T1_T2_MV (cr=31151 pr=0 pw=0 time=108246 us starts=1)
         0          0          0   HASH JOIN RIGHT SEMI (cr=31151 pr=0 pw=0 time=108244 us starts=1 cost=8574 size=250 card=5)
         5          5          5    TABLE ACCESS FULL MLOG$_T1 (cr=9 pr=0 pw=0 time=262 us starts=1 cost=6 size=135 card=5)
    380050     380050     380050    MAT_VIEW ACCESS FULL T1_T2_MV (cr=31142 pr=0 pw=0 time=34448 us starts=1 cost=8561 size=22571165 card=981355)

********************************************************************************



Connor McDonald
November 24, 2020 - 1:29 am UTC

was logged as a new question

Raul Kaubi, January 15, 2021 - 2:20 pm UTC

"Error encountered: ORA-10980"

What was the issue with this ORA error. I am on 19.9, and during mview refresh, I get exact same error.

More to Explore

DBMS_RANDOM

More on PL/SQL routine DBMS_RANDOM here