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 :
 a. Rewrite / Redesign to support Fast Refresh's / split up into
separate MViews etc   --- will take some time
 b. Move the target (Mview) to a different database  -- not
likely to be acceptable in terms of those who query the Mview
 c. 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