Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: "Throttling" a session's IO

RE: "Throttling" a session's IO

From: Smith, Steven K - MSHA <Smith.Steven_at_DOL.GOV>
Date: Mon, 2 Apr 2007 13:21:18 -0600
Message-ID: <>

On 4/2/07, Smith, Steven K - MSHA <> 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.  

You are correct. Most of my experience is with 9i. From what I understand, though, you can change this behavior in 10g by supplying the ATOMIC_REFRESH parameter in dbms_mview.refresh. Setting it to FALSE will cause a truncate to occur.

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.

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

Steve Smith

Desk: 303-231-5499  

-- Received on Mon Apr 02 2007 - 14:21:18 CDT

Original text of this message