Day 5: SQL Quarantine

December 14, 2020

A highly anticipated new feature of Oracle Database 19c is SQL Quarantine. Before we begin, it is important that customers are aware that this feature is only available on Oracle Exadata and Autonomous Databases on Oracle Cloud or Cloud at Customer. As the name would imply, this feature adds a new tool for monitoring and managing runaway queries. In the past you may have implemented a cron job to monitor long running queries or utilized Enterprise Manager to alert on these events.

Now, we can simply set up the initial configuration and enable Oracle to handle the issue for us.

Complete Preinstallation Tasks

In Oracle Database 18c, Oracle introduced a new feature to provide DBAs the ability to cancel a query (I.e. kill the offending query) and not the session. Leveraging the ALTER SYSTEM command, we can pass the arguments to cancel a query, allowing the session to continue to process. Many times, for adhoc users, we do not want to kill the offending user issuing the SELECT statement, but just cancel the query. The syntax to cancel a SQL statement is:

ALTER SYSTEM CANCEL SQL 'SID, SERIAL[, @INST_ID][, SQL_ID]';

A common use case for leveraging this feature would be to cancel a SQL in a session on the current instance or on the remote RAC instance. The syntax would look like this:

ALTER SYSTEM CANCEL SQL '921, 99210';

To cancel the SQL in session on the remote RAC instance (INST_ID = 2)

ALTER SYSTEM CANCEL SQL '921, 99210, @2';

Going back to our topic on SQL Quarantine, when Resource Manager (DBRM) detects SQL is exceeding resources or run time, the SQL execution plan is quarantined, and further attempts to run this plan will cause it to be terminated immediately prior to execution. If any of the Resource Manager thresholds is equal

to or less than a quarantine threshold specified in a SQL statement’s quarantine configuration, then the SQL statement is not allowed to run. Previous versions

allowed sql to run again and again until it hit a resource limit, then it was terminated and allowed to run again.

Steps to Enable SQL Quarantine

To receive the full 5 page article complete with code, please complete the form provided.  A pdf will be automatically emailed to you, so be sure to check your spam/junk mail if you do not see it.

    Full Name (required)

    Your Email (required)

    Company (required)

    Phone number (required)

    Job Title (required)