refresh interval
Reader, June 01, 2003 - 8:57 pm UTC
also, it depends on the refresh interval. is it not?
June 02, 2003 - 7:14 am UTC
what depends? the definition?
The bottom line is how they are done:
o truncate + insert append
vs
o row by row
you can make a "changes only" take less time by decreasing the interval between refreshes maybe - but the cumulative time.... six one way half dozen the other.
Rate of data change
Reader, June 02, 2003 - 7:11 am UTC
Refresh interval is perhaps not as important as "how quickly the data changes" and how much changes in one hit. For some data sets 'fast' never is quicker than complete.
Question: For a partitioned MV are there any differences in mechanism for MV refresh? - is there a route like "truncate partition / insert (append) into" for just the changed partition. This would be useful in a DW context
June 02, 2003 - 8:07 am UTC
Ohhhhh. !
Reader, June 02, 2003 - 8:41 am UTC
Thanks Tom, a timely reminder to "Just read the Documentation"
A reader, June 02, 2003 - 11:53 am UTC
Tom,
Do you help in writing Oracle doc's or is it some other group..
Thanks..
June 02, 2003 - 12:35 pm UTC
that is a different group
How do you predict relative refresh times?
Peter, March 29, 2004 - 5:06 am UTC
Tom, you comment that 'Fast' is not the best description for the non-complete refresh.
The business model in our DWH requires that we report 'AS IS' on data (that is we always join the fact to the current values of the reference data) The downside of this that when items change parentage (You'd think that <they> would know apples are members of fruit!) There is a need to refresh the pre-built aggregations
Fast MV refresh (on demand) is, in general, OK for our needs, but sometimes (to often) the business throws a major change that is really faster to process as a complete refresh - can you think of a way we can "guess" which approach will be quicker for a given data change and then execute the most appropriate refresh?.
A straight count of changed 'child' records will not help as the number parents affected could vary greatly - we have seen a change of 5% of the data affecting 25% of a parent summary and the same amount of change affecting less than 3% the same summary
March 29, 2004 - 10:09 am UTC
well, there are no "non-complete" -- they are "incremental"
but, the timing is going to be based on not only the percentage of change -- but the complexity involved and the spread of the data.
update 1 row 1,000,000 times -- not much resulting work.
update 1,000,000 rows 1 time each -- tons of resulting work.
an mv that takes 1,000,000 detail rows down to 1,000 aggregate rows would probably refresh much faster than one that goes down to 100,000 aggregate rows.
The only think I can think would be to capture over time the refresh times you've experienced...
Thanks
Peter, March 29, 2004 - 10:58 am UTC
OK... I was trying not to use the F (fast) word ;-} but chose the wrong alternative!
I fully understand your points (and they match our experience) But for a production system it would be great to have qualitative indicator to say "fast is best" or not.
Could we use the view logs to help on this?
March 29, 2004 - 11:45 am UTC
you'd have to customize the refresh -- you would want to create a procedure that
snooped the mv log to get a count (perhaps by dml_type and maybe get counts of distinct primary keys)
got the timestamp
called dbms_mview to refresh
get the timestamp again
and log that information
schedule THAT job instead of using the MV refreshing by itself -- to get the history.
mview refresh rate suggestion
friend, May 28, 2004 - 5:05 am UTC
Dear Tom
following are the stats from my system and i am not concluding anything as looking for your conclusion about mview refresh rate
SQL> select count(*) from mlog$_ab;
COUNT(*)
----------
163127
SELECT aobject_name,a.object_type,tablespace_name,SUM(bytes)
OVER(partition by object_name)/(1024*1024) objsize FROM all_objects a,user_segments WHERE segment_name=object_name AND a.object_name like
UPPER('MLOG$_ab') AND object_type<>'SYNONYM' ORDER BY
tablespace_name,objsize desc,object_name;
OBJECT_NAME OBJSIZE
---------- ----------
MLOG$_ab 20
Job Name Last Start Last End ST Run Pri/Xit
____________________________ ________________ ________________ __ _______ ___
gca_p_b_refresh_person_vw 05/27/2004 22:48 05/27/2004 23:17 SU 43051591/1
refresh_vw 05/27/2004 22:48 05/27/2004 23:17 SU 43051591/1
select count(*) from mlog$_ab;
COUNT(*)
----------
1989
Hence the mview refresh rate -:
= 162k/29 min = 5.6k/min
it it feasible that 350k record in 15 minutes ????????????
May 28, 2004 - 9:27 am UTC
sorry but I don't even know what I'm looking at here.
another way to look at that could be "we pushed 92 records per second over our wan and applied them to another table"
can we do a fast refresh of 350k changes in 15 minutes? sure, here is my little desktop doing just that:
ops$tkyte@ORA9IR2> create table t
2 as
3 select rownum pk, big_table.* from big_table.big_table
4 where rownum <= &R;
old 4: where rownum <= &R
new 4: where rownum <= 350000
Table created.
Elapsed: 00:00:03.67
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(pk);
Table altered.
Elapsed: 00:00:01.90
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view log on t;
Materialized view log created.
Elapsed: 00:00:00.07
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view t_mv
2 refresh fast
3 on demand
4 as
5 select * from t;
Materialized view created.
Elapsed: 00:00:05.17
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*) from MLOG$_T;
COUNT(*)
----------
0
Elapsed: 00:00:00.00
ops$tkyte@ORA9IR2> begin
2 for i in 1 .. &R
3 loop
4 update t set owner='x' where pk = 1;
5 commit;
6 end loop;
7 end;
8 /
old 2: for i in 1 .. &R
new 2: for i in 1 .. 350000
PL/SQL procedure successfully completed.
Elapsed: 00:02:13.29
ops$tkyte@ORA9IR2> select count(*) from MLOG$_T;
COUNT(*)
----------
350000
Elapsed: 00:00:00.17
ops$tkyte@ORA9IR2> exec dbms_mview.refresh('T_MV')
PL/SQL procedure successfully completed.
Elapsed: 00:00:39.00
ops$tkyte@ORA9IR2> select count(*) from MLOG$_T;
COUNT(*)
----------
0
Elapsed: 00:00:02.77
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
2 for i in 1 .. &R
3 loop
4 update t set owner='x' where pk = i;
5 commit;
6 end loop;
7 end;
8 /
old 2: for i in 1 .. &R
new 2: for i in 1 .. 350000
PL/SQL procedure successfully completed.
Elapsed: 00:03:11.22
ops$tkyte@ORA9IR2> select count(*) from MLOG$_T;
COUNT(*)
----------
350000
Elapsed: 00:00:00.31
ops$tkyte@ORA9IR2> exec dbms_mview.refresh('T_MV')
PL/SQL procedure successfully completed.
Elapsed: 00:01:45.90
ops$tkyte@ORA9IR2> select count(*) from MLOG$_T;
COUNT(*)
----------
0
Elapsed: 00:00:03.27
ops$tkyte@ORA9IR2>
<b>thing to note - big difference between 350k changes to a row vs 350k rows changed....
amount of data moving over the wan will impact you as well.
You can always run a 10046 level 12 trace to see
a) what the refresh does
b) what it is waiting on most of the time....
in order to start seeing where you want to apply more resources</b>
Trouble with Fast Refresh
Steve Gillis, November 18, 2004 - 2:31 pm UTC
I am running version 9.2.0.4 (e) The compitability param is set to 9.2.0.4) and cannot seem to get the fast refresh working on an aggregrate query. I have done the following things (as described in the data wherehouse guid
1. created a materialized view log with all columns in the table and using the "with rowid, sequence .... including new values"
2. create the materialized view as follows
CREATE MATERIALIZED VIEW mm
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
rpt2
, SUM(val1)
, count(val1)
from a_table;
After i completed the above steps i query dba_mviews and everything looks okay. I then queried the materialized view log and found nothing there, as exptected. When i update the base table the mview log gets some rows. However when i query dba_mview i find that my materialized view is UNUSABLE. When i execute dbms_mvew.refresh('MM') i get this:
11:26:54 ORA-12057: materialized view
"MM" is INVALID and must complete refresh.
Is there some sort of specific settings that need to be in place in order for fast refresh to work????
The mview is based on a simple rollup query, nothing complex and its only coming from a single table.
November 18, 2004 - 2:42 pm UTC
full test case, something we can cut and paste and run...
Here is the script
Steve Gillis, November 18, 2004 - 3:04 pm UTC
/*
Here is the sql i used in my test. This is a very simplified
version but i am still getting the same errors.
*/
-- Create the base table
CREATE TABLE base (key1 VARCHAR2(1), key2 NUMBER, val NUMBER);
-- Populate the base table
INSERT INTO base VALUES('X',1,10);
INSERT INTO base VALUES('X',2,10);
INSERT INTO base VALUES('X',3,10);
INSERT INTO base VALUES('X',4,10);
INSERT INTO base VALUES('X',5,10);
INSERT INTO base VALUES('X',6,10);
INSERT INTO base VALUES('X',7,10);
INSERT INTO base VALUES('X',8,10);
INSERT INTO base VALUES('X',9,10);
INSERT INTO base VALUES('X',10,10);
INSERT INTO base VALUES('Y',1,10);
INSERT INTO base VALUES('Y',2,10);
INSERT INTO base VALUES('Y',3,10);
INSERT INTO base VALUES('Y',4,10);
INSERT INTO base VALUES('Y',5,10);
INSERT INTO base VALUES('Y',6,10);
INSERT INTO base VALUES('Y',7,10);
INSERT INTO base VALUES('Y',8,10);
INSERT INTO base VALUES('Y',9,10);
INSERT INTO base VALUES('Y',10,10);
-- Create the materialized view log
CREATE MATERIALIZED VIEW LOG ON base
WITH SEQUENCE, ROWID (key1,key2,val)
INCLUDING NEW VALUES;
-- Create the materiliized view
CREATE MATERIALIZED VIEW base_rlup
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
key1
, SUM(val)
, COUNT(val)
FROM base
GROUP BY key1;
/*
NOW SELECT FROM THE BASE TABLE AND THE DBA_MVIEWS TO VERIFY.
*/
-- Okay everything is okay. Now we are going to update the base table
UPDATE base SET val = 10000
WHERE key1 = 'X' AND key2 = 2;
COMMIT;
/*
At this point if i query the materialized view it does not reflect the changes
that were made to the base table. Also if i query dba_mviews the staleness attribute
for the mview is 'UNUSABLE'. Am i doing something completly wrong here??
*/
November 19, 2004 - 9:49 am UTC
ops$tkyte@ORA9IR2> CREATE MATERIALIZED VIEW base_rlup
2 BUILD IMMEDIATE
3 REFRESH FAST ON COMMIT
4 AS
5 SELECT key1 , SUM(val) , COUNT(val)
6 FROM base
7 GROUP BY key1;
Materialized view created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2> declare
2 l_data EXPLAINMVARRAYTYPE;
3 begin
4 dbms_mview.explain_mview( 'BASE_RLUP', l_data );
5 open :x for
6 select capability_name, possible,
7 msgno, msgtxt
8 from table( cast( l_data as EXPLAINMVARRAYTYPE ) )
9 order by seq;
10 end;
11 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> column msgtxt format a30 word_wrapped
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> print x;
CAPABILITY_NAME P MSGNO MSGTXT
------------------------------ - ---------- ------------------------------
PCT F 0
REFRESH_COMPLETE T 0
REFRESH_FAST T 0
REWRITE F 0
PCT_TABLE F 2068 relation is not a partitioned
table
REFRESH_FAST_AFTER_INSERT T 0<b>
REFRESH_FAST_AFTER_ONETAB_DML F 2142 COUNT(*) is not present in the
select list</b>
REFRESH_FAST_AFTER_ANY_DML F 2161 see the reason why
REFRESH_FAST_AFTER_ONETAB_DML
is disabled
REFRESH_FAST_PCT F 2157 PCT is not possible on any of
the detail tables in the
materialized view
REWRITE_FULL_TEXT_MATCH F 2159 query rewrite is disabled on
the materialized view
REWRITE_PARTIAL_TEXT_MATCH F 2159 query rewrite is disabled on
the materialized view
REWRITE_GENERAL F 2159 query rewrite is disabled on
the materialized view
REWRITE_PCT F 2158 general rewrite is not
possible and PCT is not
possible on any of the detail
tables
13 rows selected.
<b>you need count(*) in there... add count(*) and it works</b>
Fast refresh
Rob, November 18, 2004 - 5:40 pm UTC
Steve,
You need to add a count(*) to you materialized view create statement.
CREATE MATERIALIZED VIEW base_rlup
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT
key1
, SUM(val) sum_val
, COUNT(val) cnt_val,
COUNT(*) cnt
FROM base
GROUP BY key1;
I found the problem
Steve Gillis, November 18, 2004 - 5:54 pm UTC
Well it looks like i found the problem. Appearantly in order for all basic DML to work under fast refresh you need to have a count(exp) for every corresponding aggregate expression as well as a count(*). Initially i was just confused about needing them both, but changig the above mv definition to:
CREATE MATERIALIZED VIEW base_rlup
REFRESH FAST ON COMMIT
AS
SELECT
key1
, SUM(val)
, COUNT(val)
, COUNT(*)
FROM base
GROUP BY key1;
Has fixed the problem. I guess im just gonna need to read the docs more closely in the future. Sorry if this cost you any time, and thanks again for all you help on this site.
November 19, 2004 - 10:06 am UTC
hope you come back and see the above .... it makes life easier.
Some worthy of notes...
Connor McDonald, November 19, 2004 - 9:53 am UTC
a) there was a bug in aggregate on-commit refresh where the cardinality hint was incorrect on one of the recursive queries which could often lead to full scans on the source table during 'fast' refresh. Fixed in 9205+
b) If you've mucked around with DUAL (eg made it an IOT), then fast refreshes can go very very wrong.
c) delete's from tables which underpin agg mat views tyically end up with a top-to-bottom scan on the mat view (because the rows where count(*) now is zero need to be deleted). You may need to index the count column on the mat view.
Bottom line - there's no such thing as a free lunch
hth
connor
Complete refresh in refresh group
neil, November 30, 2004 - 9:56 am UTC
Hi Tom
you said earlier in the thread that a complete refresh does
a truncate followed by insert /*+ APPEND */
if the materialized view is part of a refresh group, doesn't it do a normal delete of the whole MV followed by normal insert ?
thanks
Neil
November 30, 2004 - 11:16 am UTC
dbms_advisor.tune_mview
Su Baba, May 02, 2006 - 11:48 am UTC
If you have Oracle10g, would you still be using dbms_mview.explain_mview or would you only use dbms_advisor.tune_mview? Can you explain in what situation would you use one over the other? Thanks.
May 02, 2006 - 3:54 pm UTC
If i had a materialized view and wanted to know what that materialized view was capable of - I would be using dbms_mview.
after that, if it wasn't "fixable", you can use the other procedure to <quote>
This procedure shows how to decompose a materialized view into two or more materialized views and to restate the materialized view in a way that is more advantageous for fast refresh and query rewrite. It also shows how to fix materialized view logs and to enable query rewrite.
</quote>
Materialized view refresh
Naveen Kumar, February 23, 2007 - 5:21 am UTC
Hi Tom
I have a question on this . I have a materialzed view log of 10g size on 32 G master table . The materialized view on this table was last refereshed on 07 sept 2006 . The table is frequently accessed . Due to some reasons the refreshes were stopped , now i wanted to enable the fast refresh on this particular Mview but this is not getting completed and throwing ora-1555 all the time . my retention was 6 hrs but i tried increasing that also. Now All i need is parameters to fasten the fast refresh .
Thanks
Naveen
February 26, 2007 - 10:15 am UTC
... "Now All i need is parameters to fasten the fast refresh "....
just set:
fast=true
in the init.ora.
sorry - it is monday and my sense of humor is just getting warmed up :)
7-sep-2006 was a long time ago. It may well be faster to COMPLETE refresh this table once to get it caught up and then incrementally refresh it (i don't like the term 'fast refresh', incremental refresh is much more appropriate)
Also, for the 1555 - have you verified that you had sufficient undo configured to hold N hours of generated undo? v$undostat would be useful to look at to see if you are prematurely expiring extents in undo.
A reader, February 27, 2007 - 5:22 am UTC
Thanks for the Answer Tom..
Yes incremental refresh would be the appropriate one but fast refresh is what the whole world is known to . As i said table size is of 32 G and it is very very frequently accessed table , going for complete refresh may create ORA-01555 again .
the following parameters are always showing 0 on my system
UNXPSTEALCNT
UNXPBLKRELCNY
UNXPBLKREUCNT
EXPSTEALCNT
EXPBLKRELCNT
EXPBLKREUCNT
I am planning for complete refresh on this MV in the lean hours. shall i use parallel option in the dbms_mview package. can i use and any more parameters to increase the speed.
Please suggest.
February 27, 2007 - 10:46 am UTC
how about on the SYSTEM YOU ARE READING FROM? that is where the 1555 is happening
Error?
Erik van Roon, June 01, 2010 - 10:20 am UTC
Just stumbled upon this thread, thanks to the original topic.
Read about tune_mview en decided to try it for my mview.
Well, it appears Oracle not only has a funny idea about name-giving (fast in stead of incremental), but also about doing your work properly.
The result of executing tune_mview with my view is an error.
Well, that happens. So, what did I do wrong? Let's take a look at the error:
.ORA-13600: error encountered in Advisor
QSM-03116: The materialized view is already optimal and cannot be tuned any further
So Oracle considers it to be an error if I do my work right?
;-)
Maybe I should apply for a job at Oracle. according to this view on what's right and not, I'm qualified ;-)
June 08, 2010 - 8:43 am UTC
It is an exception - exceptions are not necessarily an error, it is a way to reporting "not going to happen, did not work"
Have you ever caught a "when no_data_found" exception? If you caught it, it was not an error.
Return codes - hate em. Exceptions - cannot be ignored ultimately, that is a good thing.
Re: Error?
Erik van Roon, June 14, 2010 - 11:01 am UTC
Tom,
Looking at it objectively, as you do, obviously you are very very right.
Still, you must admit that at the very least it's fun to see the desired situation being reported as "error encountered in Advisor".
June 22, 2010 - 8:41 am UTC
:) yes
Materialized view on a view
Saurabh Nolakha, July 27, 2010 - 7:36 am UTC
Hi Tom,
I am planning to create a materialized view on a complex view.will it be fine performance wise where i require a incremental refresh? Please suggest whether I should continue with the idea or shall I built a view on base tables of view only.
July 27, 2010 - 12:41 pm UTC
I plan on driving across country using roads. Will it be fine performance wise?
I won't tell you what country.
I won't tell you what roads.
I won't tell you what I'm driving (could be a tractor or a race car).
I won't tell you what I expect 'performance' wise.
I won't tell you the availability of gas stations.
In short, I won't tell you much - when you can answer me correctly, I'll get right back to you on this question.
creating Materialized view through DBlink
Sunny, August 02, 2010 - 7:59 am UTC
Hi Tom,
I am using the following code to create a materialized view from a view.
CREATE MATERIALIZED VIEW MV_View
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT * FROM V_View@Dblink1;
I am getting the following error:
ORA-12014: table 'V_View' does not contain a primary key constraint.
I tried creating LOG also using the following statement:
CREATE MATERIALIZED VIEW LOG ON V_View@Dblink1 WITH ROWID;
But following error was received:
ORA-00949: illegal reference to remote database
Unable 2 resolve the problem.Please provide your suggestions.
Regards
Sunny
August 02, 2010 - 9:29 am UTC
you cannot do fast refresh on commit over a dblink, won't ever happen.
it is not permitted.
refresh fast with sysdate filter condition
A reader, June 27, 2011 - 9:50 am UTC
Hi, I have a table with all of the application audit details recorded and it has around 11 years worth of data. I have a requirement to create a materialized view with just current year data with fast refresh. Since I have the sysdate, I'm not able to achieve the fast refresh. Is there any work around for this?
June 27, 2011 - 11:37 am UTC
you'll have to change the requirement.
it really seems to me that you do not want a materialized view - you really want to partition your audit trail table so that when you full scan it for reporting on the last year - you are only scanning the last 12-13 months of data. If you range partition by week or month, you'll be able to isolate your last years worth of data - and easily purge stuff older than 11 year.
You cannot fast refresh a materialized view with SYSDATE in it.
materialized performance
A reader, August 11, 2011 - 4:59 pm UTC
I have a simple Materialized View which processes only about 400k rows and materializes into about 100k rows.
select *
from (select a.*, row_number() over (partition by...) rnum
from tab_a a, tab_b b
where b.key = a.b_key
) tvw
where rn = 1
and exists (select * from tab_c c where c.val = tvw.val)
The MV is not part of a refresh group and it's set to REFRESH COMPLETE ON DEMAND, there are NO MV logs on the base tables.
When I call mview.refresh, it takes 10 minutes to come back. When I try to do a create table as select...using the same query as the MV, it comes back in under 10 seconds.
Any ideas where I should be looking for the big difference? Both would seem to be processing and creating the same number of rows, why would a CTAS be so much quicker?
Initially, I thought it may be because MV is doing a delete, but it should be doing a truncate as you suggested above?
August 14, 2011 - 8:09 pm UTC
no version :(
in 10g and above, a complete refresh is "delete + insert then commit"
in 9i and before, a complete refresh was "truncate (and hence commit) + direct path load (insert /*+ APPEND */) then commit"
Likely you are 10g and above and are comparing the performance of DELETE+INSERT (into a likely indexed table - correct?) versus a create table as select (no indexes to maintain there)
so, you are comparing apples to flying toaster ovens (educated guess - you don't give us much - really nothing - to work with as far as facts go here).
Try atomic_refresh => false on the refresh call - that'll have it use a truncate+insert/*+ APPEND */. Be aware that indexes will be maintained (i assume you have some... doesn't matter or change my answer if you don't however, try it)
materialize refresh taking longer than CTAS
A reader, August 12, 2011 - 4:12 am UTC
I've identified the problem - it was a BITMAP index on the MV.
Why though?
I understand locking issues with BITMAPs in an OLTP environment, but what else could be competing with a MV refresh?
August 14, 2011 - 8:23 pm UTC
it isn't competing, it was index maintenance PLUS delete PLUS insert
versus "just create the table data"
You were comparing apples with flying toaster ovens for sure.
try atomic refresh => false.
fast refresh quicker than ctas
A reader, August 17, 2011 - 4:33 am UTC
Thank you.
We're on 10g R2.
But by changing the index definition from BITMAP to standard B-Tree, it goes back down from 10minutes to 16seconds, why?
Also how do you specify atomic refresh => false if the MV was created with a refresh interval of every 5 minutes and not manually refreshed using dbms_mview.refresh?
August 17, 2011 - 4:18 pm UTC
because bitmaps are somewhat harder/more complex to maintain than standard b*trees.
If you 'fast' refresh - it does a row by row refresh - which is *painfully* bad for a bitmap index, horrifically bad - undoably bad.
You would just use dbms_job directly to schedule the refresh instead of using the refresh interval on the materialized view.
A reader, November 30, 2011 - 3:11 pm UTC
Tom,
Is it necessary that users should not post any transactions to MV related table when MV fast refresh is running for that table?
December 01, 2011 - 7:45 am UTC
what do you mean by the "MV related table"
if you mean the materialized view itself, yes, during the refresh - it will be locked.
(think about it - during the refresh - any changes made to it would "disappear" anyway - the refreshes goal is to make the materialized view look *exactly* like the base table)
A reader, December 01, 2011 - 9:06 am UTC
What I meant was, if we have a table A and we have created a mv i.e. A_MV based on table A then can a user post any transactions to table A when refresh for A_MV is going on.
If yes, then what will happen to the posted transaction in table A, which got posted after mv refresh started, will that be considered in next refresh?
Also, we are talking about point in time refresh here.
December 06, 2011 - 10:32 am UTC
it is not locked (we couldn't have refresh fast ON COMMIT with more than user if it were!)
the changes are queued up in the materialized view log - the other session doing the refresh won't see them (consistent read - they only see what was committed when their query against the log was opened, they won't see any newly created rows whether they are committed or not)
WITH COMMIT SCN
Rajeshwaran Jeyabal, August 01, 2021 - 5:19 am UTC
August 02, 2021 - 7:00 am UTC
Your mileage may vary but here's a demo
SQL>
SQL> create table t as select * from dba_objects
2 where object_id is not null;
Table created.
SQL> alter table t add primary key ( object_id );
Table altered.
SQL>
SQL> --create materialized view log on t with commit scn;
SQL> create materialized view log on t;
Materialized view log created.
SQL>
SQL> create materialized view mv
2 refresh fast as
3 select owner, object_id, object_name, created
4 from t
5 where last_ddl_time is not null;
Materialized view created.
SQL>
SQL> begin
2 for i in 1 .. 10 loop
3 delete from t where rownum <= 50; commit;
4 update t set owner = lower(owner) where rownum <= 50; commit;
5 declare
6 c int := 0;
7 r t%rowtype;
8 begin
9 for i in ( select * from t where object_id > 0 ) loop
10 c := c + 1 ; exit when c > 50;
11 r := i;
12 r.object_id := -r.object_id;
13 insert into t values r;
14 end loop;
15 end;
16 commit;
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('','T');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('','MV');
PL/SQL procedure successfully completed.
SQL>
SQL> -- exec dbms_monitor.session_trace_enable;
SQL> set timing on
SQL> exec dbms_mview.refresh('mv');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.93
SQL> -- exec dbms_monitor.session_trace_disable;
SQL>
SQL>
SQL>
SQL>
SQL> create table t as select * from dba_objects
2 where object_id is not null;
Table created.
Elapsed: 00:00:00.32
SQL> alter table t add primary key ( object_id );
Table altered.
Elapsed: 00:00:00.07
SQL>
SQL> create materialized view log on t with commit scn;
Materialized view log created.
Elapsed: 00:00:00.00
SQL> -- create materialized view log on t;
SQL>
SQL> create materialized view mv
2 refresh fast as
3 select owner, object_id, object_name, created
4 from t
5 where last_ddl_time is not null;
Materialized view created.
Elapsed: 00:00:00.15
SQL>
SQL> begin
2 for i in 1 .. 10 loop
3 delete from t where rownum <= 50; commit;
4 update t set owner = lower(owner) where rownum <= 50; commit;
5 declare
6 c int := 0;
7 r t%rowtype;
8 begin
9 for i in ( select * from t where object_id > 0 ) loop
10 c := c + 1 ; exit when c > 50;
11 r := i;
12 r.object_id := -r.object_id;
13 insert into t values r;
14 end loop;
15 end;
16 commit;
17 end loop;
18 end;
19 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
SQL> exec dbms_stats.gather_table_stats('','T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.21
SQL> exec dbms_stats.gather_table_stats('','MV');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07
SQL>
SQL> -- exec dbms_monitor.session_trace_enable;
SQL> set timing on
SQL> exec dbms_mview.refresh('mv');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.66
SQL> -- exec dbms_monitor.session_trace_disable;
SQL>
SQL>
SQL>
WITH COMMIT SCN
Rajeshwaran Jeyabal, August 02, 2021 - 1:46 pm UTC
Thanks for the test case, but can you also please explain why "WITH COMMIT SCN" goes faster,
a) what was the technical reason behind that ?
b) also what was the business use case, that one should consider using this?
c) why don't oracle goes as the default option then?
demo@XEPDB1> create materialized view log on t;
Materialized view log created.
demo@XEPDB1> select commit_scn_based
2 from user_mview_logs
3 where master ='T';
COM
---
NO
on-commit fast refresh mview is extremely slow
Paul, August 06, 2021 - 5:55 am UTC
Greetings!
Database version: 12.1.0.2
While doing test with on-commit fast refresh materialized view we are seeing update record completes in no time but commit is taking more than 6 minutes to complete.
Can you please provide insight on the cause of the slowness and how to resolve this issue. I have provided below the TKPROF report from 10046 trace. Most of the time is spent on the
1) I tried with locking all materialized view logs involved in the materialized view tables but no difference in performance.
===
SQL>
SQL> update PSG.REACHABLE_URLS set REC_DATE_END = systimestamp where src = 'PSG' and url = 'https://www.sprint.com/about/careers/benefits';
1 row updated.
Elapsed: 00:00:00.05
SQL> commit ;
Commit complete.
Elapsed: 00:06:21.49
SQL ID: 3q2ytq8c7669f Plan Hash: 219098488
DELETE /*+ BYPASS_UJVC */ FROM "PSG"."RB_DV_PRG_MAT" "SNA$"
WHERE
"SNA$"."URID" IS NULL AND "SNA$"."ERID" IN (SELECT /*+ NO_MERGE ("JV$") */
"MAS$5".ROWID FROM ( SELECT "MAS$"."ROWID" "RID$" , "MAS$".* FROM
"PSG"."REACHABLE_URLS" "MAS$" WHERE ROWID IN (SELECT /*+ HASH_SJ */
CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM
"PSG"."MLOG$_REACHABLE_URLS" "MAS$" WHERE "MAS$".XID$$ = :1 AND NOT
("MAS$".DMLTYPE$$ = 'U' AND
(sys.dbms_snapshot_utl.vector_compare(:B_CV0,
"MAS$".change_vector$$) = 0)))) "JV$",
"PSG"."RB_DV_PRG" AS OF SNAPSHOT(:B_SCN) "MAS$5" WHERE
LOWER("JV$"."URL")=LOWER("MAS$5"."src_LINK") AND
"JV$"."REC_DATE_END">TO_DATE('50000101','YYYYMMDD'))
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 5 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 5 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 569 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row src Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE RB_DV_PRG_MAT (cr=3 pr=0 pw=0 time=325 us)
0 0 0 HASH JOIN RIGHT SEMI (cr=3 pr=0 pw=0 time=324 us cost=3066 size=1408 card=44)
0 0 0 VIEW VW_NSO_1 (cr=3 pr=0 pw=0 time=219 us cost=3021 size=7104 card=592)
0 0 0 HASH JOIN (cr=3 pr=0 pw=0 time=219 us cost=3021 size=1247344 card=592)
0 0 0 VIEW (cr=3 pr=0 pw=0 time=97 us cost=2 size=2015 card=1)
0 0 0 NESTED LOOPS (cr=3 pr=0 pw=0 time=97 us cost=2 size=390 card=1)
2 2 2 TABLE ACCESS BY INDEX ROWID BATCHED MLOG$_REACHABLE_URLS (cr=2 pr=0 pw=0 time=86 us cost=1 size=273 card=1)
2 2 2 INDEX RANGE SCAN I_MLOG$_REACHABLE_URLS (cr=1 pr=0 pw=0 time=8 us cost=1 size=0 card=1)(object id 11612850)
0 0 0 TABLE ACCESS BY USER ROWID REACHABLE_URLS (cr=1 pr=0 pw=0 time=7 us cost=1 size=117 card=1)
0 0 0 TABLE ACCESS STORAGE FULL RB_DV_PRG (cr=0 pr=0 pw=0 time=0 us cost=3019 size=5448056 card=59218)
0 0 0 MAT_VIEW ACCESS STORAGE FULL RB_DV_PRG_MAT (cr=0 pr=0 pw=0 time=0 us cost=45 size=87600 card=4380)
********************************************************************************
SQL ID: g0tyfhtk93mra Plan Hash: 2113898825
INSERT /*+ NOAPPEND */ INTO "PSG"."RB_DV_PRG_MAT" SELECT /*+
NO_MERGE("JV$") */ "MAS$5"."ORGANIZATION_ID","MAS$5"."RECORDED_DATE_START",
"MAS$5"."src",EXTRACT(YEAR FROM "MAS$5"."EFFECTIVE_DATE_START"),
"MAS$5"."TYPE_CODE","MAS$5"."SUB_TYPE_CODE",NVL("MAS$3"."LOOKUP_DETAIL",
"MAS$5"."INDICATOR_CODE"),"MAS$5"."COMMENTS","MAS$5"."PCT",
"MAS$5"."MEDIAN_VALUE","MAS$5"."MEAN_VALUE",NVL("MAS$1"."LOOKUP_DETAIL",
"MAS$5"."src_SUB_TYPE_CODE"),"JV$"."URL","MAS$5"."src_SECTION",
"MAS$5".ROWID,"MAS$4".ROWID,"MAS$3".ROWID,"MAS$2".ROWID,"MAS$1".ROWID,
"JV$"."RID$" FROM ( SELECT "MAS$"."ROWID" "RID$" , "MAS$".* FROM
"PSG"."REACHABLE_URLS" "MAS$" WHERE ROWID IN (SELECT /*+ HASH_SJ */
CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM
"PSG"."MLOG$_REACHABLE_URLS" "MAS$" WHERE "MAS$".XID$$ = :1 AND NOT
("MAS$".DMLTYPE$$ = 'U' AND
(sys.dbms_snapshot_utl.vector_compare(:B_CV0,
"MAS$".change_vector$$) = 0)))) "JV$",
"PSG"."PSG_REF" AS OF SNAPSHOT(:B_SCN) "MAS$1",
"PSG"."PSG_MAP_REML_TO_PSG" AS OF SNAPSHOT(:B_SCN) "MAS$2",
"PSG"."PSG_REF" AS OF SNAPSHOT(:B_SCN) "MAS$3",
"PSG"."PSG_MAP_REML_TO_PSG" AS OF SNAPSHOT(:B_SCN) "MAS$4",
"PSG"."RB_DV_PRG" AS OF SNAPSHOT(:B_SCN) "MAS$5" WHERE
("MAS$5"."REC_DATE_END">TO_DATE('50000101','YYYYMMDD') AND
"MAS$4"."PSG_TABLE_NAME"='RB_DV_PRG' AND "MAS$4"."PSG_COLUMN_NAME"=
'INDICATOR_CODE' AND "MAS$3"."LOOKUP_CODE"(+)="MAS$5"."INDICATOR_CODE" AND
"MAS$3"."LOOKUP_REFERENCE"(+)="MAS$4"."LOOKUP_REFERENCE" AND
"MAS$2"."PSG_TABLE_NAME"='RB_DV_PRG' AND "MAS$2"."PSG_COLUMN_NAME"=
'src_SUB_TYPE_CODE' AND "MAS$1"."LOOKUP_CODE"(+)=
"MAS$5"."src_SUB_TYPE_CODE" AND "MAS$1"."LOOKUP_REFERENCE"(+)=
"MAS$2"."LOOKUP_REFERENCE" AND LOWER("JV$"."URL"(+))=
LOWER("MAS$5"."src_LINK") AND "JV$"."REC_DATE_END"(+)
>TO_DATE('50000101','YYYYMMDD')) AND NOT EXISTS ( SELECT 1 FROM
"PSG"."RB_DV_PRG_MAT" "SNA2$" WHERE ("SNA2$"."ERID" = "MAS$5".ROWID)
AND ("SNA2$"."MRID1" = "MAS$4".ROWID OR "MAS$4".ROWID IS NULL ) AND
("SNA2$"."RRID1" = "MAS$3".ROWID OR "MAS$3".ROWID IS NULL ) AND
("SNA2$"."MRID10" = "MAS$2".ROWID OR "MAS$2".ROWID IS NULL ) AND
("SNA2$"."RRID10" = "MAS$1".ROWID OR "MAS$1".ROWID IS NULL ) AND "JV$".RID$
IS NULL) AND NOT EXISTS ( SELECT 1 FROM "PSG"."REACHABLE_URLS"
"MAS_INNER$", "PSG"."RB_DV_PRG" AS OF SNAPSHOT(:B_SCN)
"MAS_OUTER$" WHERE "MAS$5".ROWID = "MAS_OUTER$".ROWID AND "JV$".RID$ IS
NULL AND LOWER("MAS_INNER$"."URL")=LOWER("MAS_OUTER$"."src_LINK") AND
"MAS_INNER$"."REC_DATE_END">TO_DATE('50000101','YYYYMMDD'))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 488.64 496.80 3095 101298679 38 15
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 488.64 496.80 3095 101298679 38 15
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 569 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row src Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL RB_DV_PRG_MAT (cr=101298671 pr=3095 pw=0 time=329252938 us)
15 15 15 FILTER (cr=101298670 pr=3095 pw=0 time=460261444 us)
59174 59174 59174 HASH JOIN RIGHT OUTER (cr=4759 pr=3095 pw=0 time=3942774 us cost=2009 size=25463568 card=37011)
2125 2125 2125 TABLE ACCESS STORAGE FULL PSG_REF_V24 (cr=23 pr=21 pw=0 time=21699 us cost=21 size=121125 card=2125)
59174 59174 59174 HASH JOIN RIGHT OUTER (cr=4736 pr=3074 pw=0 time=3786391 us cost=1988 size=23353941 card=37011)
2125 2125 2125 TABLE ACCESS STORAGE FULL PSG_REF_V24 (cr=23 pr=0 pw=0 time=228 us cost=21 size=121125 card=2125)
59174 59174 59174 HASH JOIN RIGHT OUTER (cr=4713 pr=3074 pw=0 time=3626103 us cost=1966 size=21244314 card=37011)
0 0 0 VIEW (cr=1637 pr=0 pw=0 time=38061 us cost=24 size=117 card=1)
0 0 0 HASH JOIN SEMI (cr=1637 pr=0 pw=0 time=38059 us cost=72 size=390 card=1)
62874 62874 62874 TABLE ACCESS STORAGE FULL REACHABLE_URLS (cr=1635 pr=0 pw=0 time=9731 us cost=22 size=4858425 card=41525)
2 2 2 TABLE ACCESS BY INDEX ROWID BATCHED MLOG$_REACHABLE_URLS (cr=2 pr=0 pw=0 time=146 us cost=1 size=273 card=1)
2 2 2 INDEX RANGE SCAN I_MLOG$_REACHABLE_URLS (cr=1 pr=0 pw=0 time=16 us cost=1 size=0 card=1)(object id 11612850)
59174 59174 59174 NESTED LOOPS (cr=3076 pr=3074 pw=0 time=3388605 us cost=1943 size=16914027 card=37011)
1 1 1 NESTED LOOPS (cr=6 pr=4 pw=0 time=13084 us cost=3 size=106 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID PSG_MAP_REML_TO_PSG_V24 (cr=3 pr=3 pw=0 time=12527 us cost=2 size=53 card=1)
1 1 1 INDEX UNIQUE SCAN PSG_MAP_REML_TO_PSG_V24_PK (cr=2 pr=2 pw=0 time=760 us cost=1 size=0 card=1)(object id 11611101)
1 1 1 TABLE ACCESS BY INDEX ROWID PSG_MAP_REML_TO_PSG_V24 (cr=3 pr=1 pw=0 time=553 us cost=1 size=53 card=1)
1 1 1 INDEX UNIQUE SCAN PSG_MAP_REML_TO_PSG_V24_PK (cr=2 pr=0 pw=0 time=15 us cost=0 size=0 card=1)(object id 11611101)
59174 59174 59174 TABLE ACCESS BY INDEX ROWID BATCHED RB_DV_PRG (cr=3070 pr=3070 pw=0 time=3358890 us cost=1940 size=12990861 card=37011)
59174 59174 59174 INDEX RANGE SCAN RB_DV_PRG_IDX5 (cr=159 pr=159 pw=0 time=292941 us cost=99 size=0 card=37011)(object id 11611103)
59159 59159 59159 FILTER (cr=101267371 pr=0 pw=0 time=491433495 us)
59159 59159 59159 MAT_VIEW ACCESS STORAGE FULL FIRST ROWS RB_DV_PRG_MAT (cr=101267371 pr=0 pw=0 time=491344430 us cost=45 size=50 card=1)
0 0 0 FILTER (cr=26540 pr=0 pw=0 time=1108042 us)
0 0 0 NESTED LOOPS (cr=26540 pr=0 pw=0 time=1108009 us cost=3 size=394 card=2)
15 15 15 TABLE ACCESS BY USER ROWID RB_DV_PRG (cr=15 pr=0 pw=0 time=173 us cost=1 size=92 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED REACHABLE_URLS (cr=26525 pr=0 pw=0 time=1107769 us cost=13 size=210 card=2)
943110 943110 943110 INDEX RANGE SCAN PSG_REACHABLE_IDX5 (cr=2525 pr=0 pw=0 time=128172 us cost=1 size=0 card=201)(object id 11611241)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
gc cr grant 2-way 1557 0.00 0.23
cell single block physical read 3075 0.01 1.75
gc cr multi block request 3 0.00 0.00
cell multiblock physical read 3 0.01 0.01
gc current multi block request 1 0.00 0.00
gcs enter server mode 6 0.00 0.00
latch: cache buffers chains 1 0.00 0.00
row cache lock 3 0.00 0.00
********************************************************************************
August 09, 2021 - 5:43 am UTC
Try gathering stats on your mview log just before you do the refresh, and then, if that is a typical size, look at locking those stats.
Alternatively, delete the stats on the log and lock them so that dynamic sampling comes into play.
Let us know how that goes.
on-commit fast refresh MVIEW is extremely slow
Paul, August 10, 2021 - 4:58 am UTC
Hi Connor
>>
Try gathering stats on your mview log just before you do the refresh, and then, if that is a typical size, look at locking those stats.
Alternatively, delete the stats on the log and lock them so that dynamic sampling comes into play.
Let us know how that goes.
>>
This is on-commit fast refresh. I am just updating one row in a small table. and then commit. Commit takes for ever (more than 6 minutes to complete).
Tried both your recommendations. but still commit is taking long time. I am seeing the same internal recursive SQL in 10046 trace shared earlier in my previous update.
August 11, 2021 - 6:32 am UTC
can we see the definition of the mview please
on-commit mview refresh definition
Paul, August 11, 2021 - 6:49 am UTC
Here is the mview definition:
CREATE MATERIALIZED VIEW "PSG"."RB_DV_PRG_NEW_XXX" ("ORGANIZATION_ID", "RECORDED_DATE_START", "SOURCE", "FOR_YEAR", "TYPE_CODE", "SUB_TYPE_CODE", "INDICATOR_CODE", "COMMENTS", "PCT", "MEDIAN_VALUE", "MEAN_VALUE", "SOURCESUBTYPECODE", "SOURCELINK", "SOURCESECTION", "ERID", "MRID1", "RRID1", "MRID10", "RRID10", "URID")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255
NOCOMPRESS NOLOGGING
TABLESPACE "PSG_DATA"
BUILD IMMEDIATE
USING INDEX
REFRESH FAST ON COMMIT
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS ENABLE QUERY REWRITE
AS SELECT BASE.ORGANIZATION_ID,
BASE.RECORDED_DATE_START,
BASE.SOURCE,
EXTRACT (YEAR FROM BASE.EFFECTIVE_DATE_START)
AS FOR_YEAR,
TYPE_CODE,
BASE.SUB_TYPE_CODE,
NVL (REF1.LOOKUP_DETAIL, BASE.INDICATOR_CODE)
AS INDICATOR_CODE,
BASE.COMMENTS,
BASE.PCT,
BASE.MEDIAN_VALUE,
BASE.MEAN_VALUE,
NVL (REF10.LOOKUP_DETAIL, BASE.SOURCE_SUB_TYPE_CODE)
AS SOURCESUBTYPECODE,
REACH.URL
AS SOURCELINK,
BASE.SOURCE_SECTION
AS SOURCESECTION,
BASE.ROWID
AS erid,
MAP1.ROWID
AS mrid1,
REF1.ROWID
AS rrid1,
MAP10.ROWID
AS mrid10,
REF10.ROWID
AS rrid10,
REACH.ROWID
AS urid
FROM PSG.RB_DV_PRG_NEW BASE,
PSG.PSG_MAP_REML_TO_PSG MAP1,
PSG.PSG_REFERENCES REF1,
PSG.PSG_MAP_REML_TO_PSG MAP10,
PSG.PSG_REFERENCES REF10,
PSG.PSG_REACHABLE_URLS reach
WHERE BASE.RECORDED_DATE_END > TO_DATE ('50000101', 'YYYYMMDD')
AND MAP1.PSG_TABLE_NAME = 'RML_BEN_DIV_PROG'
AND MAP1.PSG_COLUMN_NAME = 'INDICATOR_CODE'
AND REF1.LOOKUP_CODE(+) = BASE.INDICATOR_CODE
AND REF1.LOOKUP_REFERENCE(+) = MAP1.LOOKUP_REFERENCE
AND MAP10.PSG_TABLE_NAME = 'RML_BEN_DIV_PROG'
AND MAP10.PSG_COLUMN_NAME = 'SOURCE_SUB_TYPE_CODE'
AND REF10.LOOKUP_CODE(+) = BASE.SOURCE_SUB_TYPE_CODE
AND REF10.LOOKUP_REFERENCE(+) = MAP10.LOOKUP_REFERENCE
AND LOWER (REACH.URL(+)) = LOWER (BASE.SOURCE_LINK)
AND REACH.RECORDED_DATE_END(+) > TO_DATE ('50000101', 'YYYYMMDD');
August 13, 2021 - 7:34 am UTC
Can you try the refresh after doing the following:
alter session set "_mv_refresh_use_stats" = false;
I need Help please suggest me.
Alok Verma, August 11, 2021 - 8:11 pm UTC
Hello Sir,
Please suggest me interviewer ask me how to do validation
at everyday on 07 pm to 07 am any DML statement are run for XYZ table then message will display you can't perform any DML operation between 07 pm to 07 am.
please sir guide me.
I used below query but did not work this query
select case when a BETWEEN b and c then 1 else 0 end h,a,b,c
--case when b>=a and c<=a then 1 else 0 end k
from (
select to_date(to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss') a,
to_date(to_char(to_date(to_char(sysdate,'dd-mm-yyyy')|| ' 07:00:00 pm','dd-mm-yyyy hh:mi:ss pm'),'dd-mm-yyyy hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss') b,
to_date(to_char(to_date(to_char(sysdate+1,'dd-mm-yyyy')||' 07:00:00 Am','dd-mm-yyyy hh:mi:ss Am'),'dd-mm-yyyy hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss') c
from dual
);
after 11:59 pm that query does not work, if it's same day then it will work.
August 13, 2021 - 12:28 am UTC
We'll cover this on next week's DBA office hours.
on-commit mview refresh performance issue
Paul, August 13, 2021 - 1:08 am UTC
Hi Connor,
I have posted the MVIEW DDL on follow-up to your question - "can we see the definition of the mview please"
Can you provide your insight.
Thanks!
on-commit fast refresh performance issue
Paul, August 17, 2021 - 1:18 am UTC
Hi Connor,
>>Can you try the refresh after doing the following:
>> alter session set "_mv_refresh_use_stats" = false;
I did try your recommendation but its still taking 4+ minutes during the commit. 10046 trace shows the same internal recursive SQL with the same plan.
complete refresh of the same mview takes about 3 seconds.
Thanks
August 17, 2021 - 2:48 pm UTC
The problem comes on this line:
MAT_VIEW ACCESS STORAGE FULL FIRST ROWS RB_DV_PRG_MAT (cr=101267371 pr=0 pw=0 time=491344430 us cost=45 size=50 card=1)
That's 491s which accounts for almost all the runtime.
This does 101 million gets to fetch ~60k rows - which is excessive!
Figuring out why this scan does so much work and avoiding that effort is likely the key to speeding this up.
- Is the optimizer's row estimate (1 row) for the MV (roughly) accurate and - if not - does improving this help (seems no)
- Is the MV consuming much more space than the number of rows in it would suggest? Check *_segments and try shrinking/moving it if the usage is excessive
- Does creating index(es) on the MV columns in the inserts not exist clause help?
and not exists (
select 1
from "PSG"."RB_DV_PRG_MAT" "SNA2$"
where ("SNA2$"."ERID" = "MAS$5".rowid)
and ("SNA2$"."MRID1" = "MAS$4".rowid
or "MAS$4".rowid is null)
and ("SNA2$"."RRID1" = "MAS$3".rowid
or "MAS$3".rowid is null)
and ("SNA2$"."MRID10" = "MAS$2".rowid
or "MAS$2".rowid is null)
and ("SNA2$"."RRID10" = "MAS$1".rowid
or "MAS$1".rowid is null)
and "JV$".rid$ is null
)
Finally:
3s is relatively quick for a complete refresh - can you use that instead?
on-commit fast refresh performance issue
Paul, August 20, 2021 - 5:20 am UTC
Hi Chris,
Thanks!
>>
- Does creating index(es) on the MV columns in the inserts not exist clause help?
and not exists (
select 1
from "PSG"."RB_DV_PRG_MAT" "SNA2$"
where ("SNA2$"."ERID" = "MAS$5".rowid)
and ("SNA2$"."MRID1" = "MAS$4".rowid
or "MAS$4".rowid is null)
and ("SNA2$"."RRID1" = "MAS$3".rowid
or "MAS$3".rowid is null)
and ("SNA2$"."MRID10" = "MAS$2".rowid
or "MAS$2".rowid is null)
and ("SNA2$"."RRID10" = "MAS$1".rowid
or "MAS$1".rowid is null)
and "JV$".rid$ is null
)
>>
as per your suggestion, created indexes on those MV columns. Wow, it really improved the on-commit fast refresh performance. it took about less than a second now with on-commit fast refresh!
Thanks much!
Here is the 10046 trace:
********************************************************************************
SQL ID: 1zfr4z4q2sgz4 Plan Hash: 3275388845
INSERT /*+ NOAPPEND */ INTO "PSG3"."RB_DV_PRG_MAT" SELECT /*+
NO_MERGE("JV$") */ "MAS$5"."ORGANIZATION_ID","MAS$5"."RECORDED_DATE_START",
"MAS$5"."SOURCE",EXTRACT(YEAR FROM "MAS$5"."EFFECTIVE_DATE_START"),
"MAS$5"."TYPE_CODE","MAS$5"."SUB_TYPE_CODE",NVL("MAS$3"."LOOKUP_DETAIL",
"MAS$5"."INDICATOR_CODE"),"MAS$5"."COMMENTS","MAS$5"."PCT",
"MAS$5"."MEDIAN_VALUE","MAS$5"."MEAN_VALUE",NVL("MAS$1"."LOOKUP_DETAIL",
"MAS$5"."SOURCE_SUB_TYPE_CODE"),"JV$"."URL","MAS$5"."SOURCE_SECTION",
"MAS$5".ROWID,"MAS$4".ROWID,"MAS$3".ROWID,"MAS$2".ROWID,"MAS$1".ROWID,
"JV$"."RID$" FROM ( SELECT "MAS$"."ROWID" "RID$" , "MAS$".* FROM
"PSG3"."PSG_REACHABLE_URLS" "MAS$" WHERE ROWID IN (SELECT
CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM
"PSG3"."MLOG$_PSG_REACHABLE_URLS" "MAS$" WHERE "MAS$".XID$$ = :1 AND NOT
("MAS$".DMLTYPE$$ = 'U' AND
(sys.dbms_snapshot_utl.vector_compare(:B_CV0,
"MAS$".change_vector$$) = 0)))) "JV$",
"PSG3"."PSG_REFERENCES" AS OF SNAPSHOT(:B_SCN) "MAS$1",
"PSG3"."PSG_MAP_REML_TO_PSG" AS OF SNAPSHOT(:B_SCN) "MAS$2",
"PSG3"."PSG_REFERENCES" AS OF SNAPSHOT(:B_SCN) "MAS$3",
"PSG3"."PSG_MAP_REML_TO_PSG" AS OF SNAPSHOT(:B_SCN) "MAS$4",
"PSG3"."RB_DV_PRG" AS OF SNAPSHOT(:B_SCN) "MAS$5" WHERE
("MAS$5"."RECORDED_DATE_END">TO_DATE('50000101','YYYYMMDD') AND
"MAS$4"."PSG_TABLE_NAME"='RML_BEN_DIV_PROG' AND "MAS$4"."PSG_COLUMN_NAME"=
'INDICATOR_CODE' AND "MAS$3"."LOOKUP_CODE"(+)="MAS$5"."INDICATOR_CODE" AND
"MAS$3"."LOOKUP_REFERENCE"(+)="MAS$4"."LOOKUP_REFERENCE" AND
"MAS$2"."PSG_TABLE_NAME"='RML_BEN_DIV_PROG' AND "MAS$2"."PSG_COLUMN_NAME"=
'SOURCE_SUB_TYPE_CODE' AND "MAS$1"."LOOKUP_CODE"(+)=
"MAS$5"."SOURCE_SUB_TYPE_CODE" AND "MAS$1"."LOOKUP_REFERENCE"(+)=
"MAS$2"."LOOKUP_REFERENCE" AND LOWER("JV$"."URL"(+))=
LOWER("MAS$5"."SOURCE_LINK") AND "JV$"."RECORDED_DATE_END"(+)
>TO_DATE('50000101','YYYYMMDD')) AND NOT EXISTS ( SELECT 1 FROM
"PSG3"."RB_DV_PRG_MAT" "SNA2$" WHERE ("SNA2$"."ERID" =
"MAS$5".ROWID) AND ("SNA2$"."MRID1" = "MAS$4".ROWID OR "MAS$4".ROWID IS
NULL ) AND ("SNA2$"."RRID1" = "MAS$3".ROWID OR "MAS$3".ROWID IS NULL ) AND
("SNA2$"."MRID10" = "MAS$2".ROWID OR "MAS$2".ROWID IS NULL ) AND
("SNA2$"."RRID10" = "MAS$1".ROWID OR "MAS$1".ROWID IS NULL ) AND "JV$".RID$
IS NULL) AND NOT EXISTS ( SELECT 1 FROM "PSG3"."PSG_REACHABLE_URLS"
"MAS_INNER$", "PSG3"."RB_DV_PRG" AS OF SNAPSHOT(:B_SCN)
"MAS_OUTER$" WHERE "MAS$5".ROWID = "MAS_OUTER$".ROWID AND "JV$".RID$ IS
NULL AND LOWER("MAS_INNER$"."URL")=LOWER("MAS_OUTER$"."SOURCE_LINK") AND
"MAS_INNER$"."RECORDED_DATE_END">TO_DATE('50000101','YYYYMMDD'))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.50 0.61 3127 122583 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.50 0.62 3127 122583 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 569 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL RB_DV_PRG_MAT (cr=122583 pr=3127 pw=0 time=604002 us)
0 0 0 FILTER (cr=122583 pr=3127 pw=0 time=603992 us)
59169 59169 59169 HASH JOIN RIGHT OUTER (cr=2980 pr=2945 pw=0 time=288191 us cost=3028 size=40530765 card=59169)
2125 2125 2125 TABLE ACCESS STORAGE FULL PSG_REFERENCES_V24 (cr=23 pr=21 pw=0 time=4172 us cost=21 size=121125 card=2125)
59169 59169 59169 HASH JOIN RIGHT OUTER (cr=2957 pr=2924 pw=0 time=230268 us cost=3007 size=37158132 card=59169)
2125 2125 2125 TABLE ACCESS STORAGE FULL PSG_REFERENCES_V24 (cr=23 pr=0 pw=0 time=205 us cost=21 size=121125 card=2125)
59169 59169 59169 HASH JOIN RIGHT OUTER (cr=2934 pr=2924 pw=0 time=170934 us cost=2986 size=33785499 card=59169)
0 0 0 VIEW (cr=3 pr=0 pw=0 time=153 us cost=4 size=117 card=1)
0 0 0 NESTED LOOPS (cr=3 pr=0 pw=0 time=152 us cost=4 size=151 card=1)
1 1 1 SORT UNIQUE (cr=2 pr=0 pw=0 time=144 us cost=2 size=34 card=1)
2 2 2 TABLE ACCESS BY INDEX ROWID BATCHED MLOG$_PSG_REACHABLE_URLS (cr=2 pr=0 pw=0 time=126 us cost=2 size=34 card=1)
2 2 2 INDEX RANGE SCAN I_MLOG$_PSG_REACHABLE_URLS (cr=1 pr=0 pw=0 time=14 us cost=1 size=0 card=2)(object id 11625453)
0 0 0 TABLE ACCESS BY USER ROWID PSG_REACHABLE_URLS (cr=1 pr=0 pw=0 time=6 us cost=1 size=117 card=1)
59169 59169 59169 NESTED LOOPS (cr=2931 pr=2924 pw=0 time=110154 us cost=2981 size=26862726 card=59169)
1 1 1 NESTED LOOPS (cr=6 pr=4 pw=0 time=4628 us cost=3 size=106 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID PSG_MAP_REML_TO_PSG_V24 (cr=3 pr=3 pw=0 time=4143 us cost=2 size=53 card=1)
1 1 1 INDEX UNIQUE SCAN PSG_MAP_REML_TO_PSG_V24_PK (cr=2 pr=2 pw=0 time=3573 us cost=1 size=0 card=1)(object id 11611101)
1 1 1 TABLE ACCESS BY INDEX ROWID PSG_MAP_REML_TO_PSG_V24 (cr=3 pr=1 pw=0 time=482 us cost=1 size=53 card=1)
1 1 1 INDEX UNIQUE SCAN PSG_MAP_REML_TO_PSG_V24_PK (cr=2 pr=0 pw=0 time=9 us cost=0 size=0 card=1)(object id 11611101)
59169 59169 59169 TABLE ACCESS STORAGE FULL RB_DV_PRG (cr=2925 pr=2920 pw=0 time=99007 us cost=2978 size=20590812 card=59169)
59169 59169 59169 FILTER (cr=119603 pr=182 pw=0 time=263903 us)
59169 59169 59169 MAT_VIEW ACCESS BY INDEX ROWID BATCHED RB_DV_PRG_MAT (cr=119603 pr=182 pw=0 time=246550 us cost=2 size=50 card=1)
59169 59169 59169 INDEX RANGE SCAN RB_DV_PRG_MAT_IDX1 (cr=60434 pr=182 pw=0 time=162225 us cost=1 size=0 card=1)(object id 11927917)
0 0 0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=3 size=197 card=1)
0 0 0 TABLE ACCESS BY USER ROWID RB_DV_PRG (cr=0 pr=0 pw=0 time=0 us cost=1 size=92 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED PSG_REACHABLE_URLS (cr=0 pr=0 pw=0 time=0 us cost=7 size=105 card=1)
0 0 0 INDEX RANGE SCAN PSG_REACHABLE_IDX5 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=100)(object id 11611241)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
gc cr grant 2-way 63 0.00 0.00
cell single block physical read 188 0.00 0.07
gc cr multi block request 23 0.00 0.00
cell multiblock physical read 42 0.00 0.04
row cache lock 3 0.00 0.00
********************************************************************************
August 20, 2021 - 7:42 am UTC
Great to hear, you're welcome