Re: ora-1555
- From: "Andrew Kerber" <andrew.kerber@xxxxxxxxx>
- To: joan.hsieh@xxxxxxxxx
- Date: Fri, 11 Jul 2008 15:34:07 -0500
Is this run as part of a longer transaction?
On Fri, Jul 11, 2008 at 2:47 PM, Joan Hsieh <joan.hsieh@xxxxxxxxx> wrote:
> 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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
Other related posts:
