From: Thomas Jeff [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 20, 2002 8: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'
--------------------------------------------
