Re: "Throttling" a session's IO
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: Smith.Steven@xxxxxxx
- Date: Mon, 2 Apr 2007 11:35:30 -0700
On 4/2/07, Smith, Steven K - MSHA <Smith.Steven@xxxxxxx> wrote:
One option:
Does the data in the mview need to be accessible while it is refreshing?
Does this mview need to be refreshed in the same refresh group as other
mviews (data concurrency)? If the answer to these 2 questions is no,
then you can put this mview in it's own refresh group. This would cause
the refresh to use a 'truncate' instead of a 'delete' which doesn't
generate undo records.
My previous post links to an Oracle-L article that does not require a
separate refresh group, if such a thing is necessary. As an aside, I
did not realize that putting the MV in a separate refresh group would
cause a TRUNCATE rather than a DELETE.
The test I ran was a single MV, effectively in its own refresh group.
On 10g, the default method to remove data from the table during
a complete refresh was DELETE.
If you need access to the mview while it is refreshing, then create 2
mviews - _1 and _2 with a synonym of the original mview to point to _1
or _2. Refresh then on an alternating schedule and recreate the synonym
after the refresh completes. This isn't 100% accessibility to the
mview, but it is pretty close.
Another method that sounds very interesting was proposed by Gints Plivna,
and I believe it is in production use.
http://www.gplivna.eu/papers/mat_views_search.htm#_Toc141625502
Both method require you to use some method that will avoid killing
in flight queries when the data is refreshed.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
