RE: "Throttling" a session's IO
- From: "Smith, Steven K - MSHA" <Smith.Steven@xxxxxxx>
- To: <hkchital@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Apr 2007 10:37:25 -0600
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Hemant K Chitale
Sent: Monday, April 02, 2007 9:29 AM
To: oracle-l@xxxxxxxxxxxxx
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 :
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
