| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Moving slightly OT - Commit boundary <-> Stripe Unit Size Co
Dennis,
I think you need to change your tag line to:
Dennis Williams
DBA, 40%OCP 100%DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
Cheers!
RF
-----Original Message-----
To: Multiple recipients of list ORACLE-L
Sent: 2/13/2003 9:04 AM
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.
dwilliams_at_lifetouch.com
-----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.
dwilliams_at_lifetouch.com
-----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: VIVEK_SHARMA_at_infosys.com
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: ListGuru_at_fatcity.com (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).
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: ListGuru_at_fatcity.com (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).
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: ListGuru_at_fatcity.com (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). Received on Thu Feb 13 2003 - 10:19:22 CST
 |
 |