Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rollback and what table's DML caused an ORA-1555?

RE: Rollback and what table's DML caused an ORA-1555?

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Fri, 04 Apr 2003 09:24:20 -0800
Message-ID: <F001.0057A906.20030404092420@fatcity.com>


Rich
  For what it's worth, here would be my priorities:

  1. Ask the users to run the report at a different time. Maybe plot the transactions over a 24-hour period to display better time. They won't like this, and you may want to soften it with "until we get the application modified".
  2. Create a new rollback tablespace. You can do this quickly. Ask the report users to hold off running reports for a few minutes, then offline the current rollback tablespace. It won't go off until all current transactions complete.
  3. Modify the application. In my experience, actions #1 and #2 can be done within hours. Modifying the application may take between a couple of days and never.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Friday, April 04, 2003 10:54 AM
To: Multiple recipients of list ORACLE-L

Hey all,

Fighting with a lot of ORA-1555s lately on 8.1.7.4 on HP/UX. Most of them are now coming from long-running Business Objects (B.O.) queries against our OLTP DB. I think I need to recreate the RBS tablespace (currently 1MB extents in LMT), but until I can get time to do that, I'd like to approach this from the application side, where I think the majority of the problem is occurring. I've been tracking TPM based on "user commits" in V$SYSSTAT and we spiked at 20K TPM just before the B.O. query in question ORA-1555'd.
>From STATSPACK reports, I think the most likely cause for this is a COMMIT
for every DML in a batch job. From what I've read, including MetaLink 40689.1, this over committing is one potential cause of ORA-1555s.

In order to narrow down the problem, I've turned on event 1555 in the instance. Is it possible to determine what table(s)' DML is causing the ORA-1555 based on the trace file? I have the last wait state, which happens to be "db file sequential read", but I don't know if there's any correlation. If there is, I should be able to determine which table by the file# and block# given in the trace. Is this correct?

Also, if the over-committing process is not doing any DML on the tables of the B.O. query, is it still a possible suspect of causing the ORA-1555 because of the potential of overwriting another process' RBS?

Damn. I was hoping to be at 9i before I had to deal with RBSs... :)

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Apr 04 2003 - 11:24:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US