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

"Jesse, Rich" wrote:
>
> 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

Rich,

  In ORA-01555 you have two things :
  o DML
  o and a long-running query.

 IMHO the long-running query might be easier to catch. I know that with BO you don't have much control on the SQL, but who knows, perhaps that an index here or whatever ...
 Otherwise you can probably merrily get rid of that commit at every DML. For one thing, it will run much faster.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).