Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

"Throttling" a session's IO

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Mon, 02 Apr 2007 23:28:53 +0800
Message-Id: <200704021529.l32FT9AX006397@smtp42.singnet.com.sg>

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
Received on Mon Apr 02 2007 - 10:28:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US