Re: deadlocks and redologs interaction

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Wed, 12 Sep 2007 03:23:36 GMT
Message-ID: <YYIFi.124860$xx1.17679@newsfe09.phx>

carab1n3r <carab1n3r_at_hotmail.com> wrote in news:1189565376.407927.263270_at_o80g2000hse.googlegroups.com:

> On Sep 11, 7:02 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:

>> On Sep 11, 9:55 pm, carab1n3r <carab1..._at_hotmail.com> wrote:
>>
>>
>>
>> > On Sep 11, 5:32 pm, Steve Howard <stevedhow..._at_gmail.com> wrote:
>>
>> > > On Sep 11, 8:20 pm, carab1n3r <carab1..._at_hotmail.com> wrote:
>>
>> > > > I'm still relatively green as a DBA, so excuse the possibly
>> > > > simple question...
>>
>> > > > We have a DB that is seeing a lot of deadlocks, occasionally
>> > > > hundreds at a time.  Yes, typically this is an
>> > > > external-application/development problem.
>>
>> > > > However, I'm trying to determine and/or explain what impact
>> > > > lots-o- deadlocks has on redolog, particularly the switching. 
>> > > > The reason I mention it is that during these "deadlock storms",
>> > > > we occasionally run out of redologs to switch to.
>>
>> > > > The drumbeat I'm hearing is to "just add more redolog
>> > > > groups!!!" since that's easy to do, but we're only running into
>> > > > this redolog-switching problem during the deadlock storms, and
>> > > > my gut is that this is just a bandaid and will eventually not
>> > > > resolve the problem as deadlock rates continue to increase.
>>
>> > > > So can someone explain to me (in such a way that I can explain
>> > > > it to others), when we have lots of deadlocks, what impact does
>> > > > that have on redologs?
>>
>> > > > Thanks a lot in advance for the advice.
>>
>> > > Hi,
>>
>> > > Which version and OS is this running on?  What do you mean by
>> > > deadlocks?  Are exceptions being thrown in the app with an ORA-
>> > > prefix?
>>
>> > > Regards,
>>
>> > > Steve
>>
>> > Oracle enterprise 10g (10.2.0.3)
>> > RHEL4u3
>>
>> > In alert.log:
>>
>> > ORA-00060: Deadlock detected. More info in file [path to
>> > file]SID_ora_23130.trc
>>
>> > A count on "Deadlock detected" shows 702 deadlocks in the alert.log
>> > just for today (Sept 11).
>>
>> > In our other logs, we see a lot of Exclusive and "Row Exclusive"
>> > locks.
>>
>> In general, those types of deadlocks have nothing to do with redo
>> logs (you would have to really stretch to make the connection).  Far
>> more often, deadlocks are caused by unindexed foreign keys.
>>
>> Search on asktom.oracle.com for a script to point you to any foreign
>> keys without an index, as well a full discussion as to why this
>> causes deadlocks.
>>
>> Regards,
>>
>> Steve

>
> Thanks for the tips on unindexed foreign keys.
>
> It sound like you understand my issue, but just to clarify (in case I
> didn't explain things well), I personally am clear that redologs are
> not causing deadlocks. What I am looking for, though, is an
> explanation of the impact an extensive # of deadlocks can have on the
> ability of oracle to put redologs in an INACTIVE state in v$log.
>
> My suspicion is that an extensive # of deadlocks can interfere with
> redolog rotation; since redologs are circular and sequential, I
> suspect a redolog cannot be freed up until a deadlocked transaction is
> completed or rolled back. Have enough deadlocks across enough
> redologs, and the DB has to stop accepting new transactions in the
> interim (we have 10 groups, btw. 100MB each).
>
> Perhaps my paragraph above is a good synopsis of our problem, but I'd
> like to hear whether my suspicions are an accurate interpretation of
> how Oracle manages deadlocks WRT redologs.
>
>
>

How often do the redo logfiles switch during a "storm"? Have you tried to mine the redo logfiles to see what is doing all the DML? Received on Tue Sep 11 2007 - 22:23:36 CDT