Re: Operations in huge transaction slow down suddenly. Why?

From: Volker Hetzer <volker.hetzer_at_ieee.org>
Date: Mon, 20 Dec 2004 18:51:13 +0100
Message-ID: <cq73eh$i2j$1@nntp.fujitsu-siemens.com>

"Bernhard Graeuler" <fuerspam_at_gmx.de> schrieb im Newsbeitrag news:32ogvnF3prr21U1_at_individual.net...
> ana <anacedent_at_hotmail.com> schrieb:
> > "Bernhard Graeuler" <fuerspam_at_gmx.de> wrote in message
> > news:32ge68F3mhfnuU1_at_individual.net...
> > > Hi,
> >
> > Run this SQL while the slowerdown is happening.
> > If this return no rows then you don't have an enqueue problem.
> >
> > SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
> > id1, id2, lmode, request, type
> > FROM V$LOCK
> > WHERE (id1, id2, type) IN
> > (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
> > ORDER BY id1, request
> > /
>
> Sadly, this statement does not reply anything. It seems to be an IO problem,
> I found many thousands of the following lines in the TRC files:
>
> WAIT #19: nam='db file sequential read' ela= 0 p1=24 p2=245433 p3=1
>
> We did some tests with bigger log files and more log_buffers, but it didn't
> seem to help.

Maybe I'm being dense here but why the join? Can't you just select
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

          id1, id2, lmode, request, type
     FROM V$LOCK

    WHERE request>0
    ORDER BY id1, request

Personally I'd even remove the sort. On a machine under load you can always spool the output in a file and sort later.
Besides, if you only want rows with request>0, what use is the decode of? It's always going to return 'Waiter:'.

Lots of Greetings!
Volker Received on Mon Dec 20 2004 - 11:51:13 CST