Re: "Throttling" a session's IO
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: jkstill@xxxxxxxxx, Smith.Steven@xxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 03 Apr 2007 21:40:52 +0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
