Get in the Penalty Box!

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…

 

ORA-16957: SQL Analyze time limit interrupt

time_expired

First thing this morning, I was greeted with an email from my alert log monitor containing the error in the title.  This is one of the less cryptic error messages from an Oracle database, but what does it mean?

If you are an Oracle DBA and have been dealing with performance issues for any length of time, you are familiar with the SQL Tuning Advisor.  This tool is part of the Oracle Tuning Pack .  It takes one or more SQL statements as input, analyzes them, and displays recommendations to tune the statements with rationale and expected benefits.  These recommendations can include collecting object statistics, creating indexes, creating SQL profiles or SQL plan baselines, and even recommendations to rewrite.

The SQL Tuning Advisor has two modes – automatic and manual.  The automatic mode is referred to as the Automatic SQL Tuning Task or SQL Analyze for short.  This task when enabled runs nightly as part of the default maintenance plan.  Usually it runs happily in the background and goes mostly unnoticed.  Other times it generates an ORA-16957 error. This error means that it has not completed within its limit settings. You can view these settings like this:

COLUMN parameter_value FORMAT A30  

SELECT parameter_name, parameter_value  
FROM dba_advisor_parameters  
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'  
AND parameter_name IN ('TIME_LIMIT','DEFAULT_EXECUTION_TYPE',
'SQL_LIMIT','LOCAL_TIME_LIMIT');

PARAMETER_NAME                 PARAMETER_VALUE  

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

LOCAL_TIME_LIMIT               1200  
TIME_LIMIT                     7200  
DEFAULT_EXECUTION_TYPE         TUNE SQL
SQL_LIMIT                      -1

The time_limit value is the total time in seconds that the SQL Analyze job is allowed per execution within the maintenance window.  The local_time_limit is the value in seconds that the SQL Analyze job is allowed to spend analyzing each individual SQL statement.  The sql_limit parameter is the number of SQL statements to examine per execution of the job.  The -1 number indicates no limit to the number of SQL statements the job can review within its time limit.

How does it determine which statements need to be analyzed?  From the Oracle documentation, the following quote is taken:

The automated SQL tuning task does not process the following types of SQL:

  • Ad hoc SQL statements or SQL statements that do not repeat within a week
  • Parallel queries
  • Queries that take too long to run after being SQL profiled, so that it is not practical for SQL Tuning Advisor to test execution
  • Recursive SQL

 You can run SQL Tuning Advisor on demand to tune the preceding types of SQL statements.

 

Can we find out what SQL statement caused the ORA-16957 error?

Get the execution_name for the long run day:

set lines 200 pages 100 
col error_message for a60 
col execution_name for a15 

select execution_name, advisor_name,to_char(execution_start,'dd-mon-yy hh:mi:ss'), to_char(execution_end,'dd-mon-yy hh:mi:ss'), status,error_message 
from dba_advisor_executions 
where task_name = 'SYS_AUTO_SQL_TUNING_TASK' 
order by execution_start desc;

Take the name of the execution you are interested in and place it into the query below to identify the ID of the SQL causing the overrun.  If a sufficient amount of time has passed, you may not get any data from this query.

SELECT sql_id, sql_text FROM dba_hist_sqltext 
WHERE sql_id IN (SELECT attr1 FROM dba_advisor_objects 
WHERE execution_name = '&execution_name' 
AND task_name = 'SYS_AUTO_SQL_TUNING_TASK' 
AND type = 'SQL' AND bitand(attr7,64) <> 0 );


 Then you can take the SQL_ID from the query above and run it through the manual mode of the SQL Tuning Advisor:

Create the tuning task:

DECLARE 
l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
sql_id => '&SQL_ID', 
scope => DBMS_SQLTUNE.scope_comprehensive, 
time_limit => 99999,   --I use a large time out value 
task_name => 'my_tuning_task', 
description => 'Tuning task for statement blahblah.'); 
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/

 

Execute the tuning task:

BEGIN 
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_tuning_task'); 
end; 
/

When the task is complete, get the report:

SET LONG 5000 
SET LONGCHUNKSIZE 5000 
SET LINESIZE 200 
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_tuning_task') from DUAL;

Be sure to drop the tuning task before executing another.  NOTE – If the tuning task results recommend using a SQL Profile, then you MUST accept the profile before dropping the tuning task.  Otherwise, the recommended profile will be lost and you will need to run the tuning task again.

BEGIN
  dbms_sqltune.drop_tuning_task (task_name => 'my_tuning_task');
END;
/

 

In the event that the ORA-16957 error is seen on a regular basis, you may need to adjust the SQL Analyze task limits or limit the number of SQL statements analyzed. Use the DBMS_SQL_AUTOTUNE.SET_TUNING_TASK_PARAMETER procedure to change these values.  Here is an example of changing the time limit to 7200 seconds (2 hours):

BEGIN 
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => 'SYS_AUTO_SQL_TUNING_TASK', 
parameter => 'TIME_LIMIT', value => 7200); 
END; 
/

 

If you need to determine if the SQL Analyze job is running against your database, use this query:

SELECT CLIENT_NAME, STATUS
 FROM   DBA_AUTOTASK_CLIENT
 WHERE  CLIENT_NAME = 'sql tuning advisor';
  
 CLIENT_NAME          STATUS
 -------------------- --------
 sql tuning advisor   ENABLED

 

You can disable or enable the SQL Analyze job with these commands:

BEGIN
   DBMS_AUTO_TASK_ADMIN.ENABLE (
     client_name => 'sql tuning advisor'
 ,   operation   => NULL
 ,   window_name => NULL
 );
 END;
 /


 BEGIN
   DBMS_AUTO_TASK_ADMIN.DISABLE (
     client_name => 'sql tuning advisor'
 ,   operation   => NULL
 ,   window_name => NULL
 );
 END;
 /

If you are interested in viewing the latest SQL Analyze report:

VARIABLE l_report CLOB;

BEGIN
:l_report := DBMS_SQLTUNE.report_auto_tuning_task(
begin_exec => NULL,
end_exec => NULL,
type => DBMS_SQLTUNE.type_text,
level => DBMS_SQLTUNE.level_typical,
section => DBMS_SQLTUNE.section_all,
object_id => NULL,
result_limit => NULL
);
END;
/

SET LONG 1000000
PRINT :l_report

If this task is enabled in your database, then take advantage of its analysis.  Regular viewing of its output may help identify ‘usual suspects’ that continue to run poorly.