Re: Different 'priorities' when doing selects

"Joe Smith" <nospam_at_nospam.com> wrote in message news:<bodh2v$ekk$1_at_news-reader3.wanadoo.fr>...
> Hi,
>
> I'm using Oracle 9i. My program is doing "heavy" calculations on some
> tables A,B, C, which require a lot of I/O, and afterwards, inserts a value
> in a table D.
> It's vital to have a decent response time when accessing this table, and
> now, when I'm in the middle of one of these calculations, I may wait for
> more than one minute to show this D table which has no more than one hundred
> elements.
>
> Is there any way to give a higher priority to the selections in the table D?
>
> Thx!

If you are on a unix system, running directly logged on to the unix system, you may be able to find your process id from joining the v$session and v$process tables, then up your priority with the sql host nice command. But if I/O contention for the tables is the issue, make an E table on a different disk as select from D before you start updating D, and let people see that instead. Or perhaps make E a materialized view that is refreshed when ABC and D are not being messed with.

You need to establish what is really causing the slowdown to fix it. See if v$session_wait or v$locked_object tell you anything while D is thinking. I've seen some systems where one would expect them to be I/O bound, but they turn out to be cpu bound, and the answer is to throw cpu at it. After being sure the SQL is tuned, which is the usual problem, of course. There are plenty of other less common problems if your system hasn't been properly administered.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/wed/business/news_1b5dejavu.html