Oracle 19c - New Feature - SQL Quarantine - How to quarantine bad SQL in 19c - Step by Step Explored with an Demo!
Dear DBA Connections,
In this video, we are going to see Oracle 19c feature SQL quarantine. This feature will not be available for on premises enterprise databases. But still we can test in our Oracle 19c Enterprise edition database there is a way to fake the Exadata environment on your sandbox/lab databases for testing purpose.
SQL Query Quarantine. The overall performance of a data mart or a data warehouse can suffer when a user runs a query that consumes an excessive amount of I/O and compute resources. Oracle Database 19c can automatically quarantine those queries and ensure that they don’t run again, resulting in consistent performance for all database users
If you want to play and learn this functionality, Please do watching this video, which I am going to demonstrate in my lab environment.
WARNING: - I strongly and strictly suggest you all to be used in your sandbox, on your personal database for learning purposes. DO NOT implement this at work/realtime environment, which will be broken the license agreement.
The below high level steps are that you need to carried out for this DEMO:
Step 1 : Setup your database as if you are running Exadata.
Step 2 : Create test user with necessary privileges
Step 3 : Setup Resource Manager with the following sub-steps:
Step 4 : Create resource plans
Step 5 : Create resource consumer group(s)
Step 6 : Create resource plan directives
Step 7 : Grant switch privilege for resource consumer groups to users or roles
Step 8 : Assign users to resource consumer groups
Step 9 : Specify a plan to be used by the instance
Step 10 : Run a select statement that will take longer than the specified limit in Resource Manager. This SQL statement should get terminated.
Step 11: Determine the SQL ID of the SQL statement
Step 12: Create a SQL Quarantine for the SQL Statement.
Step 13: Run the select statement again. This time the SQL statement should not even execute.
Let's do it as a practical demo,
Step 1: Setup your database as if you are running Exadata:
alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;
Step 2: Create a test user:
CREATE USER testuser1 IDENTIFIED BY testuser1 QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE SESSION, CREATE PROCEDURE TO testuser1;
Step 3: Setup Resource Manager with the following sub-steps:
--Create pending area
begin
dbms_resource_manager.create_pending_area();
end;
/
Step 4: Create resource plans:
begin
dbms_resource_manager.create_plan(
plan => 'TEST_PLAN',
comment => 'Resource mgr plan for testing SQL Quarantine');
end;
/
Step 5: 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;
/
Step 6: Create resource plan directives
--the CANCEL_SQL group exists by default.
begin
dbms_resource_manager.create_plan_directive(
plan => 'TEST_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => 'terminate SQL after 10 seconds',
switch_group=>'CANCEL_SQL',
switch_time => 10,
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;
/
Step 6: 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 => 'TESTUSER1',
consumer_group => 'LIMIT_SQL',
grant_option => FALSE);
end;
/
Steps 7: Assign users to resource consumer groups
begin
dbms_resource_manager.set_initial_consumer_group(
user => 'TESTUSER1',
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.
The statement must be executed as the TESTUSER1 user, and TESTUSER1 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
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.
CONN testuser1/testuser1@//192.168.58.101:1521/srlabdbplug
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;
/
or
CREATE OR REPLACE FUNCTION testuser_cpu (p_mins IN NUMBER)
RETURN NUMBER
AS
l_start_time DATE;
l_number NUMBER := 1;
BEGIN
l_start_time := SYSDATE;
LOOP
EXIT WHEN SYSDATE - l_start_time > (p_mins/24/60);
l_number := l_number + 1;
END LOOP;
RETURN 0;
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.
SQL> 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;
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!
Views:
COLUMN sql_text FORMAT A30
COLUMN sql_quarantine FORMAT A40
SELECT sql_text, sql_id, plan_hash_value, child_number, sql_quarantine, avoided_executions
FROM v$sql
WHERE sql_quarantine IS NOT NULL;
Metalink Note: 19c New Feature SQL Quarantine Not Work (Doc ID 2634990.1)
1 comments :