Don't Kill That Process - Suspend It!

Have you ever had to kill a long-running batch process because it had overrun its window and was affecting online performance? Well, next time, think again, because there may be a better solution. You may be able to suspend such a process instead of killing it.

If you kill the process it may take longer to roll back than it would have needed to run to completion, and all of the resources used will have been wasted. However, there is an undocumented command that can be used to suspend an Oracle process indefinitely. If you suspend the process, it will consume no more resources until you resume it.

The APT scripts suspend_process.sql and resume_process.sql can be used to suspend and resume Oracle processes. However, don't try to use them under NT, before release 8.1.6, because they suspend the current session instead!

There are, however, two circumstances in which suspending and later resuming a process is not suitable. These are:

  1. If other processes need locks that the long-running process is holding in an incompatible mode.
  2. If the long-running process is dependent for read consistency on rollback segment information that would be overwritten while it is suspended.

Copyright Ixora Pty Ltd Send Email Home