RE: Commit boundary - Stripe Unit Size Co-relation
Dennis , List A Slight Correction - The Batch Transaction does a COMMIT after INSERTS/UPDATEs Corresponding to 300 Logical Transactions . 5K is the Size of ONE Logical Transaction . Yes , The Batch job is pounding Banking Interest Transactions (Data) after Calculation (CPU-intensive) of the respective interest Amounts. Thanks -Original Message- Sent: Thursday, February 13, 2003 8:34 PM To: Multiple recipients of list ORACLE-L Vivek - I just wanted to make sure that your tests accurately reflect your production situation. The easiest tests to construct are the simplest ones. I've seen people draw wrong conclusions from those tests and even widely publish those conclusions. :-( Your original question just asked how commits related to disk writes. I just wanted to make sure you understood something about the method Oracle uses to decide when to write a block to disk. If you are just running a batch job that pounds inserts into Oracle, that is a really different situation from having many users and jobs that are doing many different things at the same time. My experience has been that those other activities very strongly affect Oracle's pattern of writing inserts and updates to disk. Mainly I have seen the insert or update job slow down a lot because it must wait for free disk blocks. Make sure you are measuring the wait statistics as you try these different tests. This also relates to the tuning of your disk subsystem by setting the stripe size. If you are just doing continual writes or updates, then it makes sense that a larger stripe size may be more efficient. And if that truly represents your production environment, go for it. There are also interactions between database block size, the operating system block size, and the disk subsystem stripe size. I have seen that discussed on this list, but I have no personal experience. ;-) Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 13, 2003 12:39 AM To: Multiple recipients of list ORACLE-L Dennis , Connor , List Further a very vague Qs. For Batch Jobs , we get extremely DIFFERENT performances when using DIFFERENT Stripe Unit Sizes of 4K , 64 K , 128K , 512K , 1M , 2M WITH 128K performing the BEST. Both SELECTs UPDATEs Hang almost indefinitely with 512K , 1M , 2M Stripe Unit Sizes . Average Size Data Inserted/Updated is 5K approx. acrross 7 Tables for one Application Transaction Thus there Seems Some Co-relation between SELECT/UPDATE/INSERT from Datafiles Different Underlying Stripe Unit Sizes . Any Comments ? Thanks -Original Message- Sent: Wednesday, February 12, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Vivek - Just to add to Connor's statements (wow am I being rash here), Oracle's strength is that it's architecture disconnects transactions from disk writes. On one hand, block may be modified several times before being written to disk (hot block, for instance). On the other hand, Oracle may need buffer space and write a block to disk before a transaction commits. But Oracle keeps track of all this and can straighten everything out if the transaction is rolled back or the system crashes. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 12:04 AM To: Multiple recipients of list ORACLE-L CASE - If Size of 1 INSERT/UPDATE Statement = 1K Stripe Unit Size is 128 K ? How will 1 COMMIT issued after 300 1K INSERT/UPDATE Statements DIFFER from 1 COMMIT issued after EACH 1K INSERT/UPDATE Statement with respect to Writing to the datafiles on the Underlying Striped Volume ? If 1 INSERT Statement Data is Written to the 1st Disk (say) of the Striped Volume , will a Repeat of the SAME INSERT Statement Write to a Different Underlying Disk of the same Striped Volume within the SAME Segment Extent ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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
RE: Commit boundary - Stripe Unit Size Co-relation
Andrew , List How does setting the parameters HASH_MULTIBLOCK_IO_COUNT and SORT_MULTIBLOCK_READ_COUNT affect the SQL Queries / Execution plans of the Optimizer ? Any experiences ? Thanks indeed -Original Message- Sent: Thursday, February 13, 2003 1:32 PM To: LazyDBA.com Discussion At the risk of talking this topic to death... Stripe size can be very important. The 'best' stripe size is usually the same as the multiple of the parameters DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT, although you might want to experiment with a value half or double this depending on your application. Also, set values of HASH_MULTIBLOCK_IO_COUNT and SORT_MULTIBLOCK_READ_COUNT so that these (x DB_BLOCK_SIZE) are related to the stripe size. The other factor that can affect things badly is the number of disks in the stripe set. A 'large' number of disks (eg 16) can severely impact write performance, but should be OK for read (each disk will store 1 8k Oracle block). Equally, a very small number of disks (eg 2 for stripe or 3 for RAID-5) is poor because of lower I/O throughput. You need to experiment to get the best, but the general recommendation is for 4 to 6 data disks per set (ie 5 to 7 for RAID5). Since Vivek's system performed best with 128K stripe size, I'd suggest that the defaults for the parameters are in use (eg DB_BLOCK_SIZE = 8192 and DB_FILE_MULTIBLOCK_READ_COUNT = 16). -Original Message- Sent: 13 February 2003 06:43 To: LazyDBA.com Discussion Dennis , Connor , List Further a very vague Qs. For Batch Jobs , we get extremely DIFFERENT performances when using DIFFERENT Stripe Unit Sizes of 4K , 64 K , 128K , 512K , 1M , 2M WITH 128K performing the BEST. Both SELECTs UPDATEs Hang almost indefinitely with 512K , 1M , 2M Stripe Unit Sizes . Average Size Data Inserted/Updated is 5K approx. acrross 7 Tables for one Application Transaction Thus there Seems Some Co-relation between SELECT/UPDATE/INSERT from Datafiles Different Underlying Stripe Unit Sizes . Any Comments ? Thanks -Original Message- Sent: Wednesday, February 12, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Vivek - Just to add to Connor's statements (wow am I being rash here), Oracle's strength is that it's architecture disconnects transactions from disk writes. On one hand, block may be modified several times before being written to disk (hot block, for instance). On the other hand, Oracle may need buffer space and write a block to disk before a transaction commits. But Oracle keeps track of all this and can straighten everything out if the transaction is rolled back or the system crashes. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 12:04 AM To: Multiple recipients of list ORACLE-L CASE - If Size of 1 INSERT/UPDATE Statement = 1K Stripe Unit Size is 128 K ? How will 1 COMMIT issued after 300 1K INSERT/UPDATE Statements DIFFER from 1 COMMIT issued after EACH 1K INSERT/UPDATE Statement with respect to Writing to the datafiles on the Underlying Striped Volume ? If 1 INSERT Statement Data is Written to the 1st Disk (say) of the Striped Volume , will a Repeat of the SAME INSERT Statement Write to a Different Underlying Disk of the same Striped Volume within the SAME Segment Extent ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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: Commit boundary - Stripe Unit Size Co-relation
Vivek - I just wanted to make sure that your tests accurately reflect your production situation. The easiest tests to construct are the simplest ones. I've seen people draw wrong conclusions from those tests and even widely publish those conclusions. :-( Your original question just asked how commits related to disk writes. I just wanted to make sure you understood something about the method Oracle uses to decide when to write a block to disk. If you are just running a batch job that pounds inserts into Oracle, that is a really different situation from having many users and jobs that are doing many different things at the same time. My experience has been that those other activities very strongly affect Oracle's pattern of writing inserts and updates to disk. Mainly I have seen the insert or update job slow down a lot because it must wait for free disk blocks. Make sure you are measuring the wait statistics as you try these different tests. This also relates to the tuning of your disk subsystem by setting the stripe size. If you are just doing continual writes or updates, then it makes sense that a larger stripe size may be more efficient. And if that truly represents your production environment, go for it. There are also interactions between database block size, the operating system block size, and the disk subsystem stripe size. I have seen that discussed on this list, but I have no personal experience. ;-) Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 13, 2003 12:39 AM To: Multiple recipients of list ORACLE-L Dennis , Connor , List Further a very vague Qs. For Batch Jobs , we get extremely DIFFERENT performances when using DIFFERENT Stripe Unit Sizes of 4K , 64 K , 128K , 512K , 1M , 2M WITH 128K performing the BEST. Both SELECTs UPDATEs Hang almost indefinitely with 512K , 1M , 2M Stripe Unit Sizes . Average Size Data Inserted/Updated is 5K approx. acrross 7 Tables for one Application Transaction Thus there Seems Some Co-relation between SELECT/UPDATE/INSERT from Datafiles Different Underlying Stripe Unit Sizes . Any Comments ? Thanks -Original Message- Sent: Wednesday, February 12, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Vivek - Just to add to Connor's statements (wow am I being rash here), Oracle's strength is that it's architecture disconnects transactions from disk writes. On one hand, block may be modified several times before being written to disk (hot block, for instance). On the other hand, Oracle may need buffer space and write a block to disk before a transaction commits. But Oracle keeps track of all this and can straighten everything out if the transaction is rolled back or the system crashes. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 12:04 AM To: Multiple recipients of list ORACLE-L CASE - If Size of 1 INSERT/UPDATE Statement = 1K Stripe Unit Size is 128 K ? How will 1 COMMIT issued after 300 1K INSERT/UPDATE Statements DIFFER from 1 COMMIT issued after EACH 1K INSERT/UPDATE Statement with respect to Writing to the datafiles on the Underlying Striped Volume ? If 1 INSERT Statement Data is Written to the 1st Disk (say) of the Striped Volume , will a Repeat of the SAME INSERT Statement Write to a Different Underlying Disk of the same Striped Volume within the SAME Segment Extent ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: Commit boundary - Stripe Unit Size Co-relation
Vivek - Just to add to Connor's statements (wow am I being rash here), Oracle's strength is that it's architecture disconnects transactions from disk writes. On one hand, block may be modified several times before being written to disk (hot block, for instance). On the other hand, Oracle may need buffer space and write a block to disk before a transaction commits. But Oracle keeps track of all this and can straighten everything out if the transaction is rolled back or the system crashes. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 12:04 AM To: Multiple recipients of list ORACLE-L CASE - If Size of 1 INSERT/UPDATE Statement = 1K Stripe Unit Size is 128 K ? How will 1 COMMIT issued after 300 1K INSERT/UPDATE Statements DIFFER from 1 COMMIT issued after EACH 1K INSERT/UPDATE Statement with respect to Writing to the datafiles on the Underlying Striped Volume ? If 1 INSERT Statement Data is Written to the 1st Disk (say) of the Striped Volume , will a Repeat of the SAME INSERT Statement Write to a Different Underlying Disk of the same Striped Volume within the SAME Segment Extent ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: Commit boundary - Stripe Unit Size Co-relation
Dennis , Connor , List Further a very vague Qs. For Batch Jobs , we get extremely DIFFERENT performances when using DIFFERENT Stripe Unit Sizes of 4K , 64 K , 128K , 512K , 1M , 2M WITH 128K performing the BEST. Both SELECTs UPDATEs Hang almost indefinitely with 512K , 1M , 2M Stripe Unit Sizes . Average Size Data Inserted/Updated is 5K approx. acrross 7 Tables for one Application Transaction Thus there Seems Some Co-relation between SELECT/UPDATE/INSERT from Datafiles Different Underlying Stripe Unit Sizes . Any Comments ? Thanks -Original Message- Sent: Wednesday, February 12, 2003 8:54 PM To: Multiple recipients of list ORACLE-L Vivek - Just to add to Connor's statements (wow am I being rash here), Oracle's strength is that it's architecture disconnects transactions from disk writes. On one hand, block may be modified several times before being written to disk (hot block, for instance). On the other hand, Oracle may need buffer space and write a block to disk before a transaction commits. But Oracle keeps track of all this and can straighten everything out if the transaction is rolled back or the system crashes. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 12, 2003 12:04 AM To: Multiple recipients of list ORACLE-L CASE - If Size of 1 INSERT/UPDATE Statement = 1K Stripe Unit Size is 128 K ? How will 1 COMMIT issued after 300 1K INSERT/UPDATE Statements DIFFER from 1 COMMIT issued after EACH 1K INSERT/UPDATE Statement with respect to Writing to the datafiles on the Underlying Striped Volume ? If 1 INSERT Statement Data is Written to the 1st Disk (say) of the Striped Volume , will a Repeat of the SAME INSERT Statement Write to a Different Underlying Disk of the same Striped Volume within the SAME Segment Extent ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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).
