SQL Quarantine
Automatic SQL Quarantine
Automatic SQL Quarantine is used in conjunction with resource manager.
With Resource Manager, you can configure limits for SQL statements for consuming system resources (Resource Manager thresholds). The Resource Manager terminates SQL statements that exceed the Resource Manager thresholds. In the earlier Oracle Database releases, if a SQL statement that is terminated by the Resource Manager runs again, the Resource Manager allows it to run again and terminates it again when it exceeds the Resource Manager thresholds. Thus, it is a waste of system resources to allow such SQL statements to run again.
Starting with 19c, SQL statements that are terminated by Oracle Database Resource Manager due to their excessive consumption of CPU and I/O resources are automatically quarantined. The execution plans associated with the terminated SQL statements are quarantined to prevent them from being executed again.
An individual SQL statement may have multiple execution plans, and if it attempts to use the execution plan that is quarantined, then that SQL statement is not allowed to run. However, the same SQL Statement with a different execution plan is allowed to run, unless of course, this is subsequently terminated by resource manager.
SQL Quarantine information is periodically persisted to the data dictionary. it is important to note, that when resource manager terminates a SQL statement, it may be several minutes before the statement is quarantined
This feature protects an Oracle Database from performance degradation by preventing execution of SQL statements that excessively consume CPU and I/O resources.
Manual SQL Quarantine
When resource manager terminates a SQL statement due to high CPU or excessive IO, behind the scenes Oracle performs the following
1. Oracle creates a SQL Quarantine configuration
2. Oracle sets the SQL Quarantine Configuration thresholds based on the thresholds set in resource manager.
3. Oracle enables the SQL Quarantine configuration
The 3 steps above can be performed manually for statements not captured by resource manager. Furthermore, the automatic implementation of quarantine configurations by resource manager can be switched off. In such a case, you may wish to perform steps 1 to 3 above manually. The following pages explain how to do this - SQL Quarantine
Published 1st April 2022
