RE: Insert performance
Rick, Find out what kind(s) of buffer busy waits you have. MetaLink, Anjo Kolk’s YAPP paper, or Steve Adams’s book can tell you how to read the p1, p2, p3 values on the waits you see. Then eliminate the motive for the waits. The same documents will tell you how. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004 March 7–10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rick Stephenson Sent: Tuesday, September 23, 2003 4:05 PM To: Multiple recipients of list ORACLE-L Subject: RE: Insert performance I have already bumped that up quite a bit as well, but I still get many waits. Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] -Original Message- From: Justin Cave [mailto:[EMAIL PROTECTED] Sent: Monday, September 22, 2003 11:10 PM To: Multiple recipients of list ORACLE-L Subject: Re: Insert performance At 09:44 PM 9/22/2003, you wrote: Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? Is the sequence cache set to an appropriately large value? Justin Cave I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes "buffer busy waits". I have increased initrans, but am not sure where to go from here Any ideas would be appreciated, Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. Justin Cave Distributed Database Consulting This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
RE: Insert performance
I have already bumped that up quite a bit as well, but I still get many waits. Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] -Original Message- From: Justin Cave [mailto:[EMAIL PROTECTED] Sent: Monday, September 22, 2003 11:10 PM To: Multiple recipients of list ORACLE-L Subject: Re: Insert performance At 09:44 PM 9/22/2003, you wrote: Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? Is the sequence cache set to an appropriately large value? Justin Cave I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes "buffer busy waits". I have increased initrans, but am not sure where to go from here Any ideas would be appreciated, Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. Justin Cave Distributed Database Consulting This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
Re: Insert performance
Hi! Search for "surrogate key" in google for example. You have to modify your generated primary key values to not hit the same index blocks consecutively. For example, add another high-cardinality column to your primary key, or if using meaningless primary keys then just have your primary keys values to be spread over different value ranges (either by using two sequences, one is "main" seq, other is a "wrapping" cycling from 1 to 1000 for example - the result sequence would be wrapping seq * main seq, thus each insert is going to different part in index, other way would be using pre-generated primary key values). Other way would be to hash-partition your tables and indexes to spread inserts over partitions, but I suggest you to get your design ok first. Also, with lot's of concurrent inserts, you should increase the number of freelists for your tables to the number of concurrent inserts, also take a look to _bump_highwater_mark_count parameter and freelist groups when having really huge insert activity. Tanel. - Original Message - From: Rick Stephenson To: Multiple recipients of list ORACLE-L Sent: Tuesday, September 23, 2003 6:44 AM Subject: Insert performance Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes "buffer busy waits". I have increased initrans, but am not sure where to go from here Any ideas would be appreciated, Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
Re: Insert performance
Re: Insert performance
At 09:44 PM 9/22/2003, you wrote: Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? Is the sequence cache set to an appropriately large value? Justin Cave I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes "buffer busy waits". I have increased initrans, but am not sure where to go from here Any ideas would be appreciated, Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. Justin Cave Distributed Database Consulting
RE: Insert performance
Rick, I haven't tried this myself but you could consider a reverse key index (depending on your version). That way multiple inserts won't go to the same block. However, (from Perf Tuning 101) you will incur much more IO than a normal index if you do range scans, so you'd need to consider how your index is accessed (probably not doing range scans on a primary key sequence). Obviously this will need testing to see how well it goes for you. HTH, Bruce Reardon -Original Message- Sent: Tuesday, 23 September 2003 1:45 PM Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes "buffer busy waits". I have increased initrans, but am not sure where to go from here Any ideas would be appreciated, Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) 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).
Insert performance
Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes "buffer busy waits". I have increased initrans, but am not sure where to go from here Any ideas would be appreciated, Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED] This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
RE: Partitoned Table Insert Performance
Two years ago when partitioning came out we ran some tests. We found out that oracle had some coding problems if you were using 8.0.5. Everything ran okay, but it was slow. The problems was with how the determined which partition to put data into. If you had 200 partition and data was being loaded into the the last partition, it had to do 200 compares and then insert. They have fixed it and now the use a hash algorythem to determine which partition to insert data into. It is really fast. However you will find your biggest pay backs will be in doing selects. Did you know that partitioning takes advantage of pararrel processing, so your selects can be hitting several partitions at the same time. This really speeds everything up. You will not want to partition every table. Only those table that fit the partitioning model. Also the way that you partition data can make or break your application. You need to really think out how you are going to partition a table. It really makes a big difference in performance. Try different options and do some testing. Ask this question, "how are my users going to access the data?" This is the starting question. Also you can also see how partitioning is going to effect backups and system/database management. How many disks are you going to put data on. Which drives are used. Where are my indexes and data? All good question. So it's not just a partitioning question. >From: "Toepke, Kevin M" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: Partitoned Table Insert Performance >Date: Thu, 02 May 2002 09:48:31 -0800 > >Way back in the days of Oracle 8.0.5 I did some performance testing of bulk >inserts/sqlldr of range partitioned tables v.s. non-partitioned tables. I >don't have the benchmarks on hand, but here's what I found. All tests were >done using the direct path inserts (sqlldr direct=true or /*+ APPEND */) > >If the table had no indexes, then there was no noticable difference when >inserting approx 8 million rows. > >If the partitioned table had only local indexes, then the inserts into the >tables were slightly faster. The difference was in seconds for my 8million >row test. > >If the partitioned table had global partitioned indexes, then the inserts >were generally slower. Again, the diference was in seconds. > >If the partitioned table had a global non-partitioned index there was no >noticable diffence in time. > >In sort, range partitioning a table does not effect performance. The >indexing of the range-partitioned table does. > >HTH >Caver > >-Original Message- >Sent: Thursday, May 02, 2002 12:45 PM >To: Multiple recipients of list ORACLE-L > > >I am in the process of implementing partitioning on some existing tables. I >have been asked by management to evaluate the performance impacts of the >changes. I am aware of many of the performance advantages of partitioning: >partition pruning, partition-wise joins and parallel data loads. What I am >concerned about is the additional overhead of inserting data into a >partitioned table. What sort of overhead is associated with partitioned >table inserts? Does determining the correct partition slow insertions? We >are utilizing only range partitions, so hash value computations should not >be a factor. Our application is very insertion intensive. One of the >operations that performs insertions does so synchronously, so any decrease >in insertion performance would be quite visible. Do you have any thoughts >or >experiences with this issue. Any performance tests on two similar tables, >one partitioned and the other not? Any input would be appreciated. Thanks. >Erik > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Toepke, Kevin M > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >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). _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: basher 59 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, Califor
RE: Partitoned Table Insert Performance
Way back in the days of Oracle 8.0.5 I did some performance testing of bulk inserts/sqlldr of range partitioned tables v.s. non-partitioned tables. I don't have the benchmarks on hand, but here's what I found. All tests were done using the direct path inserts (sqlldr direct=true or /*+ APPEND */) If the table had no indexes, then there was no noticable difference when inserting approx 8 million rows. If the partitioned table had only local indexes, then the inserts into the tables were slightly faster. The difference was in seconds for my 8million row test. If the partitioned table had global partitioned indexes, then the inserts were generally slower. Again, the diference was in seconds. If the partitioned table had a global non-partitioned index there was no noticable diffence in time. In sort, range partitioning a table does not effect performance. The indexing of the range-partitioned table does. HTH Caver -Original Message- Sent: Thursday, May 02, 2002 12:45 PM To: Multiple recipients of list ORACLE-L I am in the process of implementing partitioning on some existing tables. I have been asked by management to evaluate the performance impacts of the changes. I am aware of many of the performance advantages of partitioning: partition pruning, partition-wise joins and parallel data loads. What I am concerned about is the additional overhead of inserting data into a partitioned table. What sort of overhead is associated with partitioned table inserts? Does determining the correct partition slow insertions? We are utilizing only range partitions, so hash value computations should not be a factor. Our application is very insertion intensive. One of the operations that performs insertions does so synchronously, so any decrease in insertion performance would be quite visible. Do you have any thoughts or experiences with this issue. Any performance tests on two similar tables, one partitioned and the other not? Any input would be appreciated. Thanks. Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Partitoned Table Insert Performance
My last set of test results is a little out of date, but here's an idea to check. Inserting single rows: partitioned key insert HAD ca. 50% overhead Array Inserts sorted by partition key to get lots of adjacent rows in the same partition virtually no overhead Array inserts randomised to maximise the probability of adjacent inserts being to different partitions Overhead ca. 50% Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to 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: 02 May 2002 17:35 |I am in the process of implementing partitioning on some existing tables. I |have been asked by management to evaluate the performance impacts of the |changes. I am aware of many of the performance advantages of partitioning: |partition pruning, partition-wise joins and parallel data loads. What I am |concerned about is the additional overhead of inserting data into a |partitioned table. What sort of overhead is associated with partitioned |table inserts? Does determining the correct partition slow insertions? We |are utilizing only range partitions, so hash value computations should not |be a factor. Our application is very insertion intensive. One of the |operations that performs insertions does so synchronously, so any decrease |in insertion performance would be quite visible. Do you have any thoughts or |experiences with this issue. Any performance tests on two similar tables, |one partitioned and the other not? Any input would be appreciated. Thanks. |Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Partitoned Table Insert Performance
I am in the process of implementing partitioning on some existing tables. I have been asked by management to evaluate the performance impacts of the changes. I am aware of many of the performance advantages of partitioning: partition pruning, partition-wise joins and parallel data loads. What I am concerned about is the additional overhead of inserting data into a partitioned table. What sort of overhead is associated with partitioned table inserts? Does determining the correct partition slow insertions? We are utilizing only range partitions, so hash value computations should not be a factor. Our application is very insertion intensive. One of the operations that performs insertions does so synchronously, so any decrease in insertion performance would be quite visible. Do you have any thoughts or experiences with this issue. Any performance tests on two similar tables, one partitioned and the other not? Any input would be appreciated. Thanks. Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: insert performance
No. CPU utilitization averages 40-60% across 4 CPUs. --- Mohammed Shakir <[EMAIL PROTECTED]> wrote: > No enough inserts to bog down the CPUs? > > --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > > Thanks, Mohammed and Anjo, for your replies. > > > > Now my question is this: given that the table structures > (freelists, > > etc.) are identical on the two machines, the init.ora parameters > are > > identical for the two instances, and the machines themselves are > > nearly > > identical (one has 6 CPUs, one 4, but in neither case are the CPUs > > anywhere near maxed out), what could be causing the discrepancy? > > > > PB > > > > --- Mohammed Shakir <[EMAIL PROTECTED]> wrote: > > > data block waits is the indicator of freelists contention. I have > > > never > > > seen a freelists contention, even though I have been running 11 > > > processing doing inserts using 11 CPUs. I have seen library > cache, > > > Shared pool and cache buffer chains waits. I took care of buffer > > busy > > > waits and db file sequential reads by increasing freelists, > > initrans > > > to > > > 11 and by partitioning the disks. In my case most of the data was > > > writting to the same block by as many as 9 processes. I am > working > > on > > > a > > > > > > Sparc 4500 Solaris 2.1.6 with Oracle 8.1.6.0 and 8.1.7.2 > > > > > > Library Shared Pool latch contention was found due to Literal or > > > non-shared SQL. Check SQL_text in SQLarea to find literal SQL. > > Shared > > > Pool contention seems to be due to a very large Shared pool. I > > found > > > a > > > bug report that indicates that cache buffer chain problem was > fixed > > > in > > > 8.1.3.4 and 9.0.1.3 > > > > > > Cache buffer chain is also an indicator of high physical and > > logical > > > I/O. You can check on that as well. > > > > > > Hope this helps. > > > > > > Shakir > > > > > > --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > > > > Greetings! > > > > > > > > I am trying to diagnose a performance difference between two > > > > databases > > > > running the same test. They are similarly configured (same SGA > > > size, > > > > etc.), and the servers are identical except for the number of > > CPUs > > > > (server A has 4, server B has 6). > > > > > > > > On database A, INSERT performance is about 190 rows/second. > > > > > > > > On database B, INSERT performance is over 500 rows/second. > > > > > > > > I saw some cache buffers chains, buffer busy, and library cache > > > latch > > > > waits on database A while the test was running, as well as redo > > log > > > > sync waits. The waits didn't seem excessive, though. I > checked > > > for > > > > "checkpoint not complete" redo allocation messages in database > > A's > > > > alert log and found none. The db_block_lru_latches parameter > is > > > set > > > > to > > > > one-half the number of CPUs in both machines. > > > > > > > > I'd much appreciate any suggestions as to what else to check. > I > > > know > > > > that freelists can be an issue (there are multiple sessions > > > > performing > > > > inserts); how can I check to see if there's freelist > contention? > > > > Anything else I should investigate? > > > > > > > > Many TIA, > > > > > > > > > > > > > > > > > > > > > > > > = > > > > Paul Baumgartel, Adept Computer Associates, Inc. > > > > [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > > > > > > > __ > > > > Do You Yahoo!? > > > > Yahoo! Tax Center - online filing with TurboTax > > > > http://taxes.yahoo.com/ > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > > -- > > > > Author: Paul Baumgartel > > > > INET: [EMAIL PROTECTED] > > > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) > > 538-5051 > > > > San Diego, California--
Re: insert performance
No enough inserts to bog down the CPUs? --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > Thanks, Mohammed and Anjo, for your replies. > > Now my question is this: given that the table structures (freelists, > etc.) are identical on the two machines, the init.ora parameters are > identical for the two instances, and the machines themselves are > nearly > identical (one has 6 CPUs, one 4, but in neither case are the CPUs > anywhere near maxed out), what could be causing the discrepancy? > > PB > > --- Mohammed Shakir <[EMAIL PROTECTED]> wrote: > > data block waits is the indicator of freelists contention. I have > > never > > seen a freelists contention, even though I have been running 11 > > processing doing inserts using 11 CPUs. I have seen library cache, > > Shared pool and cache buffer chains waits. I took care of buffer > busy > > waits and db file sequential reads by increasing freelists, > initrans > > to > > 11 and by partitioning the disks. In my case most of the data was > > writting to the same block by as many as 9 processes. I am working > on > > a > > > > Sparc 4500 Solaris 2.1.6 with Oracle 8.1.6.0 and 8.1.7.2 > > > > Library Shared Pool latch contention was found due to Literal or > > non-shared SQL. Check SQL_text in SQLarea to find literal SQL. > Shared > > Pool contention seems to be due to a very large Shared pool. I > found > > a > > bug report that indicates that cache buffer chain problem was fixed > > in > > 8.1.3.4 and 9.0.1.3 > > > > Cache buffer chain is also an indicator of high physical and > logical > > I/O. You can check on that as well. > > > > Hope this helps. > > > > Shakir > > > > --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > > > Greetings! > > > > > > I am trying to diagnose a performance difference between two > > > databases > > > running the same test. They are similarly configured (same SGA > > size, > > > etc.), and the servers are identical except for the number of > CPUs > > > (server A has 4, server B has 6). > > > > > > On database A, INSERT performance is about 190 rows/second. > > > > > > On database B, INSERT performance is over 500 rows/second. > > > > > > I saw some cache buffers chains, buffer busy, and library cache > > latch > > > waits on database A while the test was running, as well as redo > log > > > sync waits. The waits didn't seem excessive, though. I checked > > for > > > "checkpoint not complete" redo allocation messages in database > A's > > > alert log and found none. The db_block_lru_latches parameter is > > set > > > to > > > one-half the number of CPUs in both machines. > > > > > > I'd much appreciate any suggestions as to what else to check. I > > know > > > that freelists can be an issue (there are multiple sessions > > > performing > > > inserts); how can I check to see if there's freelist contention? > > > Anything else I should investigate? > > > > > > Many TIA, > > > > > > > > > > > > > > > > > > = > > > Paul Baumgartel, Adept Computer Associates, Inc. > > > [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > __ > > > Do You Yahoo!? > > > Yahoo! Tax Center - online filing with TurboTax > > > http://taxes.yahoo.com/ > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Paul Baumgartel > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) > 538-5051 > > > San Diego, California-- Public Internet access / Mailing > > > Lists > > > > > > > > > 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). > > > > > > = > > Mohammed Shakir > > CompuSoft, Inc. > > 11 Heather Way >
RE: insert performance
Disk layout and disk speed itself matters. While testing one same batch job with similar data on prod and test machine, test machine was performing same job in half of the time(2 hours) than prod(4 hours) and difference was fast fiber optical drives on test box even with slower processor but as it was write intensive job it was running faster on test. Please involve your unix group to explain/investigate this performance problem when everything is almost same as far as code,data and other relevant params are concerned. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 11 Apr 2002 11:28:38 -0800 Dennis-- The faster machine had 10 database connections, the slower, 50. Not all were used in either case. But my comparison is for inserts done by a single session, i.e., it's not the aggregate insert rate, but a direct comparison of the same insert statement in a single session on each server. As for the disk layout, I don't have that information readily available (the SA department doesn't like to let the DBA department get involved in such things!). I don't think it's significantly different, though. Thanks, Paul --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Paul - How many insert processes did you run on each system? Is the > disk > layout identical in terms of spreading across devices? > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -Original Message- > Sent: Wednesday, April 10, 2002 6:43 PM > To: Multiple recipients of list ORACLE-L > > > Greetings! > > I am trying to diagnose a performance difference between two > databases > running the same test. They are similarly configured (same SGA size, > etc.), and the servers are identical except for the number of CPUs > (server A has 4, server B has 6). > > On database A, INSERT performance is about 190 rows/second. > > On database B, INSERT performance is over 500 rows/second. > > I saw some cache buffers chains, buffer busy, and library cache latch > waits on database A while the test was running, as well as redo log > sync waits. The waits didn't seem excessive, though. I checked for > "checkpoint not complete" redo allocation messages in database A's > alert log and found none. The db_block_lru_latches parameter is set > to > one-half the number of CPUs in both machines. > > I'd much appreciate any suggestions as to what else to check. I know > that freelists can be an issue (there are multiple sessions > performing > inserts); how can I check to see if there's freelist contention? > Anything else I should investigate? > > Many TIA, > > > > > > = > Paul Baumgartel, Adept Computer Associates, Inc. > [EMAIL PROTECTED] > > > > > > __ > Do You Yahoo!? > Yahoo! Tax Center - online filing with TurboTax > http://taxes.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Paul Baumgartel > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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.com > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051
RE: insert performance
Paul - My point is that I have seen performance decrease when too many processes are used. For example, with imports, a multi-cpu system may import faster with two import jobs running. But at some point it seems that Oracle and/or the operating system is just trying to switch between each process to make sure they are all getting attention, and the overall performance suffers. It certainly doesn't just focus on one process and ignore all the others. Given that you are throwing 50 processes at the 4-cpu system, I could see it running slower. Typically on for a meaningful benchmark, you would vary the number of processes to see where the optimum throughput is achieved. This is why benchmarks are so expensive to perform -- very time-consuming. On a practical level, as a DBA if you must move a lot of data in a short window, a weekend conversion for example, it is a good idea to try a little benchmarking to try to improve the data movement rate. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, April 11, 2002 2:29 PM To: Multiple recipients of list ORACLE-L Dennis-- The faster machine had 10 database connections, the slower, 50. Not all were used in either case. But my comparison is for inserts done by a single session, i.e., it's not the aggregate insert rate, but a direct comparison of the same insert statement in a single session on each server. As for the disk layout, I don't have that information readily available (the SA department doesn't like to let the DBA department get involved in such things!). I don't think it's significantly different, though. Thanks, Paul --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Paul - How many insert processes did you run on each system? Is the > disk > layout identical in terms of spreading across devices? > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -Original Message- > Sent: Wednesday, April 10, 2002 6:43 PM > To: Multiple recipients of list ORACLE-L > > > Greetings! > > I am trying to diagnose a performance difference between two > databases > running the same test. They are similarly configured (same SGA size, > etc.), and the servers are identical except for the number of CPUs > (server A has 4, server B has 6). > > On database A, INSERT performance is about 190 rows/second. > > On database B, INSERT performance is over 500 rows/second. > > I saw some cache buffers chains, buffer busy, and library cache latch > waits on database A while the test was running, as well as redo log > sync waits. The waits didn't seem excessive, though. I checked for > "checkpoint not complete" redo allocation messages in database A's > alert log and found none. The db_block_lru_latches parameter is set > to > one-half the number of CPUs in both machines. > > I'd much appreciate any suggestions as to what else to check. I know > that freelists can be an issue (there are multiple sessions > performing > inserts); how can I check to see if there's freelist contention? > Anything else I should investigate? > > Many TIA, > > > > > > = > Paul Baumgartel, Adept Computer Associates, Inc. > [EMAIL PROTECTED] > > > > > > __ > Do You Yahoo!? > Yahoo! Tax Center - online filing with TurboTax > http://taxes.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Paul Baumgartel > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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.com > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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).
RE: insert performance
Dennis-- The faster machine had 10 database connections, the slower, 50. Not all were used in either case. But my comparison is for inserts done by a single session, i.e., it's not the aggregate insert rate, but a direct comparison of the same insert statement in a single session on each server. As for the disk layout, I don't have that information readily available (the SA department doesn't like to let the DBA department get involved in such things!). I don't think it's significantly different, though. Thanks, Paul --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Paul - How many insert processes did you run on each system? Is the > disk > layout identical in terms of spreading across devices? > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -Original Message- > Sent: Wednesday, April 10, 2002 6:43 PM > To: Multiple recipients of list ORACLE-L > > > Greetings! > > I am trying to diagnose a performance difference between two > databases > running the same test. They are similarly configured (same SGA size, > etc.), and the servers are identical except for the number of CPUs > (server A has 4, server B has 6). > > On database A, INSERT performance is about 190 rows/second. > > On database B, INSERT performance is over 500 rows/second. > > I saw some cache buffers chains, buffer busy, and library cache latch > waits on database A while the test was running, as well as redo log > sync waits. The waits didn't seem excessive, though. I checked for > "checkpoint not complete" redo allocation messages in database A's > alert log and found none. The db_block_lru_latches parameter is set > to > one-half the number of CPUs in both machines. > > I'd much appreciate any suggestions as to what else to check. I know > that freelists can be an issue (there are multiple sessions > performing > inserts); how can I check to see if there's freelist contention? > Anything else I should investigate? > > Many TIA, > > > > > > = > Paul Baumgartel, Adept Computer Associates, Inc. > [EMAIL PROTECTED] > > > > > > __ > Do You Yahoo!? > Yahoo! Tax Center - online filing with TurboTax > http://taxes.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Paul Baumgartel > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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.com > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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). __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: insert performance
Thanks, Mohammed and Anjo, for your replies. Now my question is this: given that the table structures (freelists, etc.) are identical on the two machines, the init.ora parameters are identical for the two instances, and the machines themselves are nearly identical (one has 6 CPUs, one 4, but in neither case are the CPUs anywhere near maxed out), what could be causing the discrepancy? PB --- Mohammed Shakir <[EMAIL PROTECTED]> wrote: > data block waits is the indicator of freelists contention. I have > never > seen a freelists contention, even though I have been running 11 > processing doing inserts using 11 CPUs. I have seen library cache, > Shared pool and cache buffer chains waits. I took care of buffer busy > waits and db file sequential reads by increasing freelists, initrans > to > 11 and by partitioning the disks. In my case most of the data was > writting to the same block by as many as 9 processes. I am working on > a > > Sparc 4500 Solaris 2.1.6 with Oracle 8.1.6.0 and 8.1.7.2 > > Library Shared Pool latch contention was found due to Literal or > non-shared SQL. Check SQL_text in SQLarea to find literal SQL. Shared > Pool contention seems to be due to a very large Shared pool. I found > a > bug report that indicates that cache buffer chain problem was fixed > in > 8.1.3.4 and 9.0.1.3 > > Cache buffer chain is also an indicator of high physical and logical > I/O. You can check on that as well. > > Hope this helps. > > Shakir > > --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > > Greetings! > > > > I am trying to diagnose a performance difference between two > > databases > > running the same test. They are similarly configured (same SGA > size, > > etc.), and the servers are identical except for the number of CPUs > > (server A has 4, server B has 6). > > > > On database A, INSERT performance is about 190 rows/second. > > > > On database B, INSERT performance is over 500 rows/second. > > > > I saw some cache buffers chains, buffer busy, and library cache > latch > > waits on database A while the test was running, as well as redo log > > sync waits. The waits didn't seem excessive, though. I checked > for > > "checkpoint not complete" redo allocation messages in database A's > > alert log and found none. The db_block_lru_latches parameter is > set > > to > > one-half the number of CPUs in both machines. > > > > I'd much appreciate any suggestions as to what else to check. I > know > > that freelists can be an issue (there are multiple sessions > > performing > > inserts); how can I check to see if there's freelist contention? > > Anything else I should investigate? > > > > Many TIA, > > > > > > > > > > > > = > > Paul Baumgartel, Adept Computer Associates, Inc. > > [EMAIL PROTECTED] > > > > > > > > > > > > __ > > Do You Yahoo!? > > Yahoo! Tax Center - online filing with TurboTax > > http://taxes.yahoo.com/ > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Paul Baumgartel > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing > > Lists > > > > > 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). > > > = > Mohammed Shakir > CompuSoft, Inc. > 11 Heather Way > East Brunswick, NJ 08816-2825 > (732) 672-0464 (Cell) > (732) 257-6001 (Home) > > __ > Do You Yahoo!? > Yahoo! Tax Center - online filing with TurboTax > http://taxes.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Mohammed Shakir > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (n
RE: insert performance
Paul - How many insert processes did you run on each system? Is the disk layout identical in terms of spreading across devices? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, April 10, 2002 6:43 PM To: Multiple recipients of list ORACLE-L Greetings! I am trying to diagnose a performance difference between two databases running the same test. They are similarly configured (same SGA size, etc.), and the servers are identical except for the number of CPUs (server A has 4, server B has 6). On database A, INSERT performance is about 190 rows/second. On database B, INSERT performance is over 500 rows/second. I saw some cache buffers chains, buffer busy, and library cache latch waits on database A while the test was running, as well as redo log sync waits. The waits didn't seem excessive, though. I checked for "checkpoint not complete" redo allocation messages in database A's alert log and found none. The db_block_lru_latches parameter is set to one-half the number of CPUs in both machines. I'd much appreciate any suggestions as to what else to check. I know that freelists can be an issue (there are multiple sessions performing inserts); how can I check to see if there's freelist contention? Anything else I should investigate? Many TIA, = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: insert performance
data block waits is the indicator of freelists contention. I have never seen a freelists contention, even though I have been running 11 processing doing inserts using 11 CPUs. I have seen library cache, Shared pool and cache buffer chains waits. I took care of buffer busy waits and db file sequential reads by increasing freelists, initrans to 11 and by partitioning the disks. In my case most of the data was writting to the same block by as many as 9 processes. I am working on a Sparc 4500 Solaris 2.1.6 with Oracle 8.1.6.0 and 8.1.7.2 Library Shared Pool latch contention was found due to Literal or non-shared SQL. Check SQL_text in SQLarea to find literal SQL. Shared Pool contention seems to be due to a very large Shared pool. I found a bug report that indicates that cache buffer chain problem was fixed in 8.1.3.4 and 9.0.1.3 Cache buffer chain is also an indicator of high physical and logical I/O. You can check on that as well. Hope this helps. Shakir --- Paul Baumgartel <[EMAIL PROTECTED]> wrote: > Greetings! > > I am trying to diagnose a performance difference between two > databases > running the same test. They are similarly configured (same SGA size, > etc.), and the servers are identical except for the number of CPUs > (server A has 4, server B has 6). > > On database A, INSERT performance is about 190 rows/second. > > On database B, INSERT performance is over 500 rows/second. > > I saw some cache buffers chains, buffer busy, and library cache latch > waits on database A while the test was running, as well as redo log > sync waits. The waits didn't seem excessive, though. I checked for > "checkpoint not complete" redo allocation messages in database A's > alert log and found none. The db_block_lru_latches parameter is set > to > one-half the number of CPUs in both machines. > > I'd much appreciate any suggestions as to what else to check. I know > that freelists can be an issue (there are multiple sessions > performing > inserts); how can I check to see if there's freelist contention? > Anything else I should investigate? > > Many TIA, > > > > > > = > Paul Baumgartel, Adept Computer Associates, Inc. > [EMAIL PROTECTED] > > > > > > __ > Do You Yahoo!? > Yahoo! Tax Center - online filing with TurboTax > http://taxes.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Paul Baumgartel > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammed Shakir INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: insert performance
aul, BBW due to not enough freelists is caused by multiple processes waiting on the head of the freelist to check if there is space in the block. problems can get worse by doing array inserts .. So by having enough freelists, different processes will check on different heads of freelsits. Anjo. Paul Baumgartel wrote: > Follow-up question: can someone explain exactly why buffer busy waits > can be due to heavy insert activity when there are insufficient > freelists? I suspect that this may figure into my problem with insert > performance. Thanks! > > Paul Baumgartel > > __ > Do You Yahoo!? > Yahoo! Tax Center - online filing with TurboTax > http://taxes.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Paul Baumgartel > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: insert performance
Follow-up question: can someone explain exactly why buffer busy waits can be due to heavy insert activity when there are insufficient freelists? I suspect that this may figure into my problem with insert performance. Thanks! Paul Baumgartel __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
insert performance
Greetings! I am trying to diagnose a performance difference between two databases running the same test. They are similarly configured (same SGA size, etc.), and the servers are identical except for the number of CPUs (server A has 4, server B has 6). On database A, INSERT performance is about 190 rows/second. On database B, INSERT performance is over 500 rows/second. I saw some cache buffers chains, buffer busy, and library cache latch waits on database A while the test was running, as well as redo log sync waits. The waits didn't seem excessive, though. I checked for "checkpoint not complete" redo allocation messages in database A's alert log and found none. The db_block_lru_latches parameter is set to one-half the number of CPUs in both machines. I'd much appreciate any suggestions as to what else to check. I know that freelists can be an issue (there are multiple sessions performing inserts); how can I check to see if there's freelist contention? Anything else I should investigate? Many TIA, = Paul Baumgartel, Adept Computer Associates, Inc. [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
