适用的平台

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.

现象

如下查询语句会使用较高的undo表空间- “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

注意一些变化

由于这个问题，客户不得不求助于隐藏的init.ora参数来控制撤销保留时间，这从本质上否定了自动撤销调优的好处。

原因

主要问题是bug

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

解决办法

需要修改隐含参数

- set _smu_debug_mode=33554432 
 - set _undo_autotune=false 
 - set _HIGHTHRESHOLD_UNDORETENTION

或者应用该bug补丁

翻译参考： High Undo Space Usage Due to Query 0rc4km05kgzb9 (Doc ID 2584684.1)