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
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
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
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
__January 21/23
The Co-operative Oracle Users' FAQ
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:
erative Oracle Users' FAQ
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 lo
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 s
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 t
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
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
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 on
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
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
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:/
Title: Row cache locks on INSERTs with a sequence
You are waiting on locks for the dictionary cache
dc_sequences (cache id = 13).
Increasing how many sequences are cached may
not help. If sequence
caching were the problem, you would seen waits for latch free event
(for sequence cache
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
Title: 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
17 matches
Mail list logo
