Oracle 物化视图: 刷新统计历史记录-我如何在user_mvref_stmt_stats中查看数据

嗨，大师们，

抱歉，还有一个问题 :-) 再次非常非常感谢您的辛勤工作和我们问题的答案!!

你能看看我的测试用例吗？有些语句失败，因为我没有liveSQL中的所有privs。但我认为你可以忽略它们。请关注lastselect语句。它查询user_mvref_stmt_stats中的行，但找不到任何行。因为以前的代码执行了一些刷新，所以我期望我在此数据字典视图中看到一些统计信息，包括SQLIDs等。

为什么什么都没有显示？
我错过特权了吗？
我在文档中监督有什么先决条件吗？
我用了:https://docs.oracle.com/database/122/DWHSG/monitoring-materialized-view-refresh.htm#DWHSG-GUID-8F9CC2EC-9CFC-4F56-97D5-5EB099BD852A

我还在开发人员VM中尝试了此测试用例 (可在此处下载:http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html)。我还尝试读取具有更高特权架构的等效dba_ * 视图。

非常感谢您的帮助!
最好的，
乔纳斯


附加了一个脚本版本，您可以在没有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;
复制

只是为了澄清一下:

这是在您所有的12.2数据库中发生的，还是只是LiveSQL？

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                            
复制

所以我们之间有些不同。

您的用户有哪些特权？授予DBA可以解决此问题吗？

注意: 有一个内部错误记录与此视图相关，不返回任何数据。所以你有可能击中它，但我不是出于某种原因。