Re: ora-1555

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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Other related posts: