ora-1555

Hi List,

Our prod almost has ora-1555 error everyday, the query just run 2 sconds and get abort. According to the Maxquerylenth is very low. and our retention parameter should be cover it enough, any ideas? Just this server had problem, no other db had problem.

ORACLE:HRPROD> SELECT

   2        to_char(min(begin_time),'MM/DD/YYYY HH24:MI:SS') "Begin Time",
   3        to_char(max(end_time),'MM/DD/YYYY HH24:MI:SS') "End Time",
   4        (max(end_time)-min(begin_time))*24*60*60 "Seconds",
   5        sum(undoblks) "UndoBlks",
   6        sum(undoblks)/((max(end_time)-min(begin_time))*24*60*60) 


"UndoBlksPerSec",

   7        max(maxquerylen) "MaxQueryLenSecs"
   8   FROM
   9          v$undostat;

Begin Time          End Time               Seconds   UndoBlks 

------------------- ------------------- ---------- ---------- 
-------------- ---------------
07/11/2008 04:20:26 07/11/2008 15:34:15      40429      18993 
.469786539            1010

ORACLE:HRPROD> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- 
------------------------------
undo_management                      string      AUTO
undo_retention                       integer     5000
undo_tablespace                      string      UNDOTBS1

alert_HRPROD.log" 130 lines, 6093 characters

   Current log# 3 seq# 6895 mem# 0: /HRPROD8/redo1/redo03.dbf    Current log# 3 seq# 6895 mem# 1: /HRPROD8/redo2/redo03.dbf Fri Jul 11 15:09:37 2008
ORA-01555 caused by SQL statement below (SQL ID: 3mqwuax1tukfq, Query Duration=2 sec, SCN: 0x0003.b85c7909):
Fri Jul 11 15:09:37 2008
SELECT
"A8"."EMPLID","A8"."EMPL_RCD","A7"."EFFDT","A3"."TFTH_TIMEK_LOC","A7"."LOCATION","A7"."DEPTID","A7"."JOBCODE","A7"."FULL_PA
RT_TIME","A7"."EMPL_CLASS","A7"."REG_TEMP","A7"."EMPL_STATUS","A3"."TFTH_RPT_FTE","A7"."EMPL_TYPE","A7"."PAYGROUP","A3"."TFTH_ANN_ SAL","A6"."DESCR","A5"."DESCR","A4"."DESCR","A7"."REPORTS_TO","A2"."TFTH_UTLN","A1"."EMAIL_ADDR" FROM (SELECT MAX("A13"."EFFSEQ")
  "VW_COL_1","A13"."EMPLID" "EMPLID","A13"."EMPL_RCD"
"EMPL_RCD","A13"."EFFDT" "EFFDT" FROM "SYSADM"."PS_JOB" "A13" GROUP BY
"A13".
"EMPLID","A13"."EMPL_RCD","A13"."EFFDT") "A9","SYSADM"."PS_EMPLOYMENT"
"A8","SYSADM"."PS_JOB" "A7","SYSADM"."PS_JOBCODE_TBL" "A6",
"SYSADM"."PS_TFTH_TIME_KEEP" "A5","SYSADM"."PS_PAYGROUP_TBL"
"A4","SYSADM"."PS_TFTH_JOB" "A3","SYSADM"."PS_TFTH_UTLN" "A2","SYSADM
"."PS_EMAIL_ADDRESSES" "A1" WHERE "A8"."EMPLID"="A7"."EMPLID" AND
"A2"."EMPLID"(+)="A7"."EMPLID" AND "A1"."EMPLID"(+)="A7"."EMPLID
" AND "A1"."E_ADDR_TYPE"(+)='BUSN' AND "A8"."EMPL_RCD"="A7"."EMPL_RCD"
AND "A8"."EMPLID"="A3"."EMPLID" AND "A8"."E ~
Thanks,

Joan

--
http://www.freelists.org/webpage/oracle-l