Re: Nicing Down User Oracle Processes -- Any problems?

M. D. Parker wrote:
snip
> I am thinking about
> nicing down user oracle processes if they exceed the usage of
> 30 CPU MINUTES. My DBA seems to indicate that that will cause
> problems with Oracle, and that oracle itself does not recommend
> such a procedure but I do not understand the problems here. I am a
> UNIX wizard but not an oracle wizard.

The main problem is that resolving locking conflicts relies on process co-operation. If you end up slowing a process down, it will hold its locks for longer. This causes other process to hang around for it, doing extra work as they resolve the waiting using more CPU and holding their locks for longer affecting other processes, etc., etc., etc.

Worse, when deadlock does occur, the offending process may have been niced down and won't get the CPU cycles to come in and participate in the tidying up increasing the probabiliy of deadlock themselves etc. It becomes a mess.

I have experience of DBA'ing a network database in a mainframe environment many years ago and we experimented reducing the CPU priority of the batch applications to give more power to the on-line stuff and we experienced all the problems above. The whole thing eventually ground to a halt timing out and crashing all over the place. So it is not just Oracle that has this trait.

The moral was keep database priorities equal and let the db sort itself out.

Regards,
Ronan.

-- 
_____________________________________________________________

Opinions expressed are my own and do not reflect my employers