Oracle 每一个版本都发布的用于调查 ORA-1555 错误的脚本，二分切每一版本都有所不同。这些脚本只适用于自动 UNDO 管理 (AUM) 配置的环境。
脚本文件以本文档附件的形式提供。在mos上可以下载到，具体的脚本有如下列出的分类，下载地址参考此链接：http://www.ludatou.com/?page_id=2479
AUM 配置和 ORA-1555 全面分析
1. 配置：
UndoDatafiles.sql — spool 输出到位于默认目录位置的文件 undodatafiles.out 中。
UndoParameters.sql — spool 输出到位于默认目录位置的文件 undoparameters.out 中。
UndoUsage.sql — spool 输出到位于默认目录位置的文件 undousage.out 中。
2. 当前未提交的事务：
CurrentActivity.sql — spool 输出到位于默认目录位置的文件 undoactivity.out 中。
3. 历史 UNDO 信息：
UndoHistoryInfo.sql — spool 输出到位于默认目录位置的 undohistory.out 中。
UndoStatistics.sql — spool 输出到位于默认目录位置的 undostatistics.out 中。您可修改此报告以显示适当的分析时间范围。默认情况下，查看最后两天的 V$UNDOSTAT 数据。在 V$UNDOSTAT 视图中，数据会保留七天。
4. 等待/锁定分析：
UndoPressure.sql — spool 输出到位于默认目录位置的 undopressure.out 中。
5. 调查 LOB 问题：
LobData.sql — spool 输出到位于默认目录位置的 lobdata.out 中。
1. 配置
示例 undodatafiles.out
Run Time
—————–
05-Jul-2023 08:53
############## DATAFILES ##############
Aut
TBSP Name File # Bytes Alloc (MB) Max Bytes Used (MB) (MB) Ext
—————————— —— ————————- ———————————— ——
SMALLUNDO 3 200 200 YES
查看配置数据。AUTOEXTEND 是否打开？如果 UNDO 表空间配置为随着空间需求自动增长，这会对数据库造成影响，数据库可能不会重新使用超过Retention设置的过期 Undo extent，以减少发生 ORA-1555 的几率。表空间进而会随着新的需求增长。
示例 undoparameters.out
Run Time
—————–
05-Jul-2023 08:56
############## PARAMETERS ##############
Instance # Parameter Session Value Instance Value
————– ———————————– ————————- ————————-
1 _smu_debug_mode 33554432 33554432
1 _undo_autotune TRUE TRUE
1 undo_management AUTO AUTO
1 undo_retention 900 900
1 undo_tablespace SMALLUNDO SMALLUNDO
查看影响 Undo Retention规则的参数设置。
‘_smu_debug_mode’=33554432 会强制让自动优化程序基于系统中运行时间最长的 SQL 的执行时间来计算自动的 undo retention。在默认情况下，自动调整后的保留时间会增长到很长的时间段，空间压力将成为 Undo 表空间中的重大问题。
‘_undo_autotune’=false 是一些 AUM bug 的权宜方法，但这会对分析产生重大影响。V$UNDOSTAT 中不会再进一步跟踪其他数据，显式指定的的 UNDO_RETENTION 设置是影响 undo Retention处理的关键。
示例 undousage.out
Run Time
————————–
05-JUL-2023 08:58
############## IN USE Undo Data ##############
PCT_INUSE
—————-
23.625
TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN RETENTION
——————————— ———————— ———————- —————- —————–
SMALLUNDO LOCAL SYSTEM MANUAL NOGUARANTEE
Sum of Free
—————-
65,536
Total Bytes
—————-
209,715,200
############## UNDO SEGMENTS ##############
Status Total Extents
—————— —————–
UNEXPIRED 21
EXPIRED 807
ACTIVE 195
————-
sum 1,023
Status Total Segments
——————– ——————-
ONLINE 11
————-
sum 11
2. 当前未提交的事务
示例 undoactivity.out
Run Time
—————–
19-Jul-2023 09:43
############## Current Uncommitted Transactions ##############
Started User Undo Segment Name File # Block # Status KBytes Rows
———— ——— ————————————- ———— ————– ————– ————- ———-
01/19/23 KEN _SYSSMU8_1245875459$ 3 9735 ACTIVE 48,664 614,178
09:43:02
查看未提交的事务。该事务有多大？什么用户在处理该事务？随着时间的推移，其是否显示为未提交？这在预期之内吗？在此事务之前开始的任何长时间运行的查询、或在此事务之前使用闪回功能都必须创建此数据的旧“副本”。
3. 历史 UNDO 信息
示例 – undohistory.out
Run Time
—————–
05-Jul-2023 09:08
############## HISTORICAL DATA ##############
Max Concurrent
Last 7 Days
——————–
5
Max Concurrent
Since Startup
———————–
5
1555 Errors
—————
0
Undo Space Errors
————————-
0
############## CURRENT STATUS OF SEGMENTS ##############
############## SNAPSHOT IN TIME INFO ##############
##############(SHOWS CURRENT UNDO ACTIVITY)##############
Segment Name Active Bytes Unexpired Bytes Expired Bytes
———————————– ——————— ———————- ——————–
_SYSSMU10_1245875459$ 0 1,114,112 65,536
_SYSSMU1_1245875459$ 0 3,211,264 75,497,472
_SYSSMU2_1245875459$ 0 196,608 65,536
_SYSSMU3_1245875459$ 0 1,507,328 55,115,776
_SYSSMU4_1245875459$ 43,253,760 0 0
_SYSSMU5_1245875459$ 0 1,048,576 19,922,944
_SYSSMU6_1245875459$ 0 327,680 0
_SYSSMU7_1245875459$ 0 1,114,112 65,536
_SYSSMU8_1245875459$ 0 458,752 4,849,664
_SYSSMU9_1245875459$ 0 1,179,648 65,536
10 rows selected.
############## UNDO SPACE USAGE ##############
Segment# Shrinks Avg Shrink Size
—————– ————- ———————–
0 0 0
1 5 2,424,832
2 5 1,402,470
3 6 2,457,600
4 2 425,984
5 4 1,638,400
6 4 1,523,712
7 2 1,048,576
8 5 2,031,616
9 1 2,621,440
10 2 1,114,112
11 rows selected.
了解并发性信息。有多少并发性事务相互重叠？如果不断看到高并发的未提交事务，是否自动调整的 retention 正在正确处理工作负载?
对于当前未提交的工作，还可以检查运行时的段活动情况。
同时查看 UNDO 改动的信息。这些段的工作负载是否平衡？收缩是否均匀地分布在段中？是否有任何段承受的压力大于其他段？
示例 undostatistics.out
Run Time
—————–
05-09:08
############## Historical V$UNDOSTAT (Last 2 Days) ##############
Query
Maximum Undo # of Tuned Ret
Date/Time Minutes SqlID TBS Blocks Trans # of Unexpired # of Expired Minutes
————- ————- ——————– ———– ——— ———- ——————— —————- —————
03-09:15 14 0rc4km05kgzb9 14 39 160 312 25,024 29
03-09:25 4 0rc4km05kgzb9 14 36 220 312 25,024 43
03-09:35 14 0rc4km05kgzb9 14 327 200 8 25,024 43
03-09:45 4 0rc4km05kgzb9 14 20 202 464 24,896 29
. . .
05-08:37 1 0rc4km05kgzb9 14 22 195 80 25,344 15
05-08:47 12 0rc4km05kgzb9 14 35 216 48 25,376 15
05-08:57 2 0rc4km05kgzb9 14 33 183 56 25,368 15
284 rows selected.
############## RECENT MISSES FOR UNDO (Last 2 Days) ##############
no rows selected
no rows selected
############## AUTO-TUNING TUNE-DOWN DATA ##############
############## ROLLBACK DATA (Since Startup) ##############
Name Counters
————————————————————————————- ————
user rollbacks 4,959
transaction tables consistent reads – undo records applied 3
transaction tables consistent read rollbacks 0
data blocks consistent reads – undo records applied 300,730
rollbacks only – consistent read gets 11,384
cleanouts and rollbacks – consistent read gets 39
rollback changes – undo records applied 18,529
transaction rollbacks 190
total number of undo segments dropped 0
tune down retentions in space pressure 0
global undo segment hints helped 1
global undo segment hints were stale 0
local undo segment hints helped 0
local undo segment hints were stale 0
undo segment header was pinned 90,532
IMU CR rollbacks 6,183
SMON posted for undo segment recovery 0
SMON posted for undo segment shrink 0
18 rows selected.
############## Long Running Query History ##############
Date SQL ID Runaway SQL ID Space Issues
——————– ———————- —————————————– ————————————————
02-19:05 0rc4km05kgzb9 Max Tuned Down – Not Auto-Tuning
02-19:15 0rc4km05kgzb9 Reached Best Retention
02-19:25 0rc4km05kgzb9 Reached Best Retention
02-19:35 0rc4km05kgzb9 Reached Best Retention
02-19:45 0rc4km05kgzb9 Reached Best Retention
############## Details on Long Run Queries ##############
SQL ID SQL Text Last Load Elapsed Days
———————- ——————————————————————————— ————————– ——————
0rc4km05kgzb9 select 1 from obj$ where name=’DBA_QUEUE_SCHEDULES’ 2009-08-04/13:30:06 19
查看报告中在设定时间内收集的关于 undo 活动的数据（默认为 2 天）。
第二部分将显示在 V$UNDOSTAT 中的七天或在实例生命周期中，查询持续时间大于调整后的Retention时间的情况。
是否有大量的“调低”相关活动？“调低”是自动调整 AUM 的一种功能，将会收缩保留时间以减少 UNDO 空间压力。这可指向尚未引发 ORA-30036 错误的空间问题。
最后调查长时运行查询数据。这些可能是我们预期内的，但也有助于指出意外的查询活动。
4. 等待/锁定分析
示例 undopressure.out
Run Time
—————–
05-08:58
############## WAITS FOR UNDO (Since Startup) ##############
Cummalitve
Instance# Enq Total Requests Total Waits Successes Failures Time
————- —— ——————– —————- ———————— ————— ——————
1 HW 2,104 0 2,104 0 0
1 US 58 0 58 0 0
############## LOCKS FOR UNDO ##############
no rows selected
############## TUNED RETENTION HISTORY (Last 2 Days) ##############
############## LOWEST AND HIGHEST DATA ##############
END_TIME TUNED_UNDORETENTION
—————– ————————————–
05-08:58 900
05-08:57 900
05-08:37 900
05-07:17 900
05-04:17 900
05-03:57 900
05-03:37 900
05-02:57 900
05-02:37 900
05-02:17 900
05-01:17 900
11 rows selected.
END_TIME TUNED_UNDORETENTION
—————– ————————————-
04-17:57 2227
############## CURRENT TRANSACTIONS ##############
START_DATE START_SCN STATUS SQL Code
——————— —————— —————- —————————————-
05-08:58 53717782 ACTIVE update abc_tmp set edition_name=”
CURRENT_SCN
———————
53734654
############## WHO’S STEALING WHAT? (Last 2 Days) ##############
UnexStolen ExStolen UnexReuse ExReuse
————— ———— ————— ———–
0 22 0 0
0 12 0 0
查看等待和锁定信息。高等待和性能问题可能与已知的 UNDO 性能 bug 匹配。同时查看高、低调整后的Retention信息。在此报告中，您是否发现被盗 extent 的证据？未过期 extent 是否被盗？
5. 调查 LOB 问题
示例 lobdata.out
—————— ———————————————- ——————– ——————– ————-
CTEST DATA_OBJECT TB1 900
PAA_TEST RESPONDER_COMMENT TB1 900
EMP_O PICTURE USERS 10
EMP_O RESUME USERS 10
TEST COMMENTS TB1 900
5 rows selected.
如果定期更新 LOB 数据，LOB 对象上发生 ORA-1555 就可能是预期内的。PCTVersion 默认为 10%，如果持续对 LOB 数据进行了更改，那么这个此值通常需要调高很多。有时 100%（保留所有更改）还不足以适应工作负载。常规的 ORA-1555 诊断/分析对与 LOB 相关的 ORA-1555 错误是没有用的。LOB 产生的 UNDO 不是使用 UNDO 表空间中的 extent，而是保留在 LOB 表空间中。
