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

Home -> Community -> Usenet -> c.d.o.server -> Re: spontaneous rollback and retry?

Re: spontaneous rollback and retry?

From: Richard Kuhler <noone_at_nowhere.com>
Date: Thu, 08 Jan 2004 20:25:00 GMT
Message-ID: <w0jLb.45890$C87.11580@twister.socal.rr.com>


Jonathan Lewis wrote:
<snip>

> Which version of Oracle are you on, 9.0. or 9.2 ?

Sorry, forgot the standard info in my original post.

Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production

We believe we've found the source of the problem although not really the explanation. At the time the process was running, a DBA kicked of a DBMS_STATS analysis for this instance. We killed our process and the DBMS_STATS session and retried our process. This time it finished without any problems in the expected amount of time. To confirm this interaction, we ran our process again and started DBMS_STATS while it was running again. The same repeated rollback and retry behavior was observed. Something about the DBMS_STATS run is causing the issues with the MERGE operation.

> Are you updating a partitioned table ?
> And you could you tell us what TX, TM and PS
> locks appear in v$lock when you run the update.
> (If you have a high degree of parallelism, you only
> need list the locks from one of the slave SIDs).

Yes, the table being updated is partitioned. However, direct path inserting is being disabled using the NOAPPEND hint for the MERGE operation because of the huge space wastage involved with that. I'll have to wait for our next test run to give any information about the locks that are being held.

Thanks,
Richard Kuhler Received on Thu Jan 08 2004 - 14:25:00 CST

Original text of this message

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