Andreas Sheriff wrote:
> "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.
Ready to write Cliff Notes? ;-)
Thanks.
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
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Thu Sep 01 2005 - 10:27:40 CDT
