SQL Quarantine

From Robs_Wiki
Jump to: navigation, search


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.

Functional overview 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.