ORA-01555 for long running queries if "_undo_autotune"=true

刘泽宇

现象：
+ This is Oracle Database 12.1.0.2.

+ The undo_retention is set high enough (i.e: to 50,000 seconds).

+ Despite of having plenty of expired extents in the undo tablespace, the tuned undo retention drops down to around 5318 seconds for no good reason.

+ The failing queries are long running ones but always with duration that is less than the undo retention setting.


原因：
Bug 31113682 - ORA-1555 FOR LONG RUNNING QUERIES IF "_UNDO_AUTOTUNE"=TRUE


<undopressure.out> shows that the tuned undo retention was set to 50,000, but later decreased significantly to 5318 seconds (~= 1.48 hours) because of the undo pressure and because of the NOGUARANTEE configuration.
It is clear that the automatic undo tuning is wrongly calculated leading to unjustifiable ORA-01555 error.

In case calculated tuned_undoretention is of small value whereas undo tablespace have sufficient free space and there are long running transactions, it is possible to be hitting this bug.

处理方法：
The fix for Bug 31113682 is needed especially on 12.1.0.2. This bug is fixed in DB version 23.1.

Here is the patch download link:
https://updates.oracle.com/download/31113682.html

Alternatively, someone may workaround the problem by disabling the automatic undo tuning by setting "_undo_autotune" to false.