ora-1555

  • From: Joan Hsieh <joan.hsieh@xxxxxxxxx>
  • To: oracle_l <ORACLE-L@xxxxxxxxxxxxx>
  • Date: Fri, 11 Jul 2008 15:47:48 -0400

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 UndoBlksPerSec MaxQueryLenSecs ------------------- ------------------- ---------- ---------- -------------- --------------- 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


Other related posts: