SQL Quarantine In Action On Your Laptop

Last week I gave you a taste for SQL Quarantine. Some of you asked me if I know when this feature will be available for on prem databases. The answer to that question, I do not know. But, what I found, thanks to Mahmoud Hatem (thank you very much), is a way to fake the Exadata environment on your database that runs on your laptop.
If you want to play and learn this functionality, aka get your hands dirty, then keep on reading.

WARNING, this is strictly to be used in your sandbox, on your personal database for learning purposes. DO NOT implement this at work, in no shape or form. You have been warned!!!

At a high level below are the steps that you need to take:

1. Setup your database as if you are running Exadata.
2. Setup Resource Manager with the following sub-steps:
   Create resource plans
   Create resource consumer group(s)
   Create resource plan directives
   Grant switch privilege for resource consumer groups to users or roles
   Assign users to resource consumer groups
   Specify a plan to be used by the instance
3. Run a select statement that will take longer than the specified limit in Resource Manager. This SQL statement should get terminated.
4. Determine the SQL ID of the SQL statement
5. Create a SQL Quarantine for the SQL Statement.
6. Run the select statement again. This time the SQL statement should not even execute.

Let’s get to work.

Setup your database as if you are running Exadata.

alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;

Setup Resource Manager with the following sub-steps:

--Create pending area
begin
dbms_resource_manager.create_pending_area();
end;
/

--Create resource plans

begin
dbms_resource_manager.create_plan(
plan => 'TEST_PLAN',
comment => 'Resource mgr plan for testing SQL Quarantine');
end;
/

--Create resource consumer group(s)

begin
dbms_resource_manager.create_consumer_group(
consumer_group => 'LIMIT_SQL',
comment => 'consumer group with limited SQL exec time');
end;
/

--Create resource plan directives
--the CANCEL_SQL group exists by default.

begin
dbms_resource_manager.create_plan_directive(
plan => 'TEST_PLAN',
group_or_subplan => 'LIMIT_SQL',
comment => 'terminate SQL after 5 seconds',
switch_group=>'CANCEL_SQL',
switch_time => 5,
switch_estimate=>false);
end;
/

--Validate pending area
begin
dbms_resource_manager.validate_pending_area();
end;
/

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

begin
dbms_resource_manager.submit_pending_area();
end;
/

In the next steps we need to assign the grants and assign consusmer groups to the users.

--Create pending area for privilages, roles and assign users

begin
dbms_resource_manager.create_pending_area();
end;
/

--Grant switch privilege for resource consumer groups to users or roles

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

--Assign users to resource consumer groups

begin
dbms_resource_manager.set_initial_consumer_group(
user => 'HR',
consumer_group => 'LIMIT_SQL');
end;
/

--Validate Pending area

begin
dbms_resource_manager.validate_pending_area();
end;
/

--Submit pending area

begin
dbms_resource_manager.submit_pending_area();
end;
/

At this point the Resource Manager is setup. The only outstanding step is to assign the created plan to the instance.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = TEST_PLAN;

Run a select statement that will take longer than the specified limit in Resource Manager. This SQL should get terminated.

Since this is my personal database, I do not have much data in it. So I made up a really silly statement, that should take longer than 5 seconds to run.
The statement must be executed as the HR user, and HR has to have access to the DBA views.

select a.object_name,b.table_name,c.owner,d.object_type from dba_objects a, dba_tables b, dba_tab_privs c,dba_objects d
*
ERROR at line 1:
ORA-00040: active time limit exceeded - call aborted

You noticed above that Resource Manager terminated the execution, with ORA-00040.

Determine the SQL ID of the statement

The SQL ID of the above statement is 8znwt0h4da4k1

Create a SQL Quarantine.

For creating the quarantine, you would use DBMS_SQLQ package, as below:

DECLARE
quarantine_sql VARCHAR2(30);
BEGIN
quarantine_sql := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => '8znwt0h4da4k1');
END;
/

Now you can query DBA_SQL_QUARANTINE view to see what SQL is quarantined.

Run the select statement again. This time the SQL should not even execute.

select a.object_name,b.table_name,c.owner,d.object_type from dba_objects a, dba_tables b, dba_tab_privs c,dba_objects d;

ERROR at line 1:
ORA-56955: quarantined plan used

You will notice that the SQL did not even execute. It terminated with ORA-56955 error.

If you check V$SQL view, you will notice 2 columns that are new: sql_quarantine and avoided_executions:

select sql_quarantine,avoided_executions from v$sql where sql_id='8znwt0h4da4k1';

Now it is your turn, to give it try and see how SQL Quarantine works!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!