Oracle 关于提交刷新快速mv的问题

团队:

下面是我的测试用例，用于下面的演示。这是来自18c数据库。

问题:

Q1-为什么此跟踪文件中报告此错误 “ORA-10980”，我的测试用例有什么问题？
Q2-所有三个delete语句的谓词如 “where rid1/rid2 in (...)” 都没有在任何一列上使用索引，为什么？
Q3-请参阅 “插入T1_T2_MV...”，它将mlog $_t2与t2结合在一起-带有提示HASH_SJ-
优化器仍然没有访问基于ROWID的T2嵌套循环为卡 = 2，
相反，它在这里使用哈希连接。这里还可以做些什么来避免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)

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

请注意，所有语句都有以下提示:

哈希 _ sj

这是强制MV及其日志之间的哈希连接。

哈希连接can't跨联接使用索引。这是它们和嵌套循环连接之间的定义差异之一。因此，只要有提示，您就可以对MV进行完整扫描。

现在的问题是: 为什么会有提示？

我不确定为什么最初添加这些内容，但是MOS note 420040.1讨论了这个问题。从10g开始，您可以通过以下方式避免出现这些情况:

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

这对我有用-如果不适合您，请检查MOS注释并跟进支持。

对于ORA-10980错误，MOS note 294513.1表示:

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.

解决办法是忽略这一点!如果这困扰您，请继续提供支持。