Hi Masters,

sorry for another question :-) and again a big, big thank You for Your hard work and the answers to our questions!!

Could You please have a look into my test case? Some of the statement fail, because I do not have all the privs in liveSQL. But I think You can ignore them. Please focus on the last select statement. It queries for rows in user_mvref_stmt_stats, but does not find any. Because the previous code performed some refreshes, I would have expected that I see some stats in this Data Dictionary View including SQLIDs and so on.

Why is nothing shown?
Am I missing privileges?
Is there any prerequisite that I oversee in the Docs?
I used: https://docs.oracle.com/database/122/DWHSG/monitoring-materialized-view-refresh.htm#DWHSG-GUID-8F9CC2EC-9CFC-4F56-97D5-5EB099BD852A

I also tried this test case in the developer VM (which is downloadable here: http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html ). I also tried to read the equivalent dba_* views with a higher privileged schema.

Thanks a lot for Your help!
Best,
Jonas


attached a version of the script that You can run without liveSQL:

drop table t_master;
drop materialized view mv_complete_refresh;
drop materialized view mv_fast_refresh;
drop materialized view mv_atomic_false;
create table t_master (
 m_id number
,m_text varchar2(3)
);

--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;
/
commit;

alter table t_master add primary key (m_id);

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

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;

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

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;

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

--since the above statements fail they all have one year...
--otherwise two out of three have retention period of a whole year. The other one 60 days
select * from user_mvref_stats_sys_defaults;
select * from user_mvref_stats_params; 

--let's 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;
/

--updates on master
update t_master
set m_id = to_number('123'||substr(m_id,3))
where to_char(m_id) like '11%';
commit;

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

--I am used to this view in older releases...
select * from user_mvref_stats;
--NEW: reports about the change data load information on the master tables associated with a refresh run
select * from user_mvref_change_stats;   
--NEW: Reports about each refresh operation
select * from user_mvref_run_stats;

-- is something shown here? It never returns any data...
-- should show SQLID of the refresh statement, and execution plan of the statement.
-- but not in my environments...please help :-)
select * from user_mvref_stmt_stats;

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

--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;
/
commit;

alter table t_master add primary key (m_id);

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

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;

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

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;

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

--let's 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;
/

--updates on master
update t_master
set m_id = to_number('123'||substr(m_id,3))
where to_char(m_id) like '11%';
commit;

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

select * from user_mvref_stmt_stats;

MV_OWNER  MV_NAME          REFRESH_ID  STEP  SQLID          STMT                                                                              EXECUTION_TIME  EXECUTION_PLAN  
CHRIS     MV_FAST_REFRESH  143         1     9xj7u7fnn9zdw  /* MV_REFRESH (IND_UNUSABLE) */ ALTER INDEX "CHRIS"."SYS_C0054529" UNUSABLE       0                               
CHRIS     MV_FAST_REFRESH  143         2     5b1j8x93ntbdf  BEGIN  sys.dbms_index_utl.multi_level_build(index_list=>'"CHRIS"."SYS_C0054529"'  1