Skip to Main Content
  • Questions
  • Questions about on commit refresh Fast MVs

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajeshwaran.

Asked: November 19, 2020 - 7:07 am UTC

Last updated: November 23, 2020 - 4:12 pm UTC

Version: 18c

Viewed 100+ times

You Asked

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)

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




and we said...

Notice all the statements have this hint:

HASH_SJ

This is forcing a hash join between the MV and its log.

Hash joins can't use indexes across the join. It's one of the defining differences between them and nested loops joins. So as long as the hint is there, you'll get a full scan of the MV.

Now the question is: why are the hints there?

I'm not sure why these were added originally, but MOS note 420040.1 discusses this issue. From 10g on you can avoid these appearing by:

Statistics need to be generated on the materialized view log while it is empty, and then locked via
DBMS_STATS.LOCK_TABLE_STATS.


This worked for me - if it doesn't for you, check the MOS note and follow up with support.

For the ORA-10980 errors, MOS note 294513.1 says:

The user should never see an ORA-10980. This error is raised internally and is cleared interally. It will be raised when Snapshot refresh queries are regenerated and parsed.
The materialized view definition query needs to be hard pased always to ensure execution of parse callback functions. UGAFMVEHP flag enforces hard parse for MVs. The cursor should not be shared. kkzparse() is called in many places with different callback functions. They are called with the query analysis results.

ORA-10980 error will be raised in your case, as it is a multi-tier MV (MV based on another MV), with aggregates, joins and Set operation.

When MVcontains aggregates, joins, Set operations, nested MVs, ORA-10980 may be raised as the refresh query could be regenerated based on some Optimizer options.


The solution is to ignore this! If this is bothering you, follow up with support.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.