Quarantine SQL Plans

One area that we need to always monitor in Siebel applications deployments is runaway queries that often consumes vast database resources and potentially might significantly upset production environment. 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 doesn't 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.

The new Oracle Database Oracle 19c Database and future releases 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's 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's high utilization when the initial queries are executed, but once they're quarantined, they no longer consume system resources because they're terminated prior to execution.

DBRM must be configured to apply a limit. In this example, 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
);

You can try and manually tune the statement, so that a new plan is generated. If the statement has a new plan it will be allowed to execute again and will only go back into quarantine if the new plan exceeds the specified limits.

The specific SQL statements can also be quarantined. The DBMS_SQLQ package provides the interface for configuring quarantine thresholds for execution plans of SQL statements. If any of the Resource Manager thresholds is equal to or less than the quarantine threshold specified in a SQL statement's quarantine configuration, then the SQL statement is not allowed to run, if it uses the execution plan specified in its quarantine configuration.

The following example creates a quarantine configuration for the execution plan having the hash value of 3488063716 for the SQL statement having the SQL ID of 8vu7s907prbgr.

DECLARE
  	quarantine_config VARCHAR2(30);
BEGIN
 quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID (SQL_ID => '8vu7s907prbgr', PLAN_HASH_VALUE => '3488063716');
END;
/

A new column in V$SQL called QUARANTINED indicates which SQL statement’s plan has been placed in quarantine. While the AVOIDED_EXECUTION column indicates number of executions attempted after plan was quarantined.

Note: As long as SQL Quarantine kills the session, Siebel will be fine and must return an error to the user. However, if it only cancels the query, depending in which step oracle is when this cancel is issued, Siebel might restart the SQL again “Siebel Retry”.

However, SQL Quarantine through Oracle Resource Manager (RM) is cancelling the SQL, however there is a “kill” option in RM that terminates/kills the session.