| MV refresh with atomic_refresh false [message #479478] |
Sun, 17 October 2010 16:24  |
sandhyaa
Messages: 79
Registered: February 2007
|
Member |
|
|
Experts,
I have one issue. Recently we migrated from 9.2.0.6 version db to 10.2.0.4.
After upgrading, the MVs refresh time increased considerably, I got to know that
MVs refreshing plan got changed (previously it used to TRUNCATE and then INSERT), now from 10g onwards it is DELETE and then INSERT, which can generate lot of redo and thereby increasing the refresh time.
In order to back to the usual behavior, I used:
dbms_mview.refresh('mv NAME', 'C', PARALLELISM=>4, ATOMIC_REFRESH=>FALSE);
Even after this, the refresh MV is taking very long time. I believe after changing the atomic_refresh to false is not becoming effective, because while the MVs were refreshing,
we would not be able to get the rows (previously), now even after changing atomic_refresh to false, if i do a select on the MV, i could get rows from the session.
Regards,
Sandhyaa
|
|
|
|
|
|
|
|
| Re: MV refresh with atomic_refresh false [message #479826 is a reply to message #479790] |
Tue, 19 October 2010 10:47  |
sandhyaa
Messages: 79
Registered: February 2007
|
Member |
|
|
OK. I think I did not ask my question properly -
My question was not specific to any Materialized view or query, let us say I create a materialized view using the below syntax:
CREATE MATERIALIZED VIEW APPS.TEMP_MV
PARALLEL ( DEGREE 4 INSTANCES 1 )
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
SELECT * FROM PER_ALL_PEOPLE_F WHERE SYSDATE BETWEEN
EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
When I was in 9.2.0.6 instance - whenever there was MV refresh, users were not able to see any data when they used to query the MV (while the refresh is in progress)
After upgrade to 10.2.0.4 instance - whenever there was MV refresh, users were able to query the data. The change in the behavior is because at the time of MV refresh, internally Oracle deletes and then inserts to provide read consistency (unlike the previous version). I got to know that to get back the previous behavior (truncate and then insert), I have to use atomic_refresh to FALSE,
I tried this to check the behavior -
dbms_mview.refresh('TEMP_MV', 'C', atomic_refresh=>FALSE);
When the refresh was happening, I could still query TEMP_MV and get records, so my question is - "IS THIS BEHAVIOR CORRECT?"
Please educate me...
Thanks
Sandhyaa
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
