High Undo Space Usage Due to Query 0rc4km05kgzb9 (文档 ID 2584684.1)

High Undo Space Usage Due to Query 0rc4km05kgzb9 (文档 ID 2584684.1) 转到底部

________________________________________

In this Document
Symptoms

Changes

Cause

Solution

________________________________________
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.1.0.6 to 18.1.0.0.0 [Release 11.1 to 18]
Information in this document applies to any platform.
SYMPTOMS
High Undo Space usage is reported due to the query - "select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'".
Status Total Extents
--------- -------------
UNEXPIRED 2,189
ACTIVE 1
EXPIRED 3
############## Historical V$UNDOSTAT (Last 2 Days) ##############
Query
Maximum Undo # of Tuned Retention
Date/Time Minutes SqlID TBS Blocks Trans # of Unexpired # of Expired (Minutes)
------------------ -------- ------------- -------- ------------ ------------ -------------- ------------ ---------------
23-14:12 30 0rc4km05kgzb9 2 61 1,719 233,888 3,584 19,906
23-14:22 20 0rc4km05kgzb9 2 65 1,587 233,888 3,584 19,993
23-14:32 10 0rc4km05kgzb9 2 59 1,653 233,888 3,584 20,081
...
23-10:32 0rc4km05kgzb9 4z6jfdhm5uxr7 9 Runaway Query-Active 0
23-10:42 0rc4km05kgzb9 4z6jfdhm5uxr7 9 Runaway Query-Active 0
23-10:52 0rc4km05kgzb9 4z6jfdhm5uxr7 9 Runaway Query-Active 0

CHANGES
Because of this problem, customers have to resort to hidden init.ora parameters to keep undo retention time in control, essentially negating the benefit of automatic undo tuning.

CAUSE
You could be affected by:
Bug 26833932 : OCIRELEASESTMT TO PUT THE CURSORS IN CURBOUND STATE WHEN PLACING THEM IN CACHE
SOLUTION
You can set workarounds below, however the issue will still persist:
- set _smu_debug_mode=33554432
- set _undo_autotune=false
- set _HIGHTHRESHOLD_UNDORETENTION
Solution is to apply the fix below:

Bug 26833932 : OCIRELEASESTMT TO PUT THE CURSORS IN CURBOUND STATE WHEN PLACING THEM IN CACHE