| 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: Veritas Quickio and DB_BLOCK_SIZE
Gaja, you have a unique gift of ending a thread ;P
"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-----Original Message-----
Sent: Thursday, October 04, 2001 4:30 PM
To: Multiple recipients of list ORACLE-L
Hi Satar & list,
To add to the issues and concerns that Jonathan has already so eloquently outlined, let me add a key factor that needs to be considered.
I/O tuning fundamentals require us to ensure that the filesystem blocksize = db_block_size. The default filesystem blocks size in Veritas is 1K and it is more than likely that almost every Veritas filesystem that is out there is in fact created with an 1K block size. This is true even though we are talking about Quick I/O which works on a Veritas-simulated raw device.
At any rate, creating an database (regardless whether it is OLTP or otherwise) with a db_block_size of anything other than the filesystem block size is a receipe for chronic I/O bottlenecks.
For example, if db_block_size were 2K and the filesystem block size were 8K, every 2K worth of I/O requested by Oracle, will actually result in the I/O sub-system performing 8K worth of I/O (essentially your system will be doing 4 times more I/O than what is required).
On the flipside, if you have db_block_size at 8K and the filesystem block size at 2K, you end up with a scenario, where every 8K I/O request by Oracle will cause 4 filesystem blocks to be read. When applications perform enough "single-block" index range-scans, it will cause the read-ahead algorithm to accidentally engage, resulting in 128K or even 256K worth pre-fetches, which results in "wasted I/O capacity". From a "real life" perspective, you will rarely find "true OLTP" systems these days. Most systems out there are hybrid in nature, OLTP during the day and DSS at night. And that is a huge factor to consider.
There have been many studies that have been done to measure and benchmark query performance, load time, index creation and transaction rate for various values of db_block_size. In every published study, it has been proved beyond a shred of doubt that each increase of db_block_size provides approximately 40% increase in performance (especially in query execution and large loads, which are significant for the batch window). Further, the height of an index is another "significant factor" in I/O performance during index scans, and it is dependent on db_block_size. The smaller the value of db_block_size, the larger is the "number of read system calls" Oracle will have to perform.
Bottom line, I personally will never create a database with a db_block_size less than 8K, regardless of whether we use Veritas or not, given the "hybrid" nature of most databases today. More importantly, the values of filesystem blocksize and db_block_size needs to be kept in strict equality. Failure to do so will result in severe I/O performance problems "by design".
While the 8K block size minimum is good for most hybrid systems of today, applications that are purely DSS, the recommendation of picking the largest possible db_block_size on that platform still holds good. When it doubt, go with the larger block size, as it is much easier to deal with block-level and row-level contention by proactively configuring INITRANS, MAXTRANS, PCTFREE and PCTUSED. Contention for the cache buffer chain and cache buffer lru latches can be dealt with by setting the relevant init.ora parameters.
In closing, I want to use the "car anology" in this thread - it is not relevant whether you recommend a Honda or a Benz(regardless of one's personal definition of "sex on wheels") when someone asks for a car recommendation. What matters is that you recommend a car that does not have an inherent "design problem".
Hope that helps,
Gaja
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: oraperfman_at_yahoo.com 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: cspence_at_FuelSpot.com 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: 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 Oct 04 2001 - 15:22:03 CDT
 |
 |