RE: Row cache locks on INSERTs with a sequence
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 # of extents over time. More than likely the excessive extents were the real problem, and not the sequence per se, the sequence was just a victim. From what I read on Metalink, the shared pool could be an issue with the row cache locks, but my thinking was that if so, there should be other symptoms pointing to a need to increase the size of the shared pool, and we weren't seeing them (or at least that's what we thought). I had them fix the code by removing the sequence (it was not needed as the column using the sequence was some kind of internal counter only needed for the duration of the load process and not part of any primary key) and to perform a truncate instead of a delete. BTW, The PL/SQL routine performing the inserts is, according to Tim Gorman's TOP script, one of the top two heaviest resource abusers. Per my statspack reports, the row cache lock is no longer one of the top 5 wait events. -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Monday, December 23, 2002 11:49 AM To: Multiple recipients of list ORACLE-L Subject: Re: Row cache locks on INSERTs with a sequence 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; end; I couldn't get a single row cache lock wait. This was using 8.1.7.4 on HPUX 11. So I wonder if the waits you were seeing were a side-effect of another issue, or highly version dependent. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 23 December 2002 14:40 >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. > >And of course, the programmers never apologized, or even deigned to >acknowledge >appreciation in improving their application, they are simply acting as >offended >cats will do, as though the DBA team doesn't exist. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row cache locks on INSERTs with a sequence
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; end; I couldn't get a single row cache lock wait. This was using 8.1.7.4 on HPUX 11. So I wonder if the waits you were seeing were a side-effect of another issue, or highly version dependent. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 23 December 2002 14:40 >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. > >And of course, the programmers never apologized, or even deigned to >acknowledge >appreciation in improving their application, they are simply acting as >offended >cats will do, as though the DBA team doesn't exist. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row cache locks on INSERTs with a sequence
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. And of course, the programmers never apologized, or even deigned to acknowledge appreciation in improving their application, they are simply acting as offended cats will do, as though the DBA team doesn't exist. -Original Message- From: Khedr, Waleed [mailto:[EMAIL PROTECTED]] Sent: Friday, December 20, 2002 11:09 PM To: Multiple recipients of list ORACLE-L Subject: RE: Row cache locks on INSERTs with a sequence 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 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 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 least on my 9.2 system - the values may vary across version). I based my comments on dc_segments - not on the cache id number - still, I got lucky ! Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 20 December 2002 21:11 >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 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://www.jlcomp.demon.co.uk > >Coming soon a new one-day tutorial: >Cost Based Optimisation >(see http://www.jlcomp.demon.co.uk/tutorial.html ) > >Next Seminar dates: >(see http://www.jlcomp.demon.co.uk/seminar.html ) > >England__January 21/23 > > >The Co-operative Oracle Users' FAQ >http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row cache locks on INSERTs with a sequence
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 Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 22 December 2002 02:28 >> It is a common error to assume that v$session_wait >> is showing a wait time - however if the STATE is >> __not__ "WAITING" then the SECONDS_IN_WAIT >> column is the time since the last wait ended. > >It's actually the time since the last wait *began*, right? > > >Cary Millsap >Hotsos Enterprises, Ltd. >http://www.hotsos.com > >Upcoming events: >- Hotsos Clinic 101, Jan 7-9 Knoxville >- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen >- 2003 Hotsos Symposium, Feb 9-12 Dallas > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row cache locks on INSERTs with a sequence
> It is a common error to assume that v$session_wait > is showing a wait time - however if the STATE is > __not__ "WAITING" then the SECONDS_IN_WAIT > column is the time since the last wait ended. It's actually the time since the last wait *began*, right? Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101, Jan 7-9 Knoxville - Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium, Feb 9-12 Dallas -Original Message- Lewis Sent: Saturday, December 21, 2002 3:49 AM To: Multiple recipients of list ORACLE-L On the information as supplied you are correct, the sequence does seem to be the most likely problem. It has occurred to me, though, that the original poster showed us a couple of lines from v$session_wait - without showing the STATE (or is it STATUS) column. It is a common error to assume that v$session_wait is showing a wait time - however if the STATE is __not__ "WAITING" then the SECONDS_IN_WAIT column is the time since the last wait ended. So perhaps the OP was seeing lots of 'latch wait' recorded as the previous wait whilst the system was busy burning CPU on something else. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__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:11 >Subject: RE: Row cache locks on INSERTs with a sequence > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row cache locks on INSERTs with a sequence
On the information as supplied you are correct, the sequence does seem to be the most likely problem. It has occurred to me, though, that the original poster showed us a couple of lines from v$session_wait - without showing the STATE (or is it STATUS) column. It is a common error to assume that v$session_wait is showing a wait time - however if the STATE is __not__ "WAITING" then the SECONDS_IN_WAIT column is the time since the last wait ended. So perhaps the OP was seeing lots of 'latch wait' recorded as the previous wait whilst the system was busy burning CPU on something else. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__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:11 >Subject: RE: Row cache locks on INSERTs with a sequence > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row cache locks on INSERTs with a sequence
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 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 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 least on my 9.2 system - the values may vary across version). I based my comments on dc_segments - not on the cache id number - still, I got lucky ! Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 20 December 2002 21:11 >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 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://www.jlcomp.demon.co.uk > >Coming soon a new one-day tutorial: >Cost Based Optimisation >(see http://www.jlcomp.demon.co.uk/tutorial.html ) > >Next Seminar dates: >(see http://www.jlcomp.demon.co.uk/seminar.html ) > >England__January 21/23 > > >The Co-operative Oracle Users' FAQ >http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row cache locks on INSERTs with a sequence
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 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 least on my 9.2 system - the values may vary across version). I based my comments on dc_segments - not on the cache id number - still, I got lucky ! Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 20 December 2002 21:11 >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 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://www.jlcomp.demon.co.uk > >Coming soon a new one-day tutorial: >Cost Based Optimisation >(see http://www.jlcomp.demon.co.uk/tutorial.html ) > >Next Seminar dates: >(see http://www.jlcomp.demon.co.uk/seminar.html ) > >England__January 21/23 > > >The Co-operative Oracle Users' FAQ >http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row cache locks on INSERTs with a sequence
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 process. Instead of truncating the table at the start of the load process, they were performing a DELETE. The table had 0 rows, was 38MB in size, and had 300 extents. The index underlying the PK constraint on this table had 632 extents. In addition, looking at the code and table design, found that they did not need a sequence at all. The column utilizing the sequence was simply a dummy number not involved in defining any keys in the live table. Merely using an internal counter in a PL/SQL loop would have sufficed. Nice. The developers were adamant to management that the DBA team was at fault, did not know how to manage the database, etc; and now they are skulking in the corner avoiding us. -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Friday, December 20, 2002 2:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: Row cache locks on INSERTs with a sequence 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://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 20 December 2002 16:56 >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. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row cache locks on INSERTs with a sequence
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 least on my 9.2 system - the values may vary across version). I based my comments on dc_segments - not on the cache id number - still, I got lucky ! Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 20 December 2002 21:11 >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 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://www.jlcomp.demon.co.uk > >Coming soon a new one-day tutorial: >Cost Based Optimisation >(see http://www.jlcomp.demon.co.uk/tutorial.html ) > >Next Seminar dates: >(see http://www.jlcomp.demon.co.uk/seminar.html ) > >England__January 21/23 > > >The Co-operative Oracle Users' FAQ >http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row cache locks on INSERTs with a sequence
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 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 process. Instead of truncating the table at the start of the load process, they were performing a DELETE. The table had 0 rows, was 38MB in size, and had 300 extents. The index underlying the PK constraint on this table had 632 extents. In addition, looking at the code and table design, found that they did not need a sequence at all. The column utilizing the sequence was simply a dummy number not involved in defining any keys in the live table. Merely using an internal counter in a PL/SQL loop would have sufficed. Nice. The developers were adamant to management that the DBA team was at fault, did not know how to manage the database, etc; and now they are skulking in the corner avoiding us. -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Friday, December 20, 2002 2:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: Row cache locks on INSERTs with a sequence 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://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 20 December 2002 16:56 >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. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row cache locks on INSERTs with a sequence
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, 2002 3:21 PMTo: Multiple recipients of list ORACLE-LSubject: 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 process. Instead of truncating the table at the start of the load process, they were performing a DELETE. The table had 0 rows, was 38MB in size, and had 300 extents. The index underlying the PK constraint on this table had 632 extents. In addition, looking at the code and table design, found that they did not need a sequence at all. The column utilizing the sequence was simply a dummy number not involved in defining any keys in the live table. Merely using an internal counter in a PL/SQL loop would have sufficed. Nice. The developers were adamant to management that the DBA team was at fault, did not know how to manage the database, etc; and now they are skulking in the corner avoiding us. -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Friday, December 20, 2002 2:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: Row cache locks on INSERTs with a sequence 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://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 20 December 2002 16:56 >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. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row cache locks on INSERTs with a sequence
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 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://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 20 December 2002 16:56 >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 P3TEXTP3 >- - -- -- -- - > 29 row cache lockcache id 13 mode 0 request5 > 105 row cache lockcache id 13 mode 0 request5 > >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. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row cache locks on INSERTs with a sequence
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://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 20 December 2002 16:56 >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 P3TEXTP3 >- - -- -- -- - > 29 row cache lockcache id 13 mode 0 request5 > 105 row cache lockcache id 13 mode 0 request5 > >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. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Row cache locks on INSERTs with a sequence
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). Increasing shared pool size may help. - Kirti -Original Message-From: Thomas Jeff [mailto:[EMAIL PROTECTED]]Sent: Friday, December 20, 2002 8:45 AMTo: Multiple recipients of list ORACLE-LSubject: 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'
RE: Row cache locks on INSERTs with a sequence
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 ORACLE-LSubject: 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'
