Process priorities

Hi,

we have some trouble with long running stored procedures on our server, in that way that normal interactive communication between the programm and the database is quite impossible. These long running jobs have no high priority, so best of all would be to give these sessions a lower priority, so that interactive operations with less I/O consumption will be still fast enough.

Since we didn't find any trick to make Oracle running these sessions at a lower priority we fiddled around with some lines in V$SYSSTAT containing the current CPU consumption of the machine. Our idea was to hold the long running processes in a loop, delaying for e.g. 10 seconds (by calling DBMS_PIPE.RECEIVE_MESSAGE or something like that) and then analyzing the load value in V$SYSSTAT. Is the load still too high then continue cycle else start your work. We never found a usable value in V$SYSSTAT suitable for this job.

Another problem are user starting these long running jobs, who terminates their clients by opening the task manager and shutting down the client. The PL/SQL routines on the server continues running of course. Is there since Oracle 8i any chance to abort these routines on another way than shutting down Oracle with shutdown abort?

Any hints, ideas?
Thanks in advance
  Ralf Jonas Received on Wed Sep 13 2000 - 03:14:51 CDT