#### 适用的平台 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'". ``` sql 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 #### 解决办法 需要修改隐含参数 ``` shell - 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)