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'
--------------------------------------------
