Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Rollback and what table's DML caused an ORA-1555?
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