OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » ORA-01555

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01555 [message #420143]

Thu, 27 August 2009 04:19

saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE

Member

Hi Experts,

I am facing a problem with deciding on the UNDO_RETENTION parameter for the Database.

Currently

UNDO_TABLESPACE = 15 (GB)
UNDO_RETENTION  = 27000 (sec)

Below are the snapshots from the alert log, since the time i got the error ORA-01555
Please note that the Query Duration is changing for the same update statement is changing and most of the time it is increasing.
Also from 25th the only change made is that the below where clause has been added. It was done to reduce the number of records manipulated by this update statement.

PTLS.ACTUALHOURS > 0

Wed Aug 19 06:49:53 2009
ORA-01555 caused by SQL statement below (Query Duration=11272 sec, SCN: 0x0697.385d4b3d):
Wed Aug 19 06:49:53 2009
UPDATE DSL_ITEMEFFORTDATA IED SET (ACTUALEFFORT) = (SELECT SUM(PTLS.ACTUALHOURS) FROM KP_PMTL_VIEW PTLS , TIMESHEET TS WHERE PTLS.TIMESHEETID = TS.TIMESHEETID AND PTLS.ITEMTYPE = 'Tsk' AND PTLS.ITEMID = IED.ITEMID AND PTLS.ITEMTYPE = IED.ITEMTYPE AND TS.USERID = IED.USERID AND PTLS.PROJECTID = IED.OWNERID AND (PTLS.PROJECTID = :B1 OR :B1 = -1) GROUP BY PTLS.ITEMTYPE , PTLS.ITEMID,TS.USERID) WHERE ITEMTYPE = 'Tsk' AND OWNERTYPE = 'Prj' AND (OWNERID = :B1 OR :B1 = -1)

Thu Aug 20 06:54:33 2009
ORA-01555 caused by SQL statement below (Query Duration=11065 sec, SCN: 0x0697.389909c2):
Thu Aug 20 06:54:33 2009
UPDATE DSL_ITEMEFFORTDATA IED SET (ACTUALEFFORT) = (SELECT SUM(PTLS.ACTUALHOURS) FROM KP_PMTL_VIEW PTLS , TIMESHEET TS WHERE PTLS.TIMESHEETID = TS.TIMESHEETID AND PTLS.ITEMTYPE = 'Tsk' AND PTLS.ITEMID = IED.ITEMID AND PTLS.ITEMTYPE = IED.ITEMTYPE AND TS.USERID = IED.USERID AND PTLS.PROJECTID = IED.OWNERID AND (PTLS.PROJECTID = :B1 OR :B1 = -1) GROUP BY PTLS.ITEMTYPE , PTLS.ITEMID,TS.USERID) WHERE ITEMTYPE = 'Tsk' AND OWNERTYPE = 'Prj' AND (OWNERID = :B1 OR :B1 = -1)

Tue Aug 25 06:01:52 2009
ORA-01555 caused by SQL statement below (Query Duration=9135 sec, SCN: 0x0697.398023de):
Tue Aug 25 06:01:52 2009
UPDATE DSL_ITEMEFFORTDATA IED SET (ACTUALEFFORT) = (SELECT SUM(PTLS.ACTUALHOURS) FROM KP_PMTL_VIEW PTLS , TIMESHEET TS WHERE PTLS.TIMESHEETID = TS.TIMESHEETID AND PTLS.ACTUALHOURS > 0 AND PTLS.ITEMTYPE = 'Tsk' AND PTLS.ITEMID = IED.ITEMID AND PTLS.ITEMTYPE = IED.ITEMTYPE AND TS.USERID = IED.USERID AND PTLS.PROJECTID = IED.OWNERID AND (PTLS.PROJECTID = :B1 OR :B1 = -1) GROUP BY PTLS.ITEMTYPE , PTLS.ITEMID,TS.USERID) WHERE ITEMTYPE = 'Tsk' AND OWNERTYPE = 'Prj' AND (OWNERID = :B1 OR :B1 = -1)

Wed Aug 26 10:28:34 2009
ORA-01555 caused by SQL statement below (Query Duration=25028 sec, SCN: 0x0697.399c991b):
Wed Aug 26 10:28:34 2009
UPDATE DSL_ITEMEFFORTDATA IED SET (ACTUALEFFORT) = (SELECT SUM(PTLS.ACTUALHOURS) FROM KP_PMTL_VIEW PTLS , TIMESHEET TS WHERE PTLS.TIMESHEETID = TS.TIMESHEETID AND PTLS.ACTUALHOURS > 0 AND PTLS.ITEMTYPE = 'Tsk' AND PTLS.ITEMID = IED.ITEMID AND PTLS.ITEMTYPE = IED.ITEMTYPE AND TS.USERID = IED.USERID AND PTLS.PROJECTID = IED.OWNERID AND (PTLS.PROJECTID = :B1 OR :B1 = -1) GROUP BY PTLS.ITEMTYPE , PTLS.ITEMID,TS.USERID) WHERE ITEMTYPE = 'Tsk' AND OWNERTYPE = 'Prj' AND (OWNERID = :B1 OR :B1 = -1)

Thu Aug 27 13:01:21 2009
ORA-01555 caused by SQL statement below (Query Duration=34636 sec, SCN: 0x0697.3ebaa271):
Thu Aug 27 13:01:21 2009
UPDATE DSL_ITEMEFFORTDATA IED SET (ACTUALEFFORT) = (SELECT SUM(PTLS.ACTUALHOURS) FROM KP_PMTL_VIEW PTLS , TIMESHEET TS WHERE PTLS.TIMESHEETID = TS.TIMESHEETID AND PTLS.ACTUALHOURS > 0 AND PTLS.ITEMTYPE = 'Tsk' AND PTLS.ITEMID = IED.ITEMID AND PTLS.ITEMTYPE = IED.ITEMTYPE AND TS.USERID = IED.USERID AND PTLS.PROJECTID = IED.OWNERID AND (PTLS.PROJECTID = :B1 OR :B1 = -1) GROUP BY PTLS.ITEMTYPE , PTLS.ITEMID,TS.USERID) WHERE ITEMTYPE = 'Tsk' AND OWNERTYPE = 'Prj' AND (OWNERID = :B1 OR :B1 = -1)

This way we are not able to decide the UNDO_RETENTION for the database..

Please suggest...

Report message to a moderator

Re: ORA-01555 [message #420161 is a reply to message #420143]

Thu, 27 August 2009 06:00

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

v$undostat is there to help decide which undo_retention you need.

Regards
Michel

Report message to a moderator

Re: ORA-01555 [message #420162 is a reply to message #420161]

Thu, 27 August 2009 06:06

saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE

Member

I executed the select statement in the update query

SELECT
  SUM(PTLS.ACTUALHOURS)
FROM
  KP_PMTL_VIEW PTLS ,
  TIMESHEET TS,
  DSL_ITEMEFFORTDATA IED
WHERE
  PTLS.TIMESHEETID = TS.TIMESHEETID AND
  PTLS.ACTUALHOURS > 0 AND
  PTLS.ITEMTYPE = 'Tsk' AND
  PTLS.ITEMID = IED.ITEMID AND
  PTLS.ITEMTYPE = IED.ITEMTYPE AND
  TS.USERID = IED.USERID AND
  PTLS.PROJECTID = IED.OWNERID AND
  (PTLS.PROJECTID = -1 OR -1 = -1)
GROUP BY PTLS.ITEMTYPE , PTLS.ITEMID,TS.USERID

Then executed the query

select max(maxquerylen) from v$undostat

MAX(MAXQUERYLEN)
34636

Yesterday the same Query gave a different result

MAX(MAXQUERYLEN)
25028

Therefore we have not been able to decide the UNDO_RETENTION

Report message to a moderator

Re: ORA-01555 [message #420167 is a reply to message #420162]

Thu, 27 August 2009 06:25

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What do you expect from us?

Regards
Michel

Report message to a moderator

Re: ORA-01555 [message #420233 is a reply to message #420162]

Thu, 27 August 2009 11:12

Nirnay Jaiswal
Messages: 3
Registered: August 2009

Junior Member

Also check for NOSPACEERRCNT in the v$undostat. You can increase the UNDO_RETENTION to 35000.

Report message to a moderator

Previous Topic:	10g database related
Next Topic:	Basic questions on Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 13:24:31 CST 2024