"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