Oracle: Limiting Query Runtime Without Killing the Session

Recently, a customer request came in to limit the runtime of a query in a 10g database for a certain user. But since connection pooling was being used, the session itself should NOT be killed.

Now, why would somebody ask for that?

I think a reason to implement such a restriction is, for example, if a website application requests data from the database and the query takes too long, then the user sitting in front of the web browser cancels the request, and so it would be beneficial to also cancel the query and not have it still running in the background. There might be quite a few other reasons why such restrictions should be implemented.

Anyway, so what would be the best solution for this? The Resource Manager, a feature not too often used in Oracle.

Actually, “overlooked” might be the better word—overlooked when investigating performance problems. I imagine it must be a new DBA’s nightmare to be confronted with end user complaints of “bad performance”, only to find after a long search that the Resource Manager has restricted the CPU for a specific user.

But back to the topic. Resource Manager not only allows one to define how much CPU a certain user or group gets, it also lets one switch into lower priority groups and kill a query while leaving the session running. It is like running a SELECT statement in sqlplus, and pressing ctrl-c. The session is still alive, yet the query is canceled.

Sure, the Resource Manager is documented by Oracle, but when I started to test this feature, I came across a few very interesting things that are not as well documented, and that’s what prompted me to write this post:

1. You HAVE to include the OTHER_GROUPS group, even if you don’t really need it.
2. Once you have created a consumer group, you have to delete it in the correct order before you can recreate it.
3. Deleting a consumer group can be quite challenging—you will have to correctly use the pending area.

So, let’s get started.

I am going to create a special resource group GROUP_WITH_LIMITED_EXEC_TIME for the PYTHIAN user, which maps to the oracle user on the OS. And then I’m going to define a resource plan LIMIT_EXEC_TIME for that specific user, to limit the query time.

Finally, I’m going to use CREATE_PLAN_DIRECTIVE with SWITCH_GROUP=CANCEL_SQL.

Here’s my first attempt:

set serverout on size 5555
begin
  -- we have to create a pending area first
  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 => '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.VALIDATE_PENDING_AREA;

end;
/

ERROR at line 1:
ORA-29382: validation of pending area failed
ORA-29377: consumer group OTHER_GROUPS is not part of top-plan LIMIT_EXEC_TIME
ORA-06512: at "SYS.DBMS_RMIN", line 402
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 437
ORA-06512: at line 34

SQL>

Okay, so let’s add that OTHER_GROUPS at the end . . .

...
  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;
  -- I also had forgotten to submit the pending area earlier:
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

end;
/

PL/SQL procedure successfully completed.

SQL> alter system set RESOURCE_MANAGER_PLAN='LIMIT_EXEC_TIME';

System altered.

SQL>

What’s that? All the users are mapped to OTHER_GROUPS. I was expecting to see PYTHIAN in the special group GROUP_WITH_LIMITED_EXEC_TIME. And when I try to delete the group, I get:

SQL> exec dbms_resource_manager.DELETE_PLAN_CASCADE ('LIMIT_EXEC_TIME');

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME');
BEGIN dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME'); END;

*
ERROR at line 1:
ORA-29368: consumer group GROUP_WITH_LIMITED_EXEC_TIME does not exist
ORA-06512: at "SYS.DBMS_RMIN", line 134
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 142
ORA-06512: at line 1

But it DOES exist:

SQL> select * from DBA_RSRC_CONSUMER_GROUPS where consumer_group like '%LIMITED%';

CONSUMER_GROUP                 CPU_METHOD
------------------------------ ------------------------------
COMMENTS
----------------------------------------------------------------------------------------------------------------------------------
STATUS                         MAN
------------------------------ ---
GROUP_WITH_LIMITED_EXEC_TIME   ROUND-ROBIN
This is the consumer group that has limited execution time per statement
PENDING                        NO

1 row selected.

SQL>

Oh, it’s pending, even though I did clear the pending area. The trick is that the pending area is process-specific, so if I created a pending area earlier directly on sqlplus command line, and another one later inside begin/end, I effectively have two pending areas, and one of them has not been cleared.

Moving on. Another issue was how to properly remove a group:

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='';

System altered.

SQL> exec dbms_resource_manager.DELETE_PLAN_CASCADE ('LIMIT_EXEC_TIME');
exec dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME');
BEGIN dbms_resource_manager.DELETE_PLAN_CASCADE ('LIMIT_EXEC_TIME'); END;

*
ERROR at line 1:
ORA-29388: plan/consumer_group GROUP_WITH_LIMITED_EXEC_TIME is part of more than one top-plan
ORA-06512: at "SYS.DBMS_RMIN", line 105
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 90
ORA-06512: at line 1

SQL> BEGIN dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME'); END;

*
ERROR at line 1:
ORA-29381: plan/consumer_group GROUP_WITH_LIMITED_EXEC_TIME referred to by another plan and cannot be deleted
ORA-06512: at "SYS.DBMS_RMIN", line 134
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 142
ORA-06512: at line 1

SQL> exec dbms_resource_manager.DELETE_PLAN('LIMIT_EXEC_TIME');

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME');

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

PL/SQL procedure successfully completed.

Back to the problem from earlier, the problem that the PYTHIAN user is NOT in the special group. If I attempt to set it there, I get:

SQL> exec dbms_resource_manager.set_initial_consumer_group('PYTHIAN','GROUP_WITH_LIMITED_EXEC_TIME');
BEGIN dbms_resource_manager.set_initial_consumer_group('PYTHIAN','GROUP_WITH_LIMITED_EXEC_TIME'); END;

*
ERROR at line 1:
ORA-29399: user PYTHIAN does not have privilege to switch to consumer group
GROUP_WITH_LIMITED_EXEC_TIME
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 489
ORA-06512: at line 1

Okay, so let’s grant that permission:

SQL>  exec dbms_resource_manager_privs.grant_switch_consumer_group('PYTHIAN','GROUP_WITH_LIMITED_EXEC_TIME',false);

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.set_initial_consumer_group('PYTHIAN','GROUP_WITH_LIMITED_EXEC_TIME');

PL/SQL procedure successfully completed.

SQL> select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;

USERNAME                       RESOURCE_CONSUMER_GROUP            COUNT(*)
------------------------------ -------------------------------- ----------
                                                                        16
DEMO_CCFS                      OTHER_GROUPS                              6
SYS                            OTHER_GROUPS                              1
UAT_CCFS                       OTHER_GROUPS                             59
PYTHIAN                        GROUP_WITH_LIMITED_EXEC_TIME              1
UAT_AMS                        OTHER_GROUPS                             31

6 rows selected.

SQL>

Ah, now it works! Finally!

To test it, I created a SELECT that would take long to execute, and it was cancelling after 15 seconds:

SQL> select count(*) from dba_segments, dba_extents;
select count(*) from dba_segments, dba_extents
                     *
ERROR at line 1:
ORA-00040: active time limit exceeded - call aborted

Nothing in the alert log. That’s good, I don’t want anything there in this case.

To sum it all up, here’s the code I used in the end to get things working:

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;
/

exec dbms_resource_manager_privs.grant_switch_consumer_group('PYTHIAN','GROUP_WITH_LIMITED_EXEC_TIME',false);

exec dbms_resource_manager.set_initial_consumer_group('PYTHIAN','GROUP_WITH_LIMITED_EXEC_TIME');

select * from DBA_RSRC_CONSUMER_GROUPS;
select * from DBA_RSRC_GROUP_MAPPINGS;
select * from DBA_RSRC_PLANS;
select * from DBA_RSRC_PLAN_DIRECTIVES;

-- to enable it:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='LIMIT_EXEC_TIME';

SELECT se.sid sess_id, co.name consumer_group,
 se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
 FROM v$rsrc_session_info se, v$rsrc_consumer_group co
 WHERE se.current_consumer_group_id = co.id;

select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;

Be sure you test this solution thoroughly, and make sure the user you are limiting is not running any Materialized View refreshes. Check the alert log frequently during testing and after implementation.

I hope this post helps you if you need to work with Resource Manager, especially if it is for the first time.