SQL Quarantine - Oracle 19c Feature
Arangaperumal Govindsamy
Principal Technology Solution Engineer at Oracle || Oracle DB and DB Option,SnowFlake,SQL Server,MySQL, Greenplum || Oracle Security || Oracle GoldenGate || OCP || OCI || AWS
When a query consumes more resources and run for long time, we will cancel the query. With Oracle Resource Manager, we can limit and regulate the use of resources. The query will be cancelled if we set threshold limit. But you have no mechanism to prevent the same query from being executed again and again running for specified limit and getting cancelled. To address this, Oracle 19c has feature called SQL Quarantine.If the query exceeds the specified limit, the Oracle Resource manager terminates the execution of the statement and quarantines the plan.
Oracle has DBMS_SQLQ package to do quarantine configurations. The following operation can be performed using this package
- Enable/Disable a quarantine
- Delete the quarantine configuration
- Transfer from one DB to another.
We can create threshold for the following
- CPU time
- Elapsed time
- I/O
- No of physical I/O request
- No of logical I/O request
NOTE: This feature is only available on Engineering System such as Exadata and Exadata Cloud Service.