如何获得oracle物化视图 定时刷新时 刷新所用的时间 或者是监控 物化视图在jobs中建立的job任务的执行时间

Question

在网上已经搜索过 根本就没有相关的问题 跪求oracle大神解答

Answer 1

你确定你搜了么。。。
以下是物化视图的建立，注意是refresh mode是 on demand还是on commit
ON DEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性;而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。
创建物化视图时未作指定，则Oracle按ON DEMAND模式来创建。

但怎么更新?或者说物化视图的数据怎么随着基表而更新?Oracle提供了两种方式，手工刷新和自动刷新，像我们这种，在物化视图定义时，未作任何指定，那当然是默认的手工刷新了。也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。
所谓的自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包

CREATE MATERIALIZED VIEW mview_name
[TABLESPACE ts_name]
[PARALLEL (DEGREE n)]
[BUILD {IMMEDIATE|DEFERRED}]
[{ REFRESH {FAST|COMPLETE|FORCE}
[{ON COMMIT|ON DEMAND}]
| NEVER REFRESH } ]
[{ENABLE|DISABLE} QUERY REWRITE]
AS SELECT … FROM …
基于你说的情况，有job的定时刷新，应该是on commit的情况，或者是手动建立的job

下面给出我的测试：
create materialized view scott.MV_test_index
refresh force on commit
as
select * from scott.test_index;
SQL> select count(*) from mv_test_index;

COUNT(*)
----------
50195

SQL> delete from test_index where rownum=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from mv_test_index;

COUNT(*)
----------
50194
这里我们发现commit的时间会有些长，因为同时要刷新物化视图，我们这里其实是重新做了删除物化视图数据和插入新数据的操作（因为刷新的方法是complete，当然你可以选择FAST来做增量刷新，而且fast是首选），然后物化视图已经更新了。
下面是开启session sql trace的结构，你可以看到commit之后oralce所做的操作。
********************************************************************************

commit

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.11 0.23 0 0 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.11 0.23 0 0 1 0

Misses in library cache during parse: 0
Parsing user id: 54
********************************************************************************

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')
NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),
NVL(SUM(C2),:"SYS_B_1")
FROM
(SELECT /*+ NO_PARALLEL("MV_TEST_INDEX") FULL("MV_TEST_INDEX")
NO_PARALLEL_INDEX("MV_TEST_INDEX") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2
FROM "SCOTT"."MV_TEST_INDEX" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED
(:"SYS_B_6") "MV_TEST_INDEX") SAMPLESUB

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.07 0.19 44 69 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.07 0.19 44 69 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=69 pr=44 pw=0 time=196762 us)
10624 MAT_VIEW ACCESS SAMPLE MV_TEST_INDEX (cr=69 pr=44 pw=0 time=1853189 us)

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

delete from "SCOTT"."MV_TEST_INDEX"

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 3 0 0
Execute 1 7.48 9.46 20 321 154984 50194
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 7.50 9.48 20 324 154984 50194

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE MV_TEST_INDEX (cr=371 pr=20 pw=0 time=9503556 us)
50194 MAT_VIEW ACCESS FULL MV_TEST_INDEX (cr=310 pr=20 pw=0 time=1355940 us)

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

INSERT /*+ */ INTO "SCOTT"."MV_TEST_INDEX"("OBJECT_ID","OBJECT_NAME") SELECT
"TEST_INDEX"."OBJECT_ID","TEST_INDEX"."OBJECT_NAME" FROM
"SCOTT"."TEST_INDEX" "TEST_INDEX"

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 5.90 12.87 0 777 103790 50193
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 5.91 12.88 0 777 103790 50193

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
50193 TABLE ACCESS FULL TEST_INDEX (cr=241 pr=0 pw=0 time=602379 us)

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

BEGIN dbms_session.set_sql_trace(false); END;

接下来看看fast的刷新方法：
CREATE MATERIALIZED VIEW LOG ON scott.test_index; --这里我试过yantinkun的那个网页，是不能加with rowid的，会报错：ORA-23415: materialized view log for does not record the primary key
DROP materialized view log on scott.test_index;
create materialized view scott.MV_test_index
refresh fast on commit
as
select * from scott.test_index;

SQL> exec dbms_session.set_sql_trace(true);

PL/SQL procedure successfully completed.

SQL> delete from test_index where rownum=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_session.set_sql_trace(false);

PL/SQL procedure successfully completed.

SQL> host
这次的commit快多了，因为只是增量的刷新；
看下log中有这样一行：
DELETE FROM "SCOTT"."MV_TEST_INDEX" SNAP$
WHERE
"OBJECT_ID" = :1
只是找到了多出来的那个object的id，然后从物化视图里删掉就ok了。
可以查看物化视图的开始刷新时间，通过查看视图
select * from mlog$ where mowner='SCOTT' and MASTER='TEST_INDEX'来查找对应建立的物化视图的log，我这里查出来的是scott.MLOG$_TEST_INDEX
然后执行查询就可以看到详细的信息了：
select * from scott.MLOG$_TEST_INDEX

然后是刷新的时间，当刷新选项是commit的时候你是没法看的，只能从session的trace里面估计，
on demand话就是你自定义的job 来执行exec dbms_mview.refresh('MV_TEST_INDEX)
这个是可以看的，可以从user_jobs这个视图来看，里面有个total_time来记录job的执行总时间。

追问

total_time 是记录的总时间 这个我知道 但是我要的是每一次执行的时间 或者DBA_jobs 有没有记录表 什么的 可以让我获得上一次的执行的总时间?

追答

额，这个我没见过，不过我有个办法，找到你写的那个job 执行的刷新物化视图的那个过程，
比如：
create table job_exedura (job varchar2（100），start_time date,exedura date);
procedure ref_mv as
date1 date;
exeduration date;
begin
select sysdate into date1 from dual;
exec dbms_mview.refresh('MV_TEST_INDEX)
select sysdate-date1 into exeduration from dual; --这个不就是你的job的执行时间？
insert into job_exedura values('job_ref_mv',date1 , exeduration );
commit;
end;

这样你以后查看这个job的执行情况，不是一目了然了？
只要select * from job_exedura 就ok了
个人意见，仅供探讨，有好方法可以大家研究下

追问

谢谢您的回答! 物化视图的 job是自动生成的 你的意思是写个存储过程 将时间记录到表中 oracle没有提供能够监控物化视图刷新时间 或者job执行时间的方法吗? 我是不想用这种方式 才到网上来找的

Answer 2

如何获得oracle物化视图 定时刷新时 刷新所用的时间 或者是监控 物化视图在jobs中建立的job任务的执行时间
以下是物化视图的建立，注意是refresh mode是 on demand还是on commit
ON DEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性;而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。
创建物化视图时未作指定，则Oracle按ON DEMAND模式来创建。