适用于:
Oracle Database - Enterprise Edition - 版本 10.1.0.5 到 11.1.0.6 [发行版 10.1 到 11.1]本文档所含信息适用于所有平台
用途
本文旨在规范对用以诊断和分析 ORA-1555 错误的脚本的使用。本文适用于所有数据库管理员及 Oracle Support 分析人员。
要求
以下脚本可在 SQL*Plus 或 iSQL*Plus 中运行。很多脚本都要求拥有数据库的 DBA 权限。
单击 此处 [修订时间：2011 年 5 月 12 日] 下载本文中讨论的脚本。
配置
查看每个脚本的备注以判定对于特定配置/应用程序环境是否提示有所更改。
说明
对于以下脚本，需要使用能够访问 DBA* 和 V$ 表的数据库管理用户。默认情况下，这些脚本登录形式为：
$ sqlplus /nolog
connect / as sysdba
这些脚本应该不会影响数据库性能，可以多次运行。
警告
示例代码
Oracle 发布的用于调查 ORA-1555 错误的脚本每一版本都有所不同。这些脚本只适用于自动 UNDO 管理 (AUM) 配置的环境。
脚本文件以本文档附件的形式提供。注意：在本文档中执行剪切粘贴操作时，这些脚本可能出现格式问题。因此，脚本都附在文档中，可下载使用。本文将讨论每个脚本的优势/作用，并提供示例输出。
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-Aug-2009 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-Aug-2009 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-Aug-2009 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-Aug-2009 09:43
############## Current Uncommitted Transactions ##############
Started User Undo Segment Name File # Block # Status KBytes Rows
------------ --------- ------------------------------------- ------------ -------------- -------------- ------------- ----------
08/19/09 KEN _SYSSMU8_1245875459$ 3 9735 ACTIVE 48,664 614,178
09:43:02
查看未提交的事务。该事务有多大？什么用户在处理该事务？随着时间的推移，其是否显示为未提交？这在预期之内吗？在此事务之前开始的任何长时间运行的查询、或在此事务之前使用闪回功能都必须创建此数据的旧“副本”。
3. 历史 UNDO 信息
示例 - undohistory.out
Run Time
-----------------
05-Aug-2009 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 表空间中。
Scripts.zip