Title: RE: Row cache locks on INSERTs with a sequence
If I stated dc_segments in my original post, I apologize, I *did* mean to say
dc_sequences. At any rate, as usual, the problem was poor application code.
The row cache lock no longer shows up as one of the top 5 wait events per
statspack
Just as a side-line observation - when I realised
that the problem should have been with the sequence,
I set up a small test on a multi-CPU box to run multiple
concurrent copies of:
begin
for i in 1..10 loop
insert into t1 values (test_seq.nextval);
end loop;
Title: RE: Row cache locks on INSERTs with a sequence
Jonathan,
The inserts were into a staging table. After the staging table was
loaded into the live tables, they were using delete and not truncate
to flush the staging table. So of course the table and index were
acquiring an excessive
Correct -
I was sure that I had a set of test results to
prove my point, but I didn't - so I've just run
a series of tests on enqueue and buffer busy waits
on an 8.1.7.4 system and you are absolutely right -
the seconds_in_wait does not reset as the wait
completes.
Thanks,
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
From: Khedr, Waleed [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 20 December 2002 21:11
Subject: RE: Row cache locks on INSERTs with a sequence
--
Please see the official
://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
From: Khedr, Waleed [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 20 December 2002 21:11
Subject: RE: Row cache locks on INSERTs with a sequence
--
Please see the official ORACLE-L FAQ
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
AMTo: Multiple recipients of list
Title: Row cache locks on INSERTs with a sequence
You are waitingon locks for the dictionary cache
dc_sequences (cache id = 13).
Increasing how many sequences are cached may
nothelp.If sequence
cachingwere the problem, you would seenwaits for latch free event
(for sequence cache).
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
What does it mean cache id 13 ?
Regards,
Waleed
-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
Title: RE: Row cache locks on INSERTs with a sequence
It
looks like a blame game!
You
mentioned many changes: truncate, dropping the use of sequence,
etc.
So
which one was it?
:)
Waleed
-Original Message-From: Thomas Jeff
[mailto:[EMAIL PROTECTED]]Sent: Friday, December 20
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
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
Row cache locks
It matches to the cache# in v$rowcache.
I have to say that I seem to have got to a
suitable conclusion by mistake. The OP
quoted cache id 13, and said it references
dc_segments - hence my suggestion about
small extents.
In fact cache id = 2 is dc_segments, and
cache id = 13 is dc_sequences (at
Title: 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
And I was unlucky :(
Cache id 13 is dc_sequences on 8i and 9i versions of oracle, so I guessed shared pool
shortage, if latch waits did not point to SQ enqueue...
- Kirti
-Original Message-
Sent: Friday, December 20, 2002 3:39 PM
To: Multiple recipients of list ORACLE-L
It matches
You are nice person. I still think the problem was mainly a sequence
problem.
regards,
Waleed
-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 12/20/02 9:43 PM
And I was unlucky :(
Cache id 13 is dc_sequences on 8i and 9i versions of oracle, so I
guessed shared pool
16 matches
Mail list logo
