RE: Row cache locks on INSERTs with a sequence

Thanks for the replies.

And, good guess Jonathan.

We've already made some changes to the stored procedure implementing the INSERT statement.

Upon investigation, I found that the table involved is used as a staging table in a
batch process. Instead of truncating the table at the start of the load process, they
were performing a DELETE. The table had 0 rows, was 38MB in size, and had 300
extents. The index underlying the PK constraint on this table had 632 extents.

In addition, looking at the code and table design, found that they did not need a sequence at all. The column utilizing the sequence was simply a dummy number
not involved in defining any keys in the live table. Merely using an internal
counter in a PL/SQL loop would have sufficed.

Nice. The developers were adamant to management that the DBA team was at fault,
did not know how to manage the database, etc; and now they are skulking in the
corner avoiding us.

-----Original Message-----
Sent: Friday, December 20, 2002 2:29 PM
To: Multiple recipients of list ORACLE-L

If the wait times on the latch were significant, I think I'd check that the inserts were high volume inserts into tables with a very small extent sizes and lots of indexes, also with very small extents.

I wouldn't have thought it was anything to do with sequences.

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: 20 December 2002 16:56

>We are continually seeing sessions hanging on row cache locks, which
in
>turn appear to be on dc_segments:
>
> SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
>----- ----------------- ---------- -- -------- -- --------- ----
> 29 row cache lock cache id 13 mode 0 request 5
> 105 row cache lock cache id 13 mode 0 request 5
>
>The offending SQL statement is an INSERT of the following form:
>
>INSERT INTO TABLE (A,B,C,D...) VALUES (:b1, :b2, :b3,
SEQUENCE.NEXTVAL,..)
>
>The sequence in question has it's cache value set to the default of
20.
>
>The developers keep insisting that it's a shared pool issue. I've
>researched Metalink and not come up with a whole lot. I've ran
>statspack and it has rendered advice with respect to the fact that a
lot
>of new sequence values are being acquired, therefore the sequence
cache size
>needs examination.
>

-- 
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).