5 Replies Latest reply: Dec 27, 2013 9:56 AM by Shivendra Narain Nirala RSS

    Limiting a oracle query

    Sitanshu.

      Hi,

      i have a query that is taking a long time to execute is there any way i can set a time out for the query so that the query & not the session is killed on oracle 10g  database.

       

      Thanks in advance

        • 2. Re: Limiting a oracle query
          Sitanshu.

          set serverout on size 5555

          --

          -- first remove an existing active plan

          ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='';

          --

          -- delete any existing plan or group

          -- we have to create a pending area first

          exec dbms_resource_manager.clear_pending_area();

          exec dbms_resource_manager.create_pending_area();

          exec dbms_resource_manager.DELETE_PLAN ('LIMIT_EXEC_TIME');

          exec dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME');

          exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

           

          exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

          begin

            dbms_resource_manager.create_pending_area();

            --

            -- we need a consumer group that maps to the desired oracle user:

            dbms_resource_manager.create_consumer_group(

              CONSUMER_GROUP=>'GROUP_WITH_LIMITED_EXEC_TIME',

              COMMENT=>'This is the consumer group that has limited execution time per statement'

              );

            dbms_resource_manager.set_consumer_group_mapping(

              attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,

              value => 'PYTHIAN',

              consumer_group =>'GROUP_WITH_LIMITED_EXEC_TIME'

            );

           

            -- and we need a resource plan:

            dbms_resource_manager.create_plan(

              PLAN=> 'LIMIT_EXEC_TIME',

              COMMENT=>'Kill statement after exceeding total execution time'

            );

           

            -- now let's create a plan directive for that special user group

            -- the plan will cancel the current SQL if it runs for more than 120 sec

            dbms_resource_manager.create_plan_directive(

              PLAN=> 'LIMIT_EXEC_TIME',

              GROUP_OR_SUBPLAN=>'GROUP_WITH_LIMITED_EXEC_TIME',

              COMMENT=>'Kill statement after exceeding total execution time',

              SWITCH_GROUP=>'CANCEL_SQL',

              SWITCH_TIME=>15,

              SWITCH_ESTIMATE=>false

            );

           

            dbms_resource_manager.create_plan_directive(

              PLAN=> 'LIMIT_EXEC_TIME',

              GROUP_OR_SUBPLAN=>'OTHER_GROUPS',

              COMMENT=>'leave others alone',

              CPU_P1=>100

            );

           

            DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

           

            DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

           

          end;

          /

           

          what value do i set to cancel the query if it exceeds 10 minutes

          • 3. Re: Limiting a oracle query
            rp0428
            i have a query that is taking a long time to execute is there any way i can set a time out for the query so that the query & not the session is killed on oracle 10g  database.

             

            Yes - if a single operation exceeds a resource limit that you set.

             

            No - if the resource limit is CONNECT_TIME - Oracle will kill the session if you use a profile to limit the connect time.

             

            See CREATE PROFILE in the doc

            http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6010.htm

            CREATE PROFILE app_user LIMIT

              SESSIONS_PER_USER UNLIMITED

              CPU_PER_SESSION UNLIMITED

              CPU_PER_CALL 3000

              CONNECT_TIME 45

              LOGICAL_READS_PER_SESSION DEFAULT

              LOGICAL_READS_PER_CALL 1000

              PRIVATE_SGA 15K

              COMPOSITE_LIMIT 5000000;


            Oracle Database enforces resource limits in the following ways:

            •   If a user exceeds the CONNECT_TIME or IDLE_TIME session resource limit, then the database rolls back the current transaction and ends the session. When the user process next issues a call, the database returns an error.
            •   If a user attempts to perform an operation that exceeds the limit for other session resources, then the database aborts the operation, rolls back the current statement, and immediately returns an error. The user can then commit or roll back the current transaction, and must then end the session.
            •   If a user attempts to perform an operation that exceeds the limit for a single call, then the database aborts the operation, rolls back the current statement, and returns an error, leaving the current transaction intact.

            • 4. Re: Limiting a oracle query
              Shivendra Narain Nirala

              Hi Sitanshu

               

              In addition , there should be change the active windows to use the null resource manager plan because it may cause to lead other problems which run in long running time.

               

              Regards

               

              Shivendra Narain Nirala

              • 5. Re: Limiting a oracle query
                Suntrupth

                Hi,

                 

                I believe you have referenced http://www.pythian.com/blog/oracle-limiting-query-runtime-without-killing-the-session/

                 

                 

                 

                Excerpt from ORACLE_BASE

                 

                 

                 

                CANCEL_SQL and KILL_SESSION

                 

                 

                In addition to automatically switching consumer groups, the CREATE_PLAN_DIRECTIVE procedure can be used to automatically cancel an SQL statement or kill the session if it exceeds its SWITCH_TIME limit. To achieve this set the SWITCH_TIME should be set as normal, but the SWITCH_GROUP should be set to CANCEL_SQL or KILL_SESSION instead of a consumer group name.

                 

                Regards,

                Suntrupth