Re: Is there a way/hack to allow the query only to run for specified duration in Oracle and kill it.

From: Andy Wattenhofer <watt0012_at_umn.edu>
Date: Wed, 1 May 2019 11:33:45 -0500
Message-ID: <CAFU3ey4DSU2pN4R2cfwicu2WSP+vA3c=R6gbdDShsWTgcp4SDA_at_mail.gmail.com>



I happened to be working on a very similar thing with DB resource manager when I saw this message. DBRM won't kill a session unless it is idle or blocking (via max_idle_time and max_idle_blocker_time directives) but you might be able to trick it by switching the session to a directive with 0% CPU and active_sess_pool_p1 set to 0. The DBRM documentation is not clear whether limiting active sessions will cause sessions to be killed.

The 12.2 documentation has an example of a DBRM plan that will switch a long running session to a low priority plan (see example 2 at https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/managing-resources-with-oracle-database-resource-manager.html#GUID-D695EE69-E08B-41BB-90FB-207E6810B938 ).

So Kunwar, using that example from the doc you would want to set the switch_time for the start_group plan directive to 60. Then set the quarantine_group directive to utilization_limit=>0 and active_sess_pool_p1=>0. Then set assign the start_group to your user that is running the SQL:

begin
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(

    ATTRIBUTE      => DBMS_RESOURCE_MANAGER.ORACLE_USER,
    VALUE          => 'SOMEUSER',
    CONSUMER_GROUP => 'START_GROUP');

end;

On Wed, May 1, 2019 at 10:53 AM Jeff Smith <jeff.d.smith_at_oracle.com> wrote:

> Sounds like you want a Resource Manager plan with a directive around
> Execution Time Limit…then make sure your script goes through with users
> attached to that resource plan
>
>
>
> *From:* kunwar singh <krishsingh.111_at_gmail.com>
> *Sent:* Wednesday, May 1, 2019 11:46 AM
> *To:* ORACLE-L <oracle-l_at_freelists.org>
> *Subject:* Is there a way/hack to allow the query only to run for
> specified duration in Oracle and kill it.
>
>
>
> Hi Listers,
>
> Small question.
>
>
>
> I currently working on a remote database in which i can only pass a query
> through a tool and i want to kill it after letting it run only for 1
> minute
>
> The reason for this is i have to try different underscore parameters at
> session to check if the issue is fixed or not.
>
>
>
> I am looking for any solution which is completely in a single sql . What i
> pass to the tool is a .sql file , thats all.
>
> If you have a simple solution in shell then i will try that one as well.
> Meaning i can run a shell command like this:
>
> !hostname
>
>
>
>
>
> The query that i want to run is a big select sql , which unfortunately i
> cannot paste here.
>
>
>
> --
>
> Cheers,
> Kunwar
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed May 01 2019 - 18:33:45 CEST

Original text of this message