Get in the Penalty Box! (Redux version)

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

The return of the Recyclebin

Last Friday, I received an email concerning a problem a DBA was having migrating objects from one tablespace to another.  They thought they had migrated all the objects, but they found some indexes when querying the DBA_SEGMENTS view.  These indexes were not visible in the DBA_INDEXES view, which was my first clue to the problem.  The next clue was when I found out all the indexes had a prefix of “BIN$”.  The DBA did not realize that these were objects that had been dropped previously and were sitting in the recyclebin.

The reyclebin  is a little-mentioned feature of the Oracle database that I blogged about back in 2011 when it saved me from an embarrassing and tricky recovery.  In honor of this unsung feature, I found the old blog, dusted it off, and updated it where needed.  Here we go…

What is recyclebin?

The Oracle recyclebin has been around since version 10g release 1 as part of a feature called Flashback Drop and in my opinion, continues to be a little-known and little-understood feature. It has saved me from potential data loss on at least one occasion, though, so I think it deserves some consideration.

The recyclebin is actually a data dictionary table containing information about dropped objects. Each named user account has their own recyclebin. Unless a user has the SYSDBA privilege, the only objects that the user has access to in the recyclebin are those that the user owns. A user can view his objects in the recyclebin using the following statement:

SELECT * FROM RECYCLEBIN;

When the recyclebin is enabled, dropped tables and any associated objects such as indexes, constraints, and nested tables are removed from the DBA_<objects> views that track database objects.  During this process, the table is renamed.  The new name has a telltale “BIN$” prefix placed on it.  These objects still occupy space in the tablespace where they originated. They continue to count against user space quotas, until purged from the recyclebin either manually or by the database because of tablespace space constraints.

When you drop an entire tablespace including its contents, the objects in the tablespace are NOT placed in the recycle bin and the database purges any entries in the recycle bin for objects located in the dropped tablespace. The database also purges any recycle bin entries for objects in a tablespace you drop. Likewise:

  • When you drop a user, any objects belonging to the user are NOT placed in the recyclebin and any objects in the recyclebin owned by that user are purged.
  • When you drop a cluster, its member tables are NOT placed in the recyclebin and any former member tables in the recyclebin are purged.
  • When you drop a type, any dependent objects such as subtypes are NOT placed in the recyclebin and any former dependent objects in the recyclebin are purged.

Recyclebin Settings

In 10g release 1, the recyclebin was enabled by default and there was only an undocumented parameter to disable it.  This parameter was _recyclebin which defaulted to a value of TRUE.  The recyclebin initialization parameter was introduced in 10g release 2 and had a default setting of ON It was a dynamic parameter and could be enabled or disabled (ON or OFF) with either an ‘alter session’ or an ‘alter system’ command.  Oracle 11g release 1 made no changes to the basic feature, except to add the ‘deferred’ option to the ‘alter session’ command to allow subsequent, but not the current, session to be affected.  Release 2 remained unchanged from release 1.  These features remain unchained into 12c release 2.  Also, 12c includes more table-associated objects that is moves to the recyclebin when a table is dropped.  The full list of these objects are:

Nested tables

LOB segments

Indexes

Constraints (excluding foreign key constraints)

Triggers

Clusters

To find out if the recyclebin is enabled, except for 10g release 1, you can execute ‘show parameter recyclebin’ from a SQL prompt as a user with sysdba privileges.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

I would recommend keeping the recyclebin enabled at all times, except during an upgrade, to add an extra layer of recovery options to the existing environment.

Using Recyclebin

Once the recyclebin is enabled, you don’t have to give much thought to its use. Every time a ‘drop table’ command is executed, the table and its associated objects are placed in the recyclebin and renamed with a system-generated naming standard. Again, the objects actually remain in their native tablespaces.  Because objects age out of the recyclebin based on demands for space, it is always a good idea to back up a table with an export or execute create table as select (CTAS) to create a backup table before dropping it. This is especially true for dropping very large tables which may not fit in the recyclebin.

I was unable to find any information on exactly how much space is allocated to the recyclebin, but suspect that it can consume all available space in the respective tablespace where the objects reside.

To find out how much space per owner and tablespace recyclebin data is using, you can use this query:

select owner, tablespace_name, round(sum(bytes)/1024/1024/1024,1) "RECYCLE_GB"

from dba_segments

where segment_name like 'BIN$%'

group by owner, tablespace_name 

order by owner, tablespace_name;

In the event that you do not want a table to be preserved in the recyclebin, the addition of the ‘purge’ option at the end of the ‘drop table’ statement ensures that the recyclebin bin is bypassed and the table as well as the associated objects are dropped from the database.  Just ensure you have a backup of the table, just in case.

 

Recovering tables from the recyclebin

To recover a table from the recyclebin, you need to know if it is available. Use the following query to get this information (the owner was designated to limit the output):

select owner, object_name, original_name, droptime

from dba_recyclebin

where owner = ‘CMN’;




OWNER                          OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME

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

CMN                            BIN$o5lg4yWjH3ngRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:23:12:42

CMN                            BIN$o5lg4yWiH3ngRAAUT0rhuQ==$0 CMN_BASE_CONTACT_PK              2011-05-18:23:12:42

CMN                            BIN$o5icV6rtByXgRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:22:51:07

CMN                            BIN$o5icV6rfByXgRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:22:17:45

CMN                            BIN$o5lg4yWvH3ngRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:23:15:11

CMN                            BIN$o5icV6rgByXgRAAUT0rhuQ==$0 EXECUTE_TABLE                    2011-05-18:22:49:58

CMN                            BIN$o5mU5fo6KF/gRAAUT0rhuQ==$0 EXECUTE_TABLE                    2011-05-18:23:30:15

CMN                            BIN$o5icV6ruByXgRAAUT0rhuQ==$0 EXECUTE_TABLE                    2011-05-18:22:53:23

CMN                            BIN$o5mU5foqKF/gRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:23:27:15

CMN                            BIN$o5mU5fo5KF/gRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:23:30:08

CMN                            BIN$o5mU5fo4KF/gRAAUT0rhuQ==$0 CMN_BASE_CONTACT_PK              2011-05-18:23:30:08

CMN                            BIN$o5mU5fpGKF/gRAAUT0rhuQ==$0 CMN_BASE_CONTACT                 2011-05-18:23:32:02

CMN                          BIN$o5mU5fpHKF/gRAAUT0rhuQ==$0 EXECUTE_TABLE                    2011-05-18:23:32:02

Here you can see which tables for the CMN user are in the recyclebin and when they were dropped.  You may have noticed that there are several versions of the BASE_CONTACT table in this recyclebin differentiated by their droptime value.

To also help determine which table holds the data you want recovered, you can query recyclebin tables like regular tables.

 select count(*)

from cmn."BIN$o5mU5fpGKF/gRAAUT0rhuQ==$0";




  COUNT(*)

----------

        89

It is important to use both the owner identifier (CMN in this case) and double-quotation marks because of the special characters in the recyclebin table name.

Once the table you want to recover has been identified, there are a few different methods by which you can recover the table or the individual data. The ‘flashback table <table_name> to before drop’ command will recover the latest copy of the table in the recyclebin. You can also specify the ‘rename to <alt_table_name>’ at the end of the command to recover the table to a different table name to recover specific data. In the case where there are several copies of the table in the recyclebin and you are not interested in the latest copy, you can do a simple create table as select (CTAS) operation using the recyclebin table name, not the original name. Remember to use double-quotation marks around the recyclebin table name due to the special characters.

When you restore a table from the recyclebin, dependent objects such as indexes DO NOT get their original names back; they retain their system-generated recyclebin names. You must manually rename dependent objects to restore their original names. If you plan to manually restore original names for dependent objects, ensure that you make note of each dependent object’s system-generated recyclebin name before you restore the table.

Purging the recyclebin

You can purge the recyclebin completely or for objects belonging to individual users. As stated earlier, each user account in the database will automatically have a recyclebin assigned to it. A user can simply execute the ‘purge recyclebin’ command to purge their own recyclebin. Users with the sysdba privilege can purse the entire recyclebin with the ‘purge dba_recyclebin’ command. Be careful, though, as this command also purges ALL users recyclebins as well. Purging the entire reyclebin should be reserved for upgrade situations, or unless otherwise directed by Oracle Support.

You can also perform selective purges of the recyclebin as a sysdba-privileged user. For example ‘purge tablespace tmpdata’ only purges those objects in the recyclebin belonging to the tmpdata tablespace regardless of which user owns them. Also, ‘purge tablespace tmpdata user jon’ will purge only those objects in the recyclebin belonging to the tmpdata tablespace owned by the named user jon. You can also purge individual tables from the recyclebin with the command ‘purge table <table_name>’. The table name in this command can be either the system-generated recyclebin name or the original name of the table.  As of Oracle version 12c release 2, you need to purge individual user’s objects out of the recyclebin by tablespace as a sysdba-privileged user.

The recyclebin is one of those features that you forget, but are very thankful when it is available and you need it.