Check for unindexed foreign keys. Bites you every time.
"Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote in
message news:3c34a3a8.3902769406_at_news.alt.net...
> On 3 Jan 2002 07:11:54 -0800, mark.powell_at_eds.com (Mark D Powell)
> wrote:
>
> >SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch) wrote in
message news:<3c335112.3816091359_at_news.alt.net>...
> >> 8.1.6
> >>
> >> I had three programs run into each other today. They all locked rows
> >> in the same table with a ROW EXCLUSIVE lock when updating a number of
> >> rows. Specifically, the first program uses one or two bind variables
> >> in each of three different UPDATE statements. It needed to run each
> >> statement >100,000 times.
> >>
> >> The second program (and third) came in (no binds) and tried running an
> >> UPDATE statement on one of the three tables. They CONNECTed between
> >> 100 and 200 times (each UPDATE having its own process). (Normally,
> >> these happen far enough apart not be run at the same time.)
> >>
> >> There were three things that I had a hard time with.
> >>
> >> 1) Figuring out what the bind variables equaled. V$SQLArea showed bind
> >> variables, but not their values. How do I find out the current value
> >> of a bind variable?
> >>
> >> 2) I killed the second two processes so they wouldn't interfere with
> >> the first process. Yet, after I killed them, the first process didn't
> >> continue. No other locks were being held. The only locks were three
> >> ROW EXCLUSIVE locks, on three different tables all held by this one
> >> program. Is there a way I could have diagnosed why it didn't continue
> >> when I removed all other locks.
> >>
> >> 3) When two separate processes hold ROW EXCLUSIVE locks on the same
> >> table, why can they not both continue. Or are they continuing but just
> >> at a slow pace?
> >>
> >> Brian
> >
> >Multiple sessions can hold row level exclusive locks without impacting
> >performance as long as the sessions do not go after 1- the same rows
> >and 2- adequate transaction work areas exist in the block to handle
> >all sessions that want to update a row in the block. Indexes update
> >activity required by the table update activity is likely to cause more
> >problems than the table update activity itself.
>
> Well, I believe they were not going after the same rows. Or at least,
> they *shouldn't* have been. I checked a few and they were different. I
> didn;t check them all.
>
> How does one check the "transaction work areas"? Unfortunately, I am
> not familiar with that.
>
> >
> >Unless your sessions were lockwaited there was no need to terminate
> >them. If they were lockwaited on each other then you may have gotten
> >into a deadlock situation. If a deadlock was detected by Oracle then
> >Oracle would have chosen one of the sessions to be rolled back. Also
> >once you killed a session rollback would have been required before
> >processing could have been resumed. Did you check your remaining
> >process to see if it was being rolled back?
>
> The process should have run the one UPDATE query. Being it didn't seem
> to run, what would have been needed to be rolled back?
>
> >What did v$session_waiting show for the competing sessions? This view
> >should be beneficial if the problem is not the need for all the
> >processes to update the same row but in enqueue related or IO related
> >etc.... Also in the case where the conflict is for a specific row
> >v$session should show the rowid being waited upon.
>
> I believe you mean v$session_wait. I could not find one with "ing" on
> the end.
>
> I did not check these. Thank you.
>
> Brian
>
Received on Fri Jan 04 2002 - 13:32:32 CST
