Intro To SQL Quarantine

In the last blog post we talked about Quarantined Objects, which have no relationship whatsoever with today’s topic. I just wanted to eliminate any confusion about today’s topic and last week’s topic, they are not related!

A new feature in 19c worth noting is the SQL Quarantine.
As of this writing, this feature is only available for Exadata (Oracle Database Enterprise Edition on Engineered Systems) and DBCS/ExaCS (Oracle Database Exadata Cloud Service), as highlighted in the Database Licensing Information User Manual. In other words, this feature is not available for 19c on prem configurations.

Since most of you are not working with Exadata or Autonomous database, you might not have the feature available to you. However, this does not prevent you from getting a high level understanding of the feature, which is the purpose of this blog post, so keep on reading!

What is SQL Quarantine?

SQL Quarantine is a feature that can be used to eliminate the overhead of runaway queries. What are runaway queries? These are queries that use up lots of resources, CPU and IO, and end up being terminated by Resource Manager, because they exceeded a resource or a run time limit. The DBRM background process, which is the database resource manager process, detects SQL statements that exceed a resource limit or run time limit, that is set in the database. The SQL execution plan and the SQL statement that exceed the limit, is quarantined, meaning if the same statement runs again, using the same execution plan, the SQL statement will be terminated with the following error: ORA-56955: quarantined plan used. The SQL Quarantine reduces the system resources that would be wasted otherwise. SQL statements that are quarantined, are terminated prior to their execution, and won’t be using any resources.

At a very high level, this is what you need, to setup SQL Quarantine

• create a resource plan that limits the SQL execution time to a specific number of seconds. SQL Statements that exceed this limit, run longer than X seconds, will be terminated.
• run a SQL statement that exceeds this limit, X seconds.
• you quarantine the SQL statement using DBMS_SQLQ package. You can check the DBA_SQL_QUARANTINE view to see quarantined SQL statements.
• run the SQL statement again. If the statement runs with the same execution plan, then the statement is terminated right away with: ORA-56955 quarantined plan used.

There you have it, a high level understanding of SQL Quarantine!

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!