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.
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 ad-hoc 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:
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:
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 are 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.
In order to activate SQL Quarantine, you must first do some configuration. At a very high level, this is what you will need to set up and test SQL Quarantine:
As we mentioned above, the SQL Quarantine feature is only available on the Exadata platform. To take advantage of this feature for testing purposes on non-engineered systems, we can leverage anunderscore parameter called _exadata_feature_on:
To enable SQL Quarantine, we leverage the DBMS_SQLQ package:
DECLARE
Note: If a PLAN_HASH_VALUE is null, then quarantine applies to all executions of the sql_id.
After creating a quarantine configuration for an execution plan of a SQL statement, you can specify quarantine thresholds for it using the DBMS_SQLQ.ALTER_QUARANTINE procedure.
Quarantine threshold options:
Set threshold example:
Note: Also query the view SQl>select * from DBA_SQL_QUARANTINE;
Any unused quarantine configurations are automatically purged or deleted after 53 weeks, but you can manually drop the SQL quarantine configuration for a SQL statement.
When a SQL statement is terminated by the Resource Manager, as it exceeds a threshold, the execution plan for the SQL statement is now added to the quarantine list so that it is not allowed to run again.
DBRM Setup –High Level
That's all you need to get started with SQL Quarantine.
In Oracle Database18c, Oracle provides the capability to kill an offending SQL and leave the connected session with the database intact. In Oracle Database 19c, Oracle provides us the capability to quarantine the query so that it can never come back to cause havoc.
One of the sayings we have at Viscosity is our customers "have four aces in their pocket.” Over the next 7 days, the talented staff at Viscosity, along with our Oracle ACEs, will address more Oracle Database 18c and 19c new features. Continue to join us next year as we continue our Oracle Database 19c hands-on lab workshops.
Happy Holidays!