Recommended Patches for Managing Runaway Queries with Resource Manager
(Doc ID 1208133.1)
Last updated on OCTOBER 15, 2019
Applies to:
Oracle Database Backup Service - Version N/A and laterOracle Database Cloud Exadata Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.4 [Release 10.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Oracle RDBMS Enterprise Edition
Details
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
Actions
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 | Bug Description (with comments in parenthesis) | Fixed Release |
|---|---|---|
| 13349073 |
CANCEL_SQL DOES NOT STOP THE SQL EXECUTION BASED ON SWITCH_TIME |
11.2.0.4 |
| 8633905 | V$RSRC_CONSUMER_GROUP.SWITCHES_IN_CPU_TIME INCORRECT (This bug results in incorrect statistics in v$rsrc_consumer_group.) |
11.2.0.2 |
| 9403364 | DBRM MAX_EST_EXEC_TIME FEATURE IS NOT RELIABLE (This bug results in problems using estimated execution times when the estimates are extremely large.) |
11.2.0.2 |
| 7198795 | SWITCH_FOR_CALL DOESN'T WORK (Without this bug fix, consumer group switching occurs for every OCI call. This is non-ideal for SQL statements that are implemented with multiple OCI calls, such as PL/SQL statements, long-running queries, etc.) |
11.2.0.1 10.2.0.5 |
| 7835112 | ORA-600[1115] SIGNALLED DURING PLAN SWITCHING (When using the kill_session option, this error can occur, resulting in PMON death and a subsequent instance crash.) |
11.2.0.1 10.2.0.5 |
| 7245076 | CANNOT EXPLAIN QUERY IF MAX_EST_EXEC_TIME IS SET (This bug can prevent the DBA from getting a query execution plan when the max_est_exec_time directive is set.) |
11.2.0.1 |
| 8202097 | VKRM SIGNALLED ORA-7445[KSKFBLOCKER()+27] +ACTIVE_TIME DOESN'T EXCLUDE WAIT TIME (When the switch_time directive is set, the time measured includes any wait time, resulting in queries being prematurely labeled as runaway.) |
11.2.0.1 10.2.0.5 |
| 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 should be reset after a call has been cancelled.) |
11.2.0.1 10.2.0.5 |
| 6431266/ 6431167 | TURN OFF QUANTUM DONATION: PLAN CONFORM DISPARITY + HIGH THRESHOLD BASED ON LOW (This bug results in poor conformance to the Resource Plan. It also results in incorrect statistics for tracking CPU consumption. This bug fix is listed under two different bug numbers, as shown to the left. Please request BLRs using bug #6431266.) |
11.1.0.7 10.2.0.5 |
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! |