Home » RDBMS Server » Server Administration » ORA-01555
ORA-01555 [message #420143] Thu, 27 August 2009 04:19 Go to next message
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...
Re: ORA-01555 [message #420161 is a reply to message #420143] Thu, 27 August 2009 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v$undostat is there to help decide which undo_retention you need.

Regards
Michel
Re: ORA-01555 [message #420162 is a reply to message #420161] Thu, 27 August 2009 06:06 Go to previous messageGo to next message
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
Re: ORA-01555 [message #420167 is a reply to message #420162] Thu, 27 August 2009 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you expect from us?

Regards
Michel
Re: ORA-01555 [message #420233 is a reply to message #420162] Thu, 27 August 2009 11:12 Go to previous message
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.
Previous Topic: 10g database related
Next Topic: Basic questions on Oracle
Goto Forum:
  


Current Time: Wed Dec 07 20:41:37 CST 2016

Total time taken to generate the page: 0.22045 seconds