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

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

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

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Fri, 04 Apr 2003 08:53:40 -0800
Message-ID: <F001.0057A812.20030404085340@fatcity.com>


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).
Received on Fri Apr 04 2003 - 10:53:40 CST

Original text of this message

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