Re: Insert performance
Hi! Search for "surrogate key" in google for example. You have to modify your generated primary key valuesto 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
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
Rick, Find out what kind(s) of buffer busy waits you have. MetaLink, Anjo Kolks YAPP paper, or Steve Adamss 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 Clinic101 in Sydney - Hotsos Symposium 2004 March 710 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
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).
Re: Insert performance
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-- 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
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
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
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). 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
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 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
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 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). __ 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
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).
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).
