Re: spontaneous rollback and retry?

"Richard Kuhler" <noone_at_nowhere.com> a écrit dans le message de news:AU0Lb.48871$nG3.25381_at_twister.socal.rr.com...
> I'm trying to make sense of what I'm seeing for a process that's issuing
> a parallel merge statement. For about the first 45 minutes I see the
> undo blocks allocated rising (v$transaction.used_ublk). Then the number
> slow goes back down to zero over the next 45 minutes. Both "transaction
> rollbacks" and "rollback changes - undo records applied" statistic goes
> up as well. At that point, Oracle seems to be stopping all the parallel
> sessions, starting a new transaction (v$transaction.start_scnb changes)
> and creating new parallel sessions. Then undo allocation starts to go
> back up again. It's been doing this for several hours longer than this
> process should really take. This process is not in any sort of loop
> however so it appears that Oracle is doing all of these "retries" on
> it's own.
>
> What exactly is Oracle doing here?
>
>
> Thanks,
> Richard Kuhler
>

Many questions: OS and Oracle release ? which statement (select...) ? Oracle configuration (RAC)?...
Do you have a trace file?
Can you activate trace with an 10046 event before your statement: alter session set events '10046 trace name context forever, level 8'; and after:
alter session set events '10046 trace name context off';

May be you'll find useful informations in the trace file.

Regards
Michel Cadot Received on Thu Jan 08 2004 - 02:22:45 CST