Re: "Throttling" a session's IO


Thanks for the suggestions.  However,

1.  I am running 9i which , by default, does a TRUNCATE instead of a DELETE.
In fact, the users cannot afford the TRUNCATE (because there would be 0 rows in the MView when it is being refreshed). So, I do a grouped refresh (which used DELETE) to ensure 
that there ARE always rows in the MV.
2. I have seen Gints Plivina's method. That is appropriate in Query rewite situations. However, we don't use Query Rewrite. When users (the application) specifically needs the data that is present in the MView, the query explicitly names the MView -- something 
like using the Mview as it was originally designed -- a Snapshot.
Moreover, the method described there still creates the same (actually a higher load because of the drop and create index -- which I don't need to do because I am not relying 
on statistics and QR).
3. The method of creating two different MViews described by Steven would probably work, provided that I use TRUNCATE (ie a non-grouped Refresh). That is one of the methods I had in mind when I stated "split up into separate MViews " (alternately redesign the query and application to fetch different subsets of data into different Mviews)
The reason why I raised "Throttling" a session's IO is not because of the Mview. 
The Mview problem is a real example that I have where one session (or a small
group of sessions) can tip a system over the threshold {actually "sar" shows very 
low CPU and 25% wio  but the issue is that the application is so very sensitive
to query response time -- it is not a human being at the other end but a piece
of equipment that knows only rigid thresholds !).

We have actually rewritten the MView and also reduced the scope of the data
it fetches so that it runs in 4min to 5.5min instead of the earlier 20min to 25min. 
It might still come back as a pain-point 6 months from now.

But , this specific MView is an example of a problem  for which I am looking
for a solution. In a CPU-starved system you can still make use of "nice" and "renice" 
{NOT on the Oracle Background processes}  on QUERY sessions {probably
not for Transactions which you'd have to be more careful with}
to limit CPU usage or provide more CPU to a specific session..

How do you control a session's IO rate ?

(like I said if it was a custom PLSQL, I could re-write it to "sleep"
between every batch of so many records).





Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
http://hemantoracledba.blogspot.com

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

Other related posts: