Re: kill query w/o killing session?

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1125461248.405986_at_yasure...
> susana73_at_hotmail.com 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
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (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
(http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/dbrm.ht 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.

-- 

Andreas Sheriff
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer
----
"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding, if you don't eat your meat?"

DO NOT REPLY TO THIS EMAIL
Reply only to the group.