Note – The commands and processes listed have been tested and verified in a Oracle 11g release 2 (11.2.0.4) environment.

Sometimes, there exist database users with no regard for what type of queries they run or how long they run for.  These users typically kick off these ill-behaved queries and then go for coffee, lunch, or leave for the day.  Their query sits out there spinning and consuming resources that you know could be better used elsewhere.  This can be especially problematic in a production system if end-of-the-month aggregation processes or reports are being executed at the same time.

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 limit all users in the database.  While it is true that only one resource plan can be active at any time, users can be segregated into consumer groups under the plan and each consumer group can be tailored with the use of directives to make sure that users or applications do not suffer due to the actions of a few users.

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 ;).  Administering the resource manager requires the ADMINISTER_RESOURCE_MANAGER privilege, go figure.  A SYSDBA user has this privilege already, but if you need to assign the privilege to a non-SYSDBA user, you can use this command (as a properly privileged user):

exec dbms_resource_manager_privs.grant_system_privilege( -

grantee_name => '<USER_NAME>', -

admin_option => true);

Check for a currently active resource plan:

SQL> show parameter resource_manager_plan

NAME                     TYPE     VALUE

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

resource_manager_plan   string

Since no resource plan is active, we can continue with the rest of the penalty box steps to create one.  Later on, we will look at our options if there is an actual resource plan already in place.

Create a new pending area for plan, consumer group, and directives creation.  The pending area is where our resource plan work will be kept until it is validated and submitted.

begin

 dbms_resource_manager.create_pending_area();

end;

/

After the pending area is created, if your session abruptly ends or you make a mistake, simply run this next command to clear the pending area and start again.

begin

 dbms_resource_manager.clear_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.  Every resource plan must contain a directive to OTHER_GROUPS.  This consumer group contains all sessions that have not been assigned to a consumer group.  If you try to submit the resource plan without the OTHER_GROUPS option, 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;

/

The CANCEL_SQL group that will be switched to when the SWITCH_TIME_IN_CALL of 60 seconds is reached is actually an internal call introduced in 11g that will just cancel the SQL.  The KILL_SESSION group can also be used, but that would be a bit extreme in this case.

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 newly-created 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 put into the penalty box.

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 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:

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = PENALTY_BOX_PLAN;

System altered.

To clear the resource plan from the instance, use this command.  Note the command uses two single quotation marks, not a double-quote.  If you just want to switch to a different plan and not disable resource plans altogether, you can just supply the resource plan name within the quotation marks.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

Once our user’s behavior has been adjusted, we can remove them from the plan and put them back into their original 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:

NOTE – 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.  The elapsed time output was the result of executing ‘set timing on’ to enable this SQL*Plus option.

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 the following commands.  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;

/

 

In the event that there is an active resource plan already in place, there are two options to consider.  Either you can create a new consumer group and assign the offending user to the new group, or you can add a plan directive to the offending user’s assigned consumer group.

The DBA_RSRC_CONSUMER_GROUP_PRIVS view displays the consumer groups granted to users or roles. Specifically, it displays the groups to which a user or role is allowed to belong or be switched.  In the example shown below, user SCOTT always starts in the SALES consumer group (INITIAL_GROUP is YES) and can switch to the MARKETING group through a specific grant, and can also switch to the DEFAULT_CONSUMER_GROUP (OTHER_GROUPS) and LOW_GROUP groups because they are granted to PUBLIC.  SCOTT also can grant the SALES group but not the MARKETING group to other users (GRANT_OPTION is YES for SALES, NO for MARKETING).

SELECT * FROM dba_rsrc_consumer_group_privs;

GRANTEE            GRANTED_GROUP                  GRANT_OPTION INITIAL_GROUP
------------------ ------------------------------ ------------ -------------
PUBLIC             DEFAULT_CONSUMER_GROUP         YES          YES
PUBLIC             LOW_GROUP                      NO           NO
SCOTT              MARKETING                      NO           NO
SCOTT              SALES                          YES          YES
SYSTEM             SYS_GROUP                      NO           YES

Using this information, let’s look at our database after the PENALTY_BOX_PLAN has been created and enabled:

 

select *
from dba_rsrc_consumer_group_privs
order by grantee;

GRANTEE                        GRANTED_GROUP                  GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC                         DEFAULT_CONSUMER_GROUP         YES YES
PUBLIC                         LOW_GROUP                      NO  NO
SCOTT                          PENALTY_BOX_GROUP              NO  YES
SYSTEM                         SYS_GROUP                      NO  YES

Of the two options I mentioned earlier, it may be safer to create a new consumer group for the particular user with all the directives of their currently-assigned initial group.  The script in My Oracle Support (MOS) note 1388634.1 has the ability to reverse-engineer a complete resource plan including all included consumer groups.

With this information, you can create a new consumer group with the addition of the execution time limiting directive mentioned earlier and then assign it to the user as their initial consumer group.

The second option – adding the execution time limiting directive to their initial consumer group, is more risky because the directive would affect ALL the users assigned to that consumer group.

This post has been a small sampling of what the Oracle Resource Manager can do.  Over the years, I have seen nearly no shops where this feature was actively used.  Using this feature in non-production environment where resources are usually more limited can be beneficial.  I have also seen instances where using it in a production environment where some users are allowed to execute ad-hoc queries would have also removed some problems.

Enjoy!

 

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