Jonathan,
The inserts were into a staging table. After the staging table was
loaded into the live tables, they were using delete and not truncate
to flush the staging table. So of course the table and index were
acquiring an excessive # of extents over time. More than likely the
excessive extents were the real problem, and not the sequence per se,
the sequence was just a victim. From what I read on Metalink, the shared
pool could be an issue with the row cache locks, but my thinking was
that if so, there should be other symptoms pointing to a need to increase
the size of the shared pool, and we weren't seeing them (or at least
that's what we thought).
I had them fix the code by removing the sequence (it was not needed as
the column using the sequence was some kind of internal counter only
needed for the duration of the load process and not part of any primary key)
and to perform a truncate instead of a delete. BTW, The PL/SQL routine
performing the inserts is, according to Tim Gorman's TOP script, one of the
top two heaviest resource abusers.
Per my statspack reports, the row cache lock is no longer one of the top 5
wait events.
-----Original Message-----
From: Jonathan Lewis [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 23, 2002 11:49 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Row cache locks on INSERTs with a sequence
Just as a side-line observation - when I realised
that the problem should have been with the sequence,
I set up a small test on a multi-CPU box to run multiple
concurrent copies of:
begin
for i in 1..100000 loop
insert into t1 values (test_seq.nextval);
end loop;
end;
I couldn't get a single row cache lock wait.
This was using 8.1.7.4 on HPUX 11.
So I wonder if the waits you were seeing were a
side-effect of another issue, or highly version
dependent.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____England______January 21/23
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 23 December 2002 14:40
>If I stated dc_segments in my original post, I apologize, I *did*
mean to
>say
>dc_sequences. At any rate, as usual, the problem was poor
application
>code.
>The row cache lock no longer shows up as one of the top 5 wait events
per
>statspack.
>
>And of course, the programmers never apologized, or even deigned to
>acknowledge
>appreciation in improving their application, they are simply acting
as
>offended
>cats will do, as though the DBA team doesn't exist.
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
