Sometimes, there exist database users with no regard for what type of queries they run or how long they run for.  These users typically kicks off a query and then goes for coffee, lunch, or leaves for the day.  Their query sits out there spinning and consuming resources that you know could be better used elsewhere, especially if end of the month aggregation processes or reports are being executed.

Maybe you have talked to them before about this behavior or even helped them tune a query or two, but they don’t seem to get the message.  In extreme cases, these users even terminate sessions when the query does not come back in a timely manner only to reconnect and retry it in a new session, leaving the old one (possibly) still executing.

It’s time to let these users know that their behavior is not acceptable in an environment where resources are finite and they need to share with other higher priority processes.  They need to spend some time in the penalty box.  For those unfamiliar with this term, it is a term used in ice hockey where misbehaving players have to sit out the game in a segregated area for a certain length of time, depending on the seriousness of their misdeeds.  The team with the misbehaving player is not allowed to send in a substitute player during the penalty time, so it leaves them one player short and vulnerable to be scored against.

The Oracle Resource Manager is the keeper of the database penalty box.  It has the ability to enforce limits on resources such CPU and I/O consumption, as well as execution time limits.  We are going to use this last feature to create our own penalty box.  I realize that most database managers don’t want to put constraints on users, especially developers, but in all honesty, if they are writing queries that take an hour or more to execute on a regular basis, then the managers need to put their foot down and the users or developers may need a remedial SQL tuning course.

A common misconception about resource plans is that they will apply to all users in the database.  This is not true and I will demonstrate how we can apply a resource plan to only the particular users that we determine are misbehaving.

Here is a simple process to create a resource plan that will limit execution time to 60 seconds.  This limit is just for testing purposes as even I have been known to write queries that run for over a minute 😉

Check for a currently active resource plan:

SQL> show parameter resource_manager_plan

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

resource_manager_plan   string

There is no currently active plan.  In the event that there is an active resource plan already in place, we can ignore the step to create a resource plan below and continue with the remaining steps.

First, clear the existing pending area.  The pending area is where our resource plan work will be saved until it is validated and submitted.  If you get stuck or make a mistake, simply run this command to clear the pending area and start again

begin
 dbms_resource_manager.clear_pending_area();
end;
/

Create a new pending area for plan, consumer group, and directives creation:

begin
 dbms_resource_manager.create_pending_area();
end;
/

Create the penalty box resource plan:

begin
 dbms_resource_manager.create_plan(
  plan => 'PENALTY_BOX_PLAN',
  comment => 'Resource plan to limit execution time');
end;
/

Create the penalty box resource consumer group:

begin
 dbms_resource_manager.create_consumer_group(
  consumer_group => 'PENALTY_BOX_GROUP',
  comment => 'Resource consumer group to limit execution time');
end;
/ 

Create the penalty box resource plan directives.  The OTHER_GROUPS directive essentially does nothing, but is necessary.  If you try to submit the resource plan without the OTHER_GROUPS directive, you will get this error message when attempting to validate the pending area:

begin
*
ERROR at line 1:
ORA-29377: consumer group OTHER_GROUPS is not part of top-plan PENALTY_BOX_PLAN
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3640
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3691
ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3703
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 746
ORA-06512: at line 2

As mentioned previously, we are setting an execution limit of 60 seconds.

begin
  dbms_resource_manager.create_plan_directive(
    PLAN=>'PENALTY_BOX_PLAN',
    GROUP_OR_SUBPLAN=>'PENALTY_BOX_GROUP',
    COMMENT=>'Kill statement after exceeding total execution time',
    SWITCH_GROUP=>'CANCEL_SQL',
    SWITCH_FOR_CALL=>TRUE,
    SWITCH_TIME_IN_CALL=>60,
    SWITCH_ESTIMATE=>false);
  dbms_resource_manager.create_plan_directive(
    PLAN=> 'PENALTY_BOX_PLAN',
    GROUP_OR_SUBPLAN=>'OTHER_GROUPS',
    COMMENT=>'leave others alone',
    CPU_P1=>100);
end;
/ 

 

Validate the pending area for the new plan, consumer group, and directives.  If there is a problem with the plan, it will appear during validation

begin
 dbms_resource_manager.validate_pending_area();
end;
/ 

Submit the pending area for plan, consumer group, and directives

begin
 dbms_resource_manager.submit_pending_area();
end;
/ 

Now we have to grant privileges in order to assign consumer groups to users.

Create a new pending area for the users we want to put in our new penalty box:

begin
 dbms_resource_manager.create_pending_area();
end;
/ 

Grant the switch privilege for resource consumer groups to users or roles.  We need to execute this command for every user we want to be eligible to switch resource groups.

begin
 dbms_resource_manager_privs.grant_switch_consumer_group(
   grantee_name => 'SCOTT',
   consumer_group => 'PENALTY_BOX_GROUP',
   grant_option => FALSE);
end;
/ 

Assign the desired users users to the new resource consumer group

begin
 dbms_resource_manager.set_initial_consumer_group(
  user => 'SCOTT',
  consumer_group => 'PENALTY_BOX_GROUP');
end;
/ 

Validate the pending area before submitting it:

begin
 dbms_resource_manager.validate_pending_area();
end;
/ 

Submit the pending area:

begin
 dbms_resource_manager.submit_pending_area();
end;
/ 

Finally, specify the new plan to be used by the instance:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = PENALTY_BOX_PLAN; 

To clear the resource plan from the instance, use this command.  Note the command uses two single quotation marks, not a double-quote:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

 

Once our user’s behavior has been adjusted, we can remove them from the plan:

begin
 dbms_resource_manager.set_initial_consumer_group(
  user => 'SCOTT',
  consumer_group => 'DEFAULT_CONSUMER_GROUP');
end;
/ 

To place other users in the penalty box, follow these abbreviated steps:

begin
 dbms_resource_manager_privs.grant_switch_consumer_group(
   grantee_name => 'USER',
   consumer_group => 'PENALTY_BOX_GROUP',
   grant_option => FALSE);
end;
/ 


begin
 dbms_resource_manager.set_initial_consumer_group(
  user => 'USER',
  consumer_group => 'PENALTY_BOX_GROUP');
end;
/ 


Now to test out our penalty box.  This query was executed as user SCOTT:

This is my “hammer” query which will execute until it runs out of TEMP space:

set timing on
select distinct owner
from dba_constraints
where owner = (select owner
 from dba_constraints
 where constraint_type = 'C'
 and owner in(select owner
 from dba_constraints
 where constraint_type = 'P'
 and owner in(select owner
 from dba_constraints
 where constraint_type = 'R'
 and owner in(select owner
 from dba_constraints
 where constraint_type = 'U'))))
/

 

If the penalty box is working properly, you should see this error if the query exceeds the time limit set in the resource plan directive:

ERROR at line 14:
ORA-00040: active time limit exceeded - call aborted


Elapsed: 00:01:31.20

Notice that the query did not end EXACTLY when the sixty-second time limit had been reached.  The time limit is an approximation.  The query will look to end after the time limit at the next convenient interrupt as determined by the database, much like times when you kill a database session and instead of ending immediately, it gets ‘marked for kill’ instead.  The database is waiting for the next convenient interrupt.  As a side note, you can add the word ‘immediate’ to the end of your ‘alter system kill session’ to speed things along.

If you want to make changes to the resource plan, consumer group, or resource plan directives, you can use these commands to remove the existing ones.  Please note that you will need to create a pending area, execute the desired command, then submit the pending area in order for the command to take affect.

To remove a consumer group:

begin
DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP('PENALTY_BOX_GROUP'); 
end;
/

 

To remove a resource plan:

 

begin
DBMS_RESOURCE_MANAGER.DELETE_PLAN('PENALTY_BOX_PLAN');
end;
/

 

To remove a resource plan and all associated sub-plans and consumer groups:

 

begin
DBMS_RESOURCE_MANAGER.DELETE_PLAN_CASCADE('PENALTY_BOX_PLAN'); 
end;
/

 

To delete plan directives:

 

begin
DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (
 plan=>'PENALTY_BOX_PLAN', 
 group_or_subplan=>'PENALTY_BOX_GROUP');
end;
/

 

Enjoy and let the misbehaved beware!

Update – 4/3/17

Oracle Support published note 2250077.1 today on how to cancel SQL using the resource manager.  Nobody over there likely reads my blog, but it is quite a coincidence.  Just saying…

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s