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.
[EMAIL PROTECTED]
-----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
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
