RE: Row cache locks on INSERTs with a sequence

[Wong, Bing]

Title: RE: Row cache locks on INSERTs with a sequence

Let me guess - they did not say sorry to you.

-----Original Message-----
From: Thomas Jeff [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 20, 2002 12:21 PM
To: Multiple recipients of list ORACLE-L
Subject: 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-----
From: Jonathan Lewis [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 20, 2002 2:29 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Row cache locks on INSERTs with a sequence

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 <[EMAIL PROTECTED]>
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: [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).