Re: deadlocks and redologs interaction

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 Received on Tue Sep 11 2007 - 21:02:53 CDT