My Oracle Support Banner

Recommended Patches for Managing Runaway Queries with Oracle Database Resource Manager (Doc ID 1340184.1)

Last updated on APRIL 12, 2022

Applies to:

Oracle Database Products > Oracle Database Suite > Oracle Database
Oracle Database Products > Exadata > Database Machine
Information in this document applies to any platform.

Details

This note lists the recommended patches for using Oracle Database Resource Manager to manage runaway queries.  It supersedes MOS <Document 1208133.1>.

It applies to Oracle RDBMS Enterprise Edition, versions 10.2.0.4, 10.2.0.5, 11.1.0.7, 11.2, and 12.1.   

The Oracle Database Resource Manager can be used in many ways - managing CPU, I/O, parallel execution, runaway queries, etc. This note addresses issues that are specific to managing runaway queries.
Resource Manager is configured to manage runaway queries via the following directives: 

   switch_group 
   switch_time 
   switch_estimate 
   max_est_exec_time 
   switch_time_in_call (deprecated) 
   switch_for_call 
   switch_io_megabytes 
   switch_io_reqs
   switch_io_logical (new in 12.1)
   switch_elapsed_time (new in 12.1)

Recommended Bug Fixes

Below is a list of the most critical, known bugs for managing runaway queries with these directives. These bug fixes are recommended for all customers that are either evaluating or currently using Resource Manager to manage runaway queries.

Bug

Description

Recommended Releases
18029594

RUNAWAY QUERY INEFFECTIVE FOR PQ SERVERS

For RDBMS 11.2.0.4 only, this bug fix is required for runaway query to work for parallel operations

11.2.0.4
16571172

RUNAWAY QUERY MANAGEMENT SHOULD NOT REQUIRE TUNING PACK

In RDBMS 11.2.0.4 and all subsequent releases, runaway query management is integrated with SQL Monitor, which requires the Oracle Tuning Pack.  If the database does not have the Oracle Tuning Pack option, then runaway query management will not work.  For 11.2.0.4 and 12.1.0.1, this bug fix is required for databases without the Oracle Tuning Pack option.

11.2.0.4, 12.1.0.1
8633905

V$RSRC_CONSUMER_GROUP.SWITCHES_IN_CPU_TIME INCORRECT

The statistic in V$RSRC_CONSUMER_GROUP.SWITCHES_IN_CPU_TIME is incorrect. It incorrectly includes switches due to the "switch_io" directives and switches back to the original consumer group.

11.1.0.7, 11.2.0.1
6431167

CONSUMED CPU TIME REPORTED INCORRECTLY

The amount of CPU consumed is intermittently incorrect. As a result, Resource Manager sometimes mis-identifies runaway queries that are defined using "switch_time".

10.2.0.4
7198795

SWITCH_FOR_CALL DOESN'T WORK

This bug affects runaway queries that are identified on a per-operation basis, using the "switch_for_call" directive. Without this fix, operations for the "switch_time" and "switch_io" thresholds are defined as a single OCI call. Since operations like PL/SQL routines and queries are typically composed of many OCI calls, runaway query management is not very effective without this fix.

10.2.0.4
11.1.0.7

7835112

ORA-600[1115] SIGNALLED DURING PLAN SWITCHING

This bug can cause PMON (and the instance) to exit with the error, ORA-600[1115]. This bug is intermittent and only occurs if the "kill_session" option is used.

10.2.0.4
11.1.0.7

8202097

VKRM SIGNALLED ORA-7445[KSKFBLOCKER()+27] +ACTIVE_TIME DOESN'T EXCLUDE WAIT TIME

The error, ORA-7445[KSKFBLOCKER()+27], may occur if the "kill_session" option is used. This bug fix also fixes "switch_time" to correspond to CPU consumed rather than elapsed time.

10.2.0.4
11.1.0.7

8369546

SQL EXECUTION GETS INTERRUPTED BY RESOURCE MANAGER WHEN EXEC TIME <MAX EXEC TIME

When the "cancel_sql" option is used, the timer for detecting the next runaway query is not reset after the operation has been cancelled. The workaround is to always set "switch_for_call" to true when the "cancel_sql" option is used.

10.2.0.4
11.1.0.7
6800544

DATABASE HANGS WITH SETTING RESOURCE_MANAGER_PLAN AFTER QUERYTIMEOUT PASSES

Usage of ctrl-c or the "cancel_sql" option may result in premature aborts for the next database operation. For database instances running on a single CPU, this bug may also result in a database hang.

10.2.0.4
11.1.0.7

 

Helpful Tips

1. For most use cases, the Resource Manager directive "switch_for_call" should be set to TRUE.  If switch_for_call is TRUE, then the session's CPU and I/O usage counters are reset at the start of each query or PL/SQL block.  Otherwise, the CPU and I/O usage counters are reset when the session is inactive and idle for a certain period of time.

2. The "switch_time" directive specifies the amount of CPU time befor the action is taken.  "CPU time" corresponds to time that the session is actively using CPU (which is also called DB CPU time).  It does not correspond to elapsed time.  Elapsed time is first supported in the 12.1.0.1 release, using the directive "switch_elapsed_time". 

3. For all releases before 11.2.0.4, the CPU and I/O usage counters are reset to 0 each time a resource plan is set. 

4. For 11.2.0.4 and all subsequent releases, queries that are started before the Resource Manager plan is enabled are not managed.  Therefore, when using runaway query management, enable the Resource Plan at database startup.

For other recommended bug fixes, monitoring scripts, and other tips for Resource Manager, see the Primary Note for Oracle Database Resource Manager MOS <Document 1339769.1>.

Actions

 

Contacts

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Details
 Recommended Bug Fixes
 Helpful Tips
Actions
Contacts
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.