automatic undo mysteries

Hi,

Please look at the alert log error I got

It shows ORA-01555 but the query duration is 0 seconds. How is that possible that query goe the error just after starting

this is Oracle 9.2.0.5 running with automatic undo with undo_retention = 36000

ORA-01555 caused by SQL statement below (Query Duration=0 sec, SCN: 0x000c.46f0197a):
Sat Nov 26 01:01:32 2005
SELECT
"EFFECTIVE_START_DATE","EFFECTIVE_END_DATE","POSITION_ID","ASSIGNMENT_STATUS_TYPE_ID","PERSON_ID","ORGANIZATION_ID","ASS_ATTRIBUTE9","A SS_ATTRIBUTE16" FROM "HR"."PER_ALL_ASSIGNMENTS_F" "AL1" WHERE "EFFECTIVE_END_DATE">=:1 AND "EFFECTIVE_START_DATE"<=:2 AND "ASS_ATTRIBUTE9" LIK
E 'CRN%' AND
(TO_DATE(SUBSTR("ASS_ATTRIBUTE16",1,10),'YYYY/MM/DD')>TO_DATE('16-FEB-'||TO_CHAR(:3,'YYYY'),'DD-MON-YYYY') AND TO_DATE(SUBSTR("AS
S_ATTRIBUTE16",1,10),'YYYY/MM/DD')<=TO_DATE('31-MAY-'||TO_CHAR(:4,'YYYY'),'DD-MON-YYYY') OR TO_DATE(SUBSTR("ASS_ATTRIBUTE16",1,10),'YYYY/MM/DD
')>TO_DATE('01-OCT-'||TO_CHAR(:5,'YYYY'),'DD-MON-YYYY') AND
TO_DATE(SUBSTR("ASS_ATTRIBUTE16",1,10),'YYYY/MM/DD')<=TO_DATE('15-JAN-'||TO_CHAR(T O_NUMBER(TO_CHAR(:6,'YYYY'))+1),'DD-MON-YYYY')) Sat Nov 26 01:31:30 2005

I also checked stats$undostat that shows ssolderrcnt as 1 for the time interval

but it shows 0 for both for all others block resue and steal counts.

So why is the query getting snapshot tool old.

Could any out guys could clarify my confustion

thanks
amit Received on Tue Nov 29 2005 - 14:30:39 CST