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.
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, 18.104.22.168, 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_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.
RUNAWAY QUERY INEFFECTIVE FOR PQ SERVERS
RUNAWAY QUERY MANAGEMENT SHOULD NOT REQUIRE TUNING PACK
In RDBMS 22.214.171.124 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 126.96.36.199 and 188.8.131.52, this bug fix is required for databases without the Oracle Tuning Pack option.
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.
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".
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.
ORA-600 SIGNALLED DURING PLAN SWITCHING
This bug can cause PMON (and the instance) to exit with the error, ORA-600. This bug is intermittent and only occurs if the "kill_session" option is used.
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.
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.
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.
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 184.108.40.206 release, using the directive "switch_elapsed_time".
3. For all releases before 220.127.116.11, the CPU and I/O usage counters are reset to 0 each time a resource plan is set.
4. For 18.104.22.168 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>.
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
|Recommended Bug Fixes|