RE: Row cache locks on INSERTs with a sequence

From: Thomas Jeff <ThomasJe_at_tce.com>
Date: Mon, 23 Dec 2002 10:13:42 -0800
Message-ID: <F001.005215B9.20021223101342@fatcity.com>

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-----
Sent: Monday, December 23, 2002 11:49 AM To: Multiple recipients of list ORACLE-L

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 <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Thomas Jeff
  INET: ThomasJe_at_tce.com

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: ListGuru_at_fatcity.com (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).

Received on Mon Dec 23 2002 - 12:13:42 CST