SQL QUARANTINE

This Oracle Database 19c new feature is available on certain Oracle Database platforms. Check the Oracle Database Licensing Guide for more information.

RUNAWAY SQL STATEMENTS

Runaway SQL statements can be a threat to database service levels by consuming significant system resources if they remain unchecked. This is a particular problem if multiple runaway queries are running concurrently. The Oracle Database Resource Manager (DBRM) has always offered the ability to terminate a SQL statement that exceed certain resource utilization thresholds (such as CPU and IO) as well as the ability to terminate queries that exceed maximum runtime thresholds. However, this does not prevent repeated execution of runaway queries. A problem query may be run over and over again, each time consuming significant resources before being terminated each time.

For example:

For the sake of example, we will assume that the machine CPU utilization looks something like this in response to the runaway queries being executed. In this case, when three queries are running concurrently, the CPU is nearly at 100%:

SQL QUARANTINE

The new Oracle Database 19c feature SQL Quarantine can be used to eliminate the overhead of runaway queries. When DBRM detects a SQL statement is exceeding a resource or run-time limit, the SQL execution plan used by the statement is quarantined. 

If the SQL statement is executed again and it is using the same SQL execution plan then it will be terminated immediately. This can significantly reduce the amount of system resource that would otherwise be wasted. 

In the following example, there is high utilization when the initial queries are executed, but once they are quarantined they no longer consume system resources because they are terminated prior to execution:

Figure 21 - CPU saved by SQL quarantine

DBRM must be configured to apply a limit. In this case, queries executing for more than 5 seconds will be terminated:

-- The plan will cancel the current SQL if elapsed time exceeds 5 seconds.

dbms_resource_Manager.create_plan_directive(

plan => 'LIMIT_RESOURCE',

group_or_subplan => 'TEST_RUNAWAY_PLANS',

comment => 'Kill statement after exceeding desired execution time',

switch_group => 'CANCEL_SQL',

switch_time => 5,

switch_estimate => false

);

A DBRM exception is raised as follows:

SQL> select /* Time consuming query */ * from ...;

*

ERROR at line 1:

ORA-00040: active time limit exceeded - call aborted

Elapsed: 00:00:07.59

Quarantine information is held in memory and flushed to disk periodically. Once the quarantine configuration is persisted in the data dictionary, the following error condition will be raised:

SQL> select /* Time consuming query */ * from ...;

*

ERROR at line 1:

ORA-56955: quarantined plan used

Elapsed: 00:00:00.00