系统层级、经验闲谈 AUM 常用分析/诊断脚本 (文档 ID 1526122.1) 2019年6月12日 小潘 留下评论 AUM 常用分析/诊断脚本 (文档 ID 1526122.1) 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 Support不提供技术支持。它已经过内部测试，然而我们无法确保它在任何环境中都能成功使用。请您在使用之前先在测试环境中运行。 示例代码 Oracle 发布的用于调查 ORA-1555 错误的脚本每一版本都有所不同。这些脚本只适用于自动 UNDO 管理 (AUM) 配置的环境。 脚本文件以本文档附件的形式提供。注意：在本文档中执行剪切粘贴操作时，这些脚本可能出现格式问题。因此，脚本都附在文档中，可下载使用。本文将讨论每个脚本的优势/作用，并提供示例输出。 AUM 配置和 ORA-1555 全面分析 配置： UndoDatafiles.sql — spool 输出到位于默认目录位置的文件 undodatafiles.out 中。 UndoParameters.sql — spool 输出到位于默认目录位置的文件 undoparameters.out 中。 UndoUsage.sql — spool 输出到位于默认目录位置的文件 undousage.out 中。 当前未提交的事务： CurrentActivity.sql — spool 输出到位于默认目录位置的文件 undoactivity.out 中。 历史 UNDO 信息： UndoHistoryInfo.sql — spool 输出到位于默认目录位置的 undohistory.out 中。 UndoStatistics.sql — spool 输出到位于默认目录位置的 undostatistics.out 中。您可修改此报告以显示适当的分析时间范围。默认情况下，查看最后两天的 V$UNDOSTAT 数据。在 V$UNDOSTAT 视图中，数据会保留七天。 等待/锁定分析： UndoPressure.sql — spool 输出到位于默认目录位置的 undopressure.out 中。 调查 LOB 问题： LobData.sql — spool 输出到位于默认目录位置的 lobdata.out 中。 示例输出 配置 示例 undodatafiles.out ############## RUNTIME ############## 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 ############## RUNTIME ##############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 ############## RUNTIME ############## 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 当前未提交的事务 示例 undoactivity.out ############## RUNTIME ############## 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 查看未提交的事务。该事务有多大？什么用户在处理该事务？随着时间的推移，其是否显示为未提交？这在预期之内吗？在此事务之前开始的任何长时间运行的查询、或在此事务之前使用闪回功能都必须创建此数据的旧“副本”。 历史 UNDO 信息 示例 – undohistory.out ############## RUNTIME ############## 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 ############## RUNTIME ############## 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 错误的空间问题。 最后调查长时运行查询数据。这些可能是我们预期内的，但也有助于指出意外的查询活动。 等待/锁定分析 示例 undopressure.out ############## RUNTIME ############## 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 是否被盗？ 调查 LOB 问题 示例 lobdata.out Table Column Tablespace PCTVersion % Retention —————— ———————————————- ——————– ——————– ————- 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 表空间中。 其他参考： https://wenku.baidu.com/view/47b01e6ab84ae45c3b358ca8.html https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams222.htm#REFRN10225 https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=341463354078393&parent=DOCUMENT&sourceId=1307334.1&id=1555.1&_afrWindowMode=0&_adf.ctrl-state=wgqt71fpl_126 https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=341423607535564&id=1307334.1&_afrWindowMode=0&_adf.ctrl-state=wgqt71fpl_77 Oracle 标准服务 – 数据库技术支持通讯 – 2015年11月版，57期 (文档 ID 2088997.1) https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=wgqt71fpl_299&_afrLoop=344990618186201#title1