Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: kill query w/o killing session?

Re: kill query w/o killing session?

From: DA Morgan <>
Date: Thu, 01 Sep 2005 08:27:40 -0700
Message-ID: <1125588421.518765@yasure>

Andreas Sheriff wrote:
> "DA Morgan" <> wrote in message
> news:1125461248.405986_at_yasure...

>> wrote:
>>>I've been using "alter system kill session 'sid,serial';" to kill
>>>sessions.  Now I am wondering is there a way to kill just the query of
>>>a session/sid without killing the session/sid itself?  Or is it simply
>>>not recommended?
>>Can not be done AFAIK.
>>Daniel A. Morgan
>>(replace x with u to respond)

> You're in luck!
> As you all know (or may have forgotten), I am in the process of reading all
> (yes ALL) of the Oracle documentation from cover to cover. Just an hour ago
> I completed chapter 27, Using the Database Resource Manager, of the Oracle
> 9i Administrator's guide. I don't know why it didn't occur to me before,
> because I read the same thing in the OCP 9i Sybex books, but check out this
> paragraph straight from the 9i Adm Guide:
> Changing Resource Consumer Groups
> (
> m#12724)
> ...
> Instead of killing a session of a user who is using excessive CPU, an
> administrator can instead change that user's consumer group to one that is
> allowed less CPU. Or, this switching can be enforced automatically, using
> automatic consumer group switching resource plan directives.
> Coupled with MAX_EST_EXEC_TIME, you can effectively switch to a group that
> allows 0 execution time, effectively killing the query.
> Now, I can't say for sure that switcing to a group with MAX_EST_EXEC_TIME
> will kill the query and I don't have time to test it right now, but I can
> show the definitions for this parameter and perhaps the OP can test?
> From the 9i Adm Guide (Oracle)
> MAX_EST_EXEC_TIME: Specifies the maximum execution time (in seconds)
> allowed for a session. Default is UNLIMITED.
> From Oracle 9i Performance Tuning Study Guide (Sybex, 525):
> ...
> Once set, Resource Manager will use the statistics in the CBO to generate an
> estimated completion time for each database operation. This estimate is
> then compared to this specified maximum allowed time. If the operation is
> going to take longer than the specified duration, then the operation will
> not be started.
> Note: It does not say anything about operations that have already started.

Ready to write Cliff Notes? ;-)


I suspect it will only affect a new transaction but that is a suspicion worthy of verification. Has anyone done it yet?

Daniel A. Morgan
(replace x with u to respond)
Received on Thu Sep 01 2005 - 10:27:40 CDT

Original text of this message