In this Document

Applies to:

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

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

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

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

2. Current Uncommitted Transactions

Sample undoactivity.out

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

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

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

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

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. 

References