RE: Row cache locks on INSERTs with a sequence

[Khedr, Waleed]

Title: Row cache locks on INSERTs with a sequence

It looks like sequence generation & caching rate is slower than your insertion rate.

 

Waleed

-----Original Message-----
From: Thomas Jeff [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 20, 2002 9:45 AM
To: Multiple recipients of list ORACLE-L
Subject: Row cache locks on INSERTs with a sequence

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.

But it is necessarily the small cache size of a sequence that can cause
these locking issues?

Thanks.

--------------------------------------------
Jeffery D Thomas
DBA
Thomson Information Services
Thomson, Inc.

Email: [EMAIL PROTECTED]

Indy DBA Master Documentation available at:
http://gkmqp.tce.com/tis_dba
Select 'Indy DBA' then 'DBA Web Pages'
--------------------------------------------