RE: "Throttling" a session's IO

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.

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.

Steve Smith
Desk: 303-231-5499    

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hemant K Chitale Sent: Monday, April 02, 2007 9:29 AM
To: oracle-l_at_freelists.org
Subject: "Throttling" a session's IO

As an exercise , or thought experiment, how would I "throttle" (ie control the rate of)
an Oracle Database session's I/O ?

For CPU utilisation, I could use "nice" or "renice" but what about I/O ?

We have this Materialized View which generates more I/O when running the initial "DELETE" portion of a COMPLETE Refresh --- the DELETE generates a lot of UNDO and, more importantly, REDO (for the UNDO !). Some portions of the application that run against the same database are
"response-time sensitive" --- the application generates timeouts if it
doesn't receive a response to it's SQL within 30 seconds. Under normal circumstances, the application works well. However, when this Mview Refresh kicks in, we get occasional timeouts. Since the application supports a Manufacturing system, those timeouts are "not nice".

Now, my options, would be :

1. Rewrite / Redesign to support Fast Refresh's / split up into separate MViews etc --- will take some time
2. Move the target (Mview) to a different database -- not likely to be acceptable in terms of those who query the Mview
3. Redistribute I/O / Get a Faster I/O subsystem / Get a Larger cache -- the usual HARDWARE solutions

Wouldn't it be nice if I could "control" the rate of the DELETEs ?

If the DELETE and INSERT were custom built SQL code, I would probably put some "sleeps" between each batch of deletes. (as I do in some online backups -- use combinations of "sleep" and
"nice" in the
"cp" and "compress" commands)

However, a COMPLETE Refresh always does a complete DELETE in one SQL statement.

Hemant K Chitale

http://hemantoracledba.blogspot.com

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l