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