Re: deadlocks and redologs interaction

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. Received on Tue Sep 11 2007 - 20:55:07 CDT