Common Diagnostic Scripts for AUM problems (Doc ID 746173.1)

In this Document
  Purpose
  Software Requirements/Prerequisites
  Configuring the Sample Code
  Running the Sample Code
  Caution
  Sample Code
  Sample Code Output
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.8 - Release: 9.2 to 11.1
Oracle Server - Personal Edition - Version: 9.2.0.1 to 11.1.0.8 [Release: 9.2 to 11.1]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 9.2.0.1 to 11.1.0.8

Purpose

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

The scripts below can

be run in SQL*Plus or iSQL*Plus. Some of the scripts require DBA privileges in the database.

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

NOTE: This article is NOT intended as a training guide on analysis of AUM issues. The scripts can provide valuable information in diagnosing problems with UNDO management and are often requested in working with support to investigate ORA-1555 or other UNDO related error scenarios.

Software Requirements/Prerequisites

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

Configuring the Sample Code

Script information below will indicate if SYSDBA privileges are needed to access the data.

Running the Sample Code

There are often formatting problems doing cut and pastes from an SR. This note includes the common scripts in a .zip file to be downloaded for analysis and diagnostics.

Caution

This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.

Sample Code

1. Finding UNDO configuration information

UndoParameters.sql - reports parameter settings related to UNDO configuration. This scripts access information on hidden parameters and will require SYSDBA privileges.

UndoConfig.sql - focuses on information about the UNDO tablespace and how extents are being used currently

2. General performance information

UndoHealthcheck.sql - overview information on how UNDO/Rollback Segments are working. Lists waits, contention, and some generic configuration information

3. UNDO analysis

UndoExtents.sql - reports overview and detailed information about UNDO extents

CurrentActivity.sql - reports current transactions information

Sample Code Output

Sample output for each of the queries above is shown.

UndoParameters.sql - spools to a file undo_param.out

Database Started Last
----------------------------------------
29-Oct-2008 09:25:15

Setting
-------------------------------------------------------
Undo Management:  AUTO
Undo Retention  Seconds:  100  Hours: .03  Days: 0
Undo Tablespace:  UNDOTBS2

Parameter                 Session Value             Instance Value
------------------------- ------------------------- -------------------------
_undo_autotune            TRUE                      TRUE

UndoConfig.sql - spools to a file undoconfig.out

TS Name                         Total Bytes     Free Bytes      Ext
------------------------------  --------------  --------------  ----
EXAMPLE                            104,857,600      23,724,032     3
ISIS_TABSPACE                      104,857,600     104,792,064     1
SYSAUX                           1,223,294,976      58,261,504    20
SYSTEM                             775,946,240      29,687,808     2
UNDOTBS2                            41,943,040      12,517,376    21
USERS                              292,421,632     247,791,616   106
                                --------------  --------------
sum                              2,543,321,088     476,774,400

6 rows selected.

                                                        Extent    Auto     Retention
Tablespace         TB Status    DF Status  Management   Type      Segment  Level        Auto?   Max Allowed

USERS              ONLINE       AVAILABLE  LOCAL        SYSTEM    AUTO     NOT APPLY    YES          32,768
SYSAUX             ONLINE       AVAILABLE  LOCAL        SYSTEM    AUTO     NOT APPLY    YES          32,768
SYSTEM             ONLINE       AVAILABLE  LOCAL        SYSTEM    MANUAL   NOT APPLY    YES          32,768
EXAMPLE            ONLINE       AVAILABLE  LOCAL        SYSTEM    AUTO     NOT APPLY    YES          32,768
ISIS_TABSPACE      ONLINE       AVAILABLE  LOCAL        SYSTEM    MANUAL   NOT APPLY    NO                0
UNDOTBS2           ONLINE       AVAILABLE  LOCAL        SYSTEM    MANUAL   NOGUARANTEE  NO                0

6 rows selected.

Status                   How Many?
--------------------  ------------
ONLINE                          11

UndoHealthcheck.sql - spools to a file undohealth.out

ROLLBACK HIT STATISTICS:

DataFile Name                       WAITS       GETS
------------------------------ ---------- ----------
SYSTEM                                  0        455
_SYSSMU11_1214418413$                   1      7,230
_SYSSMU12_1214418413$                   3      8,704
_SYSSMU13_1214418413$                   1     15,244
_SYSSMU14_1214418413$                   3      7,531
_SYSSMU15_1214418413$                   5     14,324
_SYSSMU16_1214418413$                   1      8,930
_SYSSMU17_1214418413$                   0      7,317
_SYSSMU18_1214418413$                   1     21,010
_SYSSMU19_1214418413$                   2      8,984
_SYSSMU20_1214418413$                   1      7,252

11 rows selected.

----------------------------------------------------------------------
The average of waits/gets is .02%

REDO CONTENTION STATISTICS:

Class Type         Times Waited
------------------ ------------
system undo header            0
system undo block             0
undo header                  19
undo block                    0

Total requests = 95

Contention for system undo header = 0%

Contention for system undo block = 0%

Contention for undo header = 20%

Contention for undo block = 0%

redo log space requests = 2794

LATCH CONTENTION:

DataFile Name       GETS   MISSES IMMED GETS IMMED MISS  SLEEPS
--------------- -------- -------- ---------- ---------- -------
redo copy            135        0    3533636        293       0
redo allocation   186763       92    3533607         93       7

Ratio of MISSES to GETS: .05%

Ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS: .01%

REPORTING I/O STATISTICS:

                                                         Physical  Physical      Read     Write
DataFile Name                                            Blk Read Blks Wrtn      Time      Time

D:\APP\KHROBINS\ORADATA\SNEEZY\EXAMPLE01.DBF                 1277        17       998         1
D:\APP\KHROBINS\ORADATA\SNEEZY\ISIS.DBF                        22        17       116         1
D:\APP\KHROBINS\ORADATA\SNEEZY\SYSAUX01.DBF                 52872     28714     44943     20941
D:\APP\KHROBINS\ORADATA\SNEEZY\SYSTEM01.DBF                 54686      3865     55427      2287
D:\APP\KHROBINS\ORADATA\SNEEZY\UNDOTBS02.DBF                11918     40030      2391     17676
D:\APP\KHROBINS\ORADATA\SNEEZY\USERS01.DBF                  26011     24967      9587     13030

6 rows selected.

GENERATING WAIT STATISTICS:

Class Type         Times Waited Total Times
------------------ ------------ -----------
data block                   75         149
segment header                1           0
undo header                  19           3

ROLLBACK EXTENT STATISTICS:

                    Size in      Optimal  High Water  Num of Num of   Num of   Average size
Undo # Extents        Bytes         Size        Mark Shrinks  Wraps  Extends Active Extents

     0       6      385,024                  385,024       0      0        0              0
    11      18    3,137,536                3,137,536      12     36       30        269,992
    12       4    1,236,992                2,285,568      16     39       25        151,540
    13       7    2,416,640               40,689,664      26    183      163        500,199
    14      18    3,137,536                3,137,536      12     53       43        260,203
    15      18    3,137,536               39,641,088      34    222      212      1,858,287
    16       5    2,285,568                5,234,688      18     41       33        226,922
    17      18    3,137,536                3,137,536      22     41       36        218,288
    18      19    4,186,112               40,689,664      28    308      167      1,240,319
    19       8    3,465,216                4,644,864      22     51       41        237,800
    20      19    4,186,112                4,186,112       9     27       23        212,098

11 rows selected.

UndoExtents.sql - spools to a file undoext.out

                                     Rollback Segments Breakdown

Status                   How Many?
--------------------  ------------
ONLINE                          11

                                     Undo Extents

                          Active        Unxpired          Expired
Name                      Extents       Extents           Extents
_SYSSMU14_1214418413$        0         2,818,048          327,680
_SYSSMU19_1214418413$        0         3,473,408                0
_SYSSMU15_1214418413$        0         3,145,728                0
...

                                     Undo Extents Statistics

Status        How Many?              Size
---------  ------------  ----------------
UNEXPIRED           126        28,901,376
EXPIRED               8         1,507,328

                                     Undo Extents Statistics

Segment               Tablespace ID FILE_ID BLOCK_ID BYTES    BLOCKS STATUS

_SYSSMU11_1214418413$ UNDOTBS2   0    7           9   65,536      8  UNEXPIRED
_SYSSMU11_1214418413$ UNDOTBS2   1    7        1769   65,536      8  UNEXPIRED
...

                 Undo Extents Error Conditions (Default - Last 4 Hours)

                        # Unexp    # Exp     1555    Space Query        Tuned
INST_ID  BEGIN_TIME     Stolen    Reused    Error   Error  Length Undo Retention (HRs)

       1 10/30/2008 04:55    0         0        0       0      860        8,962      2
...

CurrentActivity.sql - spools to a file undoactivity.out

Started User Undo Segment Name File # Block # Status KBytes Rows

10/30/08 KEN _SYSSMU13_1214418413$ 7 401 ACTIVE 24 152
...

References

Document Details

Related Products

Information Centers

Document References

How to gather info about uncommitted transactions [786472.1]

Attachments

Various Undo diagnostics scripts(12.09 KB)