RE: Mview refresh is much slower in 10g
- From: "Smith, Steven K - MSHA" <Smith.Steven@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 Jan 2007 15:08:58 -0700
We set up 2 identical materialized views - *_MVW_1 and *_MVW_2
Refresh them on an alternating schedule. As part of the refresh jobs,
recreate a synonym pointing to the newly refreshed view as the last step
and use that synonym to reference the materialized view.
Steve Smith
Envision Technology Partners / MSHA MSIS Team
Desk: 303-231-5499
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of jaromir nemec
Sent: Thursday, January 18, 2007 3:03 PM
To: jkstill@xxxxxxxxx; gints.plivna@xxxxxxxxx
Cc: davidb158@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Mview refresh is much slower in 10g
Hi List,
> The last post in that thread (from Pavel Ruzicka) implies that
> the default refresh mechanism did indeed change from 9i->10g,
> but that there is a workaround.
Despite the fact that it is not documented change of the default
behaviour, I thing it is a good feature enabling a 24 hour accessible
full refreshable MV.
My question - are there experiences in 9i how to workaround the gap on
full refresh, where the MV is empty (other than simply say there is a
loading window, don't query the MV)?
regards,
Jaromir
----- Original Message -----
From: Jared Still <mailto:jkstill@xxxxxxxxx>
To: gints.plivna@xxxxxxxxx
Cc: davidb158@xxxxxxxxxxx ; oracle-l@xxxxxxxxxxxxx
Sent: Thursday, January 18, 2007 9:33 PM
Subject: Re: Mview refresh is much slower in 10g
On 1/17/07, Gints Plivna <gints.plivna@xxxxxxxxx> wrote:
Refresh method has been changed.
Previously (<9i) for complete refreshes Oracle did truncate mv and
insert /*+ append */.
Now (10g) it does delete, normal insert.
It is discussed for example here
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1569
5764787749
I didn't see in the AskTom article cited where it said that the
refresh mechanism changed from truncate/insert append to
delete and insert when moving from 9i to 10g.
It does say that the if the MV is part of a refresh group, a
complete refresh will be done as delete/insert, rather than
as truncate/append.
The last post in that thread (from Pavel Ruzicka) implies that
the default refresh mechanism did indeed change from 9i->10g,
but that there is a workaround.
I ran a small test to create an MV and do a complete refresh
on both 9.2.0.6 and 10.2.0.1 that confirms this behavior.
From the 9i trace:
PARSING IN CURSOR #15 len=56 dep=1 uid=45 oct=85 lid=45
tim=1141749498683672 hv=68464594 ad='5cad9640'
truncate table "JS001292"."MVTEST_MV" purge snapshot log
END OF STMT
...
INSERT /*+ APPEND */ INTO
"JS001292"."MVTEST_MV"("OWNER","TABLE_NAME","TABLESPACE_NAME") SELECT
"MVTEST"."OWNER","MVTEST"."TABLE_NAME","MVTEST"."TABL
ESPACE_NAME" FROM "MVTEST" "MVTEST"
END OF STMT
From the 10g trace:
PARSING IN CURSOR #15 len=35 dep=1 uid=56 oct=7 lid=56
tim=1141749792514219 hv=540326182 ad='733ed2d0'
delete from "JS001292"."MVTEST_MV"
END OF STMT
...
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO
"JS001292"."MVTEST_MV"("OWNER","TABLE_NAME","TABLESPACE_NAME") SELECT
"MVTEST"."OWNER","MVTEST"."TABLE_NAME
","MVTEST"."TABLESPACE_NAME" FROM "MVTEST" "MVTEST"
By changing the refresh method to set atomic_refresh = false, the
truncate/append
behavior can be restored.
begin
-- dbms_mview.refresh('MVTEST_MV',method => 'C');
-- use this with 10g to return to truncate/append behavior
dbms_mview.refresh('MVTEST_MV',method => 'C', atomic_refresh=>false);
end;
/
Here's the results of doing so in 10g:
PARSING IN CURSOR #24 len=57 dep=1 uid=56 oct=85 lid=56
tim=1141750173641027 hv=455978900 ad='705be890'
truncate table "JS001292"."MVTEST_MV" purge snapshot log
END OF STMT
...
PARSING IN CURSOR #24 len=208 dep=1 uid=56 oct=2 lid=56
tim=1141750173945788 hv=896677336 ad='6d21e8f0'
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND */ INTO
"JS001292"."MVTEST_MV"("OWNER","TABLE_NAME","TABLESPACE_NAME") SELECT
"MVTEST"."OWNER","MVTEST"."TA
BLE_NAME","MVTEST"."TABLESPACE_NAME" FROM "MVTEST" "MVTEST"
END OF STMT
Nice to know.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
