AUM Common Analysis/Diagnostic Scripts (Doc ID 877613.1)

rongshiyuan發表於2014-11-20

In this Document

Purpose

Requirements

Configuring

Instructions

Sample Code

Sample Output

References

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.5 to 11.2.0.4 [Release 10.1 to 11.2]
Information in this document applies to any platform.

Purpose

This article is intended to standardize the use of scripts to diagnose and analyze ORA-1555 errors.. This article is intended for all Database Administrators and Oracle Support Analysts

Requirements

The scripts below can be run in SQL*Plus or iSQL*Plus. Many of the scripts require DBA privileges in the database.

Click here [modified 5-12-2011] to download the scripts discussed in this article.

Configuring

See the remarks with each script to determine if there are changes indicated for specific configuration / application environments.

Instructions

For the scripts below, it is important to use an administrative database user account that is able to access DBA* and V$ tables. By default, these scripts log in like

$ sqlplus /nolog
connect / as sysdba

These scripts should not impact performance on the database and can be as often as you like.

Caution

This sample code is provided for educational purposes only, and is not supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Sample Code

The scripts used to investigate an ORA-1555 error can differ from Oracle release to release. These scripts are focused on those environments configured for Automatic UNDO Management (AUM).

The script files are provided as attachments to this note. NOTE: There can be formatting issues with these scripts when doing a cut and paste from this document. Therefore, the scripts are attached to the document and can be downloaded for use. This article will discuss the benefits/uses and provide a sample output for each script.

General Analysis of the AUM configuration and ORA-1555

1. Configuration:

UndoDatafiles.sql -- spools to file undodatafiles.out at the default directory location.

UndoParameters.sql -- spools to undoparameters.out at the default directory location.

UndoUsage.sql -- spools to undousage.out at the default directory location.

2. Current Uncommitted Transactions:

CurrentActivity.sql -- spools to file undoactivity.out at the default directory location.

3. Historical UNDO Information:

UndoHistoryInfo.sql -- spools to undohistory.out at the default directory location.

UndoStatistics.sql -- spools to undostatistics.out at the default directory location. You can modify this report to show the proper time frame for analysis. By default V$UNDOSTAT data is looked at for the last two days. Seven days of data is maintained over time in the V$UNDOSTAT view.

4. Waits / Locks Analysis:

UndoPressure.sql -- spools to undopressure.out at the default directory location.

5. Investigate LOBs Issues:

LobData.sql -- spools to lobdata.out at the default directory location.

Sample Output

1. Configuration

Sample 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

Review the data on configuration. Is AUTOEXTEND on? If the UNDO tablespace is configured to grow automatically with demand for space, this can influence the database to not reuse expired Undo extents beyond Retention settings to decrease chances of ORA-1555. Instead the tablespace will continue to grow with new demand.

Sample 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

Review parameter settings that are affecting Undo Retention rules.

'_smu_debug_mode'=33554432 forces the auto-tuner to use the workload query duration expectations to "throttle back" automatic tuning up of the Retention. In some cases, tuned retention can grow to very long periods of time and space pressure can be a significant problem in the Undo tablespace.

'_undo_autotune'=false is a workaround for some AUM bugs, but this has big impacts on analysis. No further data is tracked in V$UNDOSTAT and the explicit setting for UNDO_RETENTION is key to handling retention of undo.

Sample 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

2. Current Uncommitted Transactions

Sample 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

Review uncommitted transactions. How big is the transaction? What user is processing the transaction? Does this show up as uncommitted over time? Is that expected? Any long running query started prior to this transaction or using Flashback functionality to a time prior to this transaction will have to build old "copies" of this data.

3. Historical UNDO Information

Sample - 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.

Study Concurrency information. How many concurrent transactions are overlapping? If you consistently see high concurrent uncommitted transactions, is auto-tuned Retention handling the workload appropriately.

You can also see the segments activity at the run time for current uncommitted work.

Also review the UNDO change information. Are the segments balancing the workload? Are shrinks evenly distributed across segments? Any segments appearing to be under more pressure than others?

Sample 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

Review data gathered on undo activity over the time frame in the report (by default 2 days).

The second section will show cases where Query Duration was greater than Tuned Retention over the seven days in V$UNDOSTAT or over the life of the instance.

Is there a lot of "Tune Down" related activity? "Tune Down" is a feature of auto-tuning AUM that will shrink retention times in response to space pressure problems for UNDO. This can point to space issues that have not caused ORA-30036 errors yet.

Lastly, investigate Long-Running Query data. These may be expected, but may help point to unexpected query activity.

4. Waits / Locks Analysis

Sample 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

Review waits and locks information. High waits and performance issue may match up to known UNDO performance bugs. Also review low and high Tuned Retention information. Are you seeing evidence in this report of stolen extents? Are unexpired extents being stolen?

5. Investigate LOBs Issues

Sample 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.

LOBs and ORA-1555 are expected if the LOB data is being updated regularly. PCTVersion is by default 10% and if you do any changes to the LOB data over time, this will usually have to be much higher. Sometimes 100% (where all changes are retained) is not enough to accommodate workload. Normal diagnostics / analysis for ORA-1555 is not useful with LOB related ORA-1555 errors. The UNDO is not allocated to extents in the UNDO tablespace, but is maintained in the LOB tablespace.