Oracle Live SQL - Script: Question on user_mvref_stmt

Statement 1

create table t_master ( 
 m_id number 
,m_text varchar2(3) 
)

Table created.

Statement 2

--prepare some test data (total of 60 rows) we will see this number later in the stats

begin 
for anz in (select 11 rec from dual 
            union all 
            select 2 rec from dual 
            union all 
            select 17 rec from dual 
            union all 
            select 30 rec from dual)  
loop   
  insert into t_master (m_id,m_text) 
  select  
     to_number(anz.rec || level) m_id  
   ,dbms_random.string('U',3) as m_text  
  from dual  
  connect by level <= anz.rec; 
end loop; 
end;

1 row(s) inserted.

Statement 3

commit

Statement processed.

Statement 4

alter table t_master add primary key (m_id)

Table altered.

Statement 5

create materialized view log on t_master 
with primary key 
including new values

Statement processed.

Statement 6

create materialized view mv_complete_refresh (m_id, m_text) 
  build deferred 
  refresh complete on demand --!!!! 
  disable query rewrite 
  as select m_id, m_text 
       from t_master

Statement processed.

Statement 7

--same definition again...we will compare atomic refresh

create materialized view mv_atomic_false (m_id, m_text) 
  build deferred 
  refresh complete on demand --!!!! 
  disable query rewrite 
  as select m_id, m_text 
       from t_master

Statement processed.

Statement 8

create materialized view mv_fast_refresh (m_id, m_text) 
  build deferred 
  refresh fast on demand --!!!! 
  disable query rewrite 
  as select m_id, m_text 
       from t_master

Statement processed.

Statement 9

I think we can ignore the error?

--settings for stats is not allowed in liveSQL...maybe this is a problem and I would need to set something here?

begin 
 dbms_mview_stats.set_system_default ('COLLECTION_LEVEL','TYPICAL'); 
 --DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS (NULL, 'ADVANCED'); 
 dbms_mview_stats.set_mvref_stats_params ('MV_FAST_REFRESH','ADVANCED',60); -- The retention period is set to 60 days 
commit; 
end;

ORA-06550: line 2, column 2:
PLS-00201: identifier 'DBMS_MVIEW_STATS' must be declared

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 10

select * from user_mvref_stats_sys_defaults


PARAMETER_NAME VALUE
COLLECTION_LEVEL TYPICAL
COLLECTION_LEVEL TYPICAL
RETENTION_PERIOD 365
RETENTION_PERIOD 365

4 rows selected.

PARAMETER_NAME	VALUE
COLLECTION_LEVEL	TYPICAL
COLLECTION_LEVEL	TYPICAL
RETENTION_PERIOD	365
RETENTION_PERIOD	365

Statement 11

select * from user_mvref_stats_params


MV_OWNER MV_NAME COLLECTION_LEVEL RETENTION_PERIOD
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA MV_ATOMIC_FALSE TYPICAL 365
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA MV_ATOMIC_FALSE TYPICAL 365
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA MV_COMPLETE_REFRESH TYPICAL 365
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA MV_COMPLETE_REFRESH TYPICAL 365
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA MV_FAST_REFRESH TYPICAL 365
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA MV_FAST_REFRESH TYPICAL 365

6 rows selected.

MV_OWNER	MV_NAME	COLLECTION_LEVEL	RETENTION_PERIOD
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA	MV_ATOMIC_FALSE	TYPICAL	365
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA	MV_ATOMIC_FALSE	TYPICAL	365
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA	MV_COMPLETE_REFRESH	TYPICAL	365
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA	MV_COMPLETE_REFRESH	TYPICAL	365
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA	MV_FAST_REFRESH	TYPICAL	365
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA	MV_FAST_REFRESH	TYPICAL	365

Statement 12

For some reason liveSQL will not let me do a refresh of the last two MVs (MV_ATOMIC_FALSE and MV_FAST_REFRESH). I think we can ignore it. We at least have some stats about the complete refresh.

begin 
dbms_mview.refresh('MV_COMPLETE_REFRESH', 'C'); 
dbms_mview.refresh('MV_ATOMIC_FALSE', 'C', atomic_refresh => false); 
--...intial load required 
dbms_mview.refresh('MV_FAST_REFRESH', 'C', atomic_refresh => false); 
dbms_mview.refresh('MV_FAST_REFRESH', 'F', atomic_refresh => false); 
end;

ORA-12008: error in materialized view or zonemap refresh path ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2952
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2370
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 85
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 245
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2352
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2908
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3191
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3221
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 15
ORA-06512: at line 3
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-12008

Statement 13

--updates on master

update t_master 
set m_id = to_number('123'||substr(m_id,3)) 
where to_char(m_id) like '11%'

11 row(s) updated.

Statement 14

commit

Statement processed.

Statement 15

Same like #12

--populate changes to MVs

begin 
dbms_mview.refresh('MV_COMPLETE_REFRESH', 'C'); 
dbms_mview.refresh('MV_ATOMIC_FALSE', 'C', atomic_refresh => false); 
dbms_mview.refresh('MV_FAST_REFRESH', 'F', atomic_refresh => false); 
end;

ORA-12008: error in materialized view or zonemap refresh path ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2952
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2370
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 85
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 245
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2352
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2908
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3191
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3221
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 15
ORA-06512: at line 3
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-12008

Statement 16

--I am used to this view in older releases...

select * from user_mvref_stats


MV_NAME REFRESH_ID REFRESH_METHOD REFRESH_OPTIMIZATIONS ADDITIONAL_EXECUTIONS START_TIME END_TIME ELAPSED_TIME LOG_SETUP_TIME LOG_PURGE_TIME INITIAL_NUM_ROWS FINAL_NUM_ROWS
MV_COMPLETE_REFRESH 2286 COMPLETE  -  - 27-SEP-17 05.42.44.000000 AM 27-SEP-17 05.42.44.000000 AM 0 0 0 0 60
MV_COMPLETE_REFRESH 2288 COMPLETE  -  - 27-SEP-17 05.42.45.000000 AM 27-SEP-17 05.42.45.000000 AM 0 0 1 60 60

2 rows selected.

MV_NAME	REFRESH_ID	REFRESH_METHOD	REFRESH_OPTIMIZATIONS	ADDITIONAL_EXECUTIONS	START_TIME	END_TIME	ELAPSED_TIME	LOG_SETUP_TIME	LOG_PURGE_TIME	INITIAL_NUM_ROWS	FINAL_NUM_ROWS
MV_COMPLETE_REFRESH	2286	COMPLETE	-	-	27-SEP-17 05.42.44.000000 AM	27-SEP-17 05.42.44.000000 AM	0	0	0	0	60
MV_COMPLETE_REFRESH	2288	COMPLETE	-	-	27-SEP-17 05.42.45.000000 AM	27-SEP-17 05.42.45.000000 AM	0	0	1	60	60

Statement 17

--NEW: Reports about each refresh operation

select * from user_mvref_run_stats


REFRESH_ID NUM_MVS MVIEWS BASE_TABLES METHOD ROLLBACK_SEG PUSH_DEFERRED_RPC REFRESH_AFTER_ERRORS PURGE_OPTION PARALLELISM HEAP_SIZE ATOMIC_REFRESH NESTED OUT_OF_PLACE NUMBER_OF_FAILURES START_TIME END_TIME ELAPSED_TIME LOG_SETUP_TIME LOG_PURGE_TIME COMPLETE_STATS_AVAILABLE
2245 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - c  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2269 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2265 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2249 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2267 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2253 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2271 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2257 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2251 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2261 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2255 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2287 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2259 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2288 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_COMPLETE_REFRESH"  - C  - Y N 1 0 0 Y N N 0 27-SEP-17 05.42.45.000000 AM 27-SEP-17 05.42.45.000000 AM 1 0 1 Y
2263 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2272 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2289 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"  - C  - Y N 1 0 0 N N N 0  -  -  -  -  - Y
2286 1 "SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_COMPLETE_REFRESH"  - C  - Y N 1 0 0 Y N N 0 27-SEP-17 05.42.44.000000 AM 27-SEP-17 05.42.44.000000 AM 0 0 0 Y

18 rows selected.

Statement 18

--NEW: reports about the change data load information on the master tables associated with a refresh run

select * from user_mvref_change_stats


TBL_OWNER TBL_NAME MV_OWNER MV_NAME REFRESH_ID NUM_ROWS_INS NUM_ROWS_UPD NUM_ROWS_DEL NUM_ROWS_DL_INS PMOPS_OCCURRED PMOP_DETAILS NUM_ROWS
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA T_MASTER SQL_UMNSFFJRHMPNZVKIFMDLQGEUA MV_COMPLETE_REFRESH 2286 0 0 0 0 N  - 60
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA T_MASTER SQL_UMNSFFJRHMPNZVKIFMDLQGEUA MV_COMPLETE_REFRESH 2288 11 0 11 0 N  - 60

2 rows selected.

TBL_OWNER	TBL_NAME	MV_OWNER	MV_NAME	REFRESH_ID	NUM_ROWS_INS	NUM_ROWS_UPD	NUM_ROWS_DEL	NUM_ROWS_DL_INS	PMOPS_OCCURRED	PMOP_DETAILS	NUM_ROWS
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA	T_MASTER	SQL_UMNSFFJRHMPNZVKIFMDLQGEUA	MV_COMPLETE_REFRESH	2286	0	0	0	0	N	-	60
SQL_UMNSFFJRHMPNZVKIFMDLQGEUA	T_MASTER	SQL_UMNSFFJRHMPNZVKIFMDLQGEUA	MV_COMPLETE_REFRESH	2288	11	0	11	0	N	-	60

Statement 19

Why does this View not contain any data? Is it a special parameter that has to be set?

-- is something shown here? It never returns any data... -- should show SQLID of the refresh statement, and execution plan of the statement.

select * from user_mvref_stmt_stats

no data found

Question on user_mvref_stmt_stats

Additional Information

REFRESH_ID	NUM_MVS	MVIEWS	BASE_TABLES	METHOD	ROLLBACK_SEG	PUSH_DEFERRED_RPC	REFRESH_AFTER_ERRORS	PURGE_OPTION	ATOMIC_REFRESH	NESTED	OUT_OF_PLACE	START_TIME	END_TIME	ELAPSED_TIME	LOG_SETUP_TIME	LOG_PURGE_TIME	COMPLETE_STATS_AVAILABLE
2245	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	c	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2269	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2265	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2249	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2267	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2253	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2271	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2257	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2251	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2261	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2255	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2287	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2259	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2288	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_COMPLETE_REFRESH"	-	C	-	Y	N	1	Y	N	N	27-SEP-17 05.42.45.000000 AM	27-SEP-17 05.42.45.000000 AM	1	0	1	Y
2263	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2272	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2289	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_ATOMIC_FALSE"	-	C	-	Y	N	1	N	N	N	-	-	-	-	-	Y
2286	1	"SQL_UMNSFFJRHMPNZVKIFMDLQGEUA"."MV_COMPLETE_REFRESH"	-	C	-	Y	N	1	Y	N	N	27-SEP-17 05.42.44.000000 AM	27-SEP-17 05.42.44.000000 AM	0	0	0	Y