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...
Is this answer out of date? If it is, please let us know via a Comment