Wrongly calculated MAXQUERYLEN Causing ORA-01555 or ORA-30036 Errors (Doc ID 2005931.1)

Last updated on SEPTEMBER 21, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 06-Aug-2016***

Symptoms

ORA-01555  is caused by a query which runs very fast and the actual time taked to run it is less than undo_retention 

Example:

Alert log file shows:

undo_retention=1800 seconds

..

ORA-01555 caused by SQL statement below (SQL ID: 31qqrj8juuzgr, Query Duration=4115 sec, SCN: 0x0010.0777a724)==Query Duration=4115 sec

Trace file shows:

SSOLD: SQL ID: 31qqrj8juuzgr, Statement:
MAXQUERYLEN  = 4190 MAXQUERYID     = 31qqrj8juuzgr
MAXQUERYLEN  = 2986 MAXQUERYID     = 31qqrj8juuzgr
MAXQUERYLEN  = 2384 MAXQUERYID     = 31qqrj8juuzgr
MAXQUERYLEN  = 3578 MAXQUERYID     = 31qqrj8juuzgr
MAXQUERYLEN  = 2977 MAXQUERYID     = 31qqrj8juuzgr

 

Performance SQL monitoring software (Precise i3) shows elapsed time 80 seconds


Global Information
------------------------------
SQL ID              :  31qqrj8juuzgr


Global Stats
===========================================================================
| Elapsed |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
===========================================================================
|      80 |      73 |     0.04 |     7.24 |     1 |    150 | 8168 |   8GB |
===========================================================================

 

 

 

 

 

 

 

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms