Skip to Main Content
  • Questions
  • ORA-01555 caused by SQL statement below

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Bolat.

Asked: November 21, 2016 - 6:28 am UTC

Last updated: November 21, 2016 - 10:46 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,
Sometimes we get ORA errors in alert file with message as:

....
ORA-01555 caused by SQL statement below (SQL ID: gc8312bb07hav, Query Duration=11848 sec, SCN: 0x0000.35f6a778):
WITH CORETABLE(
DATAID,
MAJOR,
MINOR)
AS (
SELECT
Q1.DATAID,
Q1.MAJOR, --max(Q1.MAJOR),
Q1.MINOR --max(Q1.MINOR)
FROM (
SELECT T1.DATAID, T1.MAJOR, T1.MINOR FROM NCOC_ATTR_COMPANYDOCUMENTS T1
WHERE T1.DATAID in (:"SYS_B_0000",:"SYS_B_0001",:"SYS_B_0002",:"SYS_B_0003",:"SYS_B_0004",:"SYS_B_0005",:"SYS_B_0006",:"SYS_B_0007",:"SYS_B_0008",:"SYS_B_0009",:"SYS_B_0010",:"SYS_B_0011",:"SYS_B_0012",:"SYS_B_0013",:"SYS_B_0014",:"SYS_B_0015",:"SYS_B_0016",:"SYS_B_0017",:"SYS_B_0018",:"SYS_B_0019",
....
ORA-01555 caused by SQL statement below (SQL ID: axs5tkqdcpt7t, Query Duration=23674 sec, SCN: 0x0000.35f277ab):
WITH CORETABLE(
DATAID,
MAJOR,
MINOR)
AS (
SELECT
Q1.DATAID,
Q1.MAJOR, --max(Q1.MAJOR),
Q1.MINOR --max(Q1.MINOR)
FROM (
SELECT T1.DATAID, T1.MAJOR, T1.MINOR FROM NCOC_ATTR_COMPANYDOCUMENTS T1
LEFT JOIN DTREE TR ON TR.DATAID = T1.DATAID
WHERE abs(TR.OWNERID) = :"SYS_B_000" and T1.MAJOR = :"SYS_B_001" and T1.MINOR = :"SYS_B_002"
UNION
SELECT T2.DATAID, T2.MAJOR, T2.MINOR FROM NCOC_ATTR_ENGINEERINGDOCUMENTS T2
LEFT JOIN DTREE TR ON TR.DATAID = T2.DATAID
WHERE abs(TR.OWNERID)
....

I can not understand the origin of such errors.
We extended REDO groups from 4 to 5 (each with clone) & extended each REDO size from 250 MB to 350M.
Also I checked Log Switch Frequency Map in TOAD - for last 2 days, number of switches is not higher than 4.
UNDO_RETENTION=2900 in our database. I read in some blog - it was recommended to set UNDO_RETENTION parameter to value of the longest SQL query.

With regards!


and Chris said...

Redo is a separate concept. Adding redo groups won't help you here. You need more undo space. Oracle Database uses undo to get a read consistent view of your data at the time a query started.

Your queries run for 11848s and 23674s. But your undo retention is just 2,900 seconds. An order of magnitude too small!

So Oracle has overwritten information it needs in the undo tablespace to reconstruct how the data looked at the time the query started.

To resolve this, look into:

- Tuning your SQL statements so they run faster
- Increasing your undo retention.

http://docs.oracle.com/database/121/ADMIN/undo.htm#ADMIN-GUID-28EA4133-BC73-4036-87F2-656FC1A6D9A2

For further troubleshooting advice, see:

https://blogs.oracle.com/db/entry/troubleshooting_ora_1555

Is this answer out of date? If it is, please let us know via a Comment