Christofer, maybe it is not black and white, though. Bigger block size means more latch contention on cache buffers chains, for example. That's why one may play around with minimize record_per_block or artifically high pctfree. Both mean waste disk space and _memory_. Many of larger block benefits, mentioned by Jonathan, will be lost if this is the case. Fortunately, 9i allows variable block size (BTW, did anybody test this feature efficiency?). And if you don't mind, how much is "much more efficient" from your experience? That's clear, why it should be more efficient, but HOW much?
Thanks, Vadim -----Original Message----- Sent: Wednesday, October 03, 2001 11:00 AM To: Multiple recipients of list ORACLE-L I disagree in the 2k for OLTP as well, for similar reasons Jonathan mentioned, as well as a few of the obvious. Most OLTP are not PERFECTLY tuned to only do indexes scans either. And indexes are much more efficient on the larger block sizes as well "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: Tuesday, October 02, 2001 8:55 PM To: Multiple recipients of list ORACLE-L Hi Jonathan, Sweeping statement...maybe. It all depends on your application. That's why I put an emphasis on his/her application (meaning both physical structure and data) requirements. As a GENERAL rule of thumb, I (personally) suggest (if possible) 2k for OLTP databases. It's like if you ask me what car to buy, I would say a "Honda". But if you were to tell me that you need speed, style and sex on wheels, I would suggest a Benz. You and I, (especially you!) can argue the benefits/disadvantages of using 2k block size...Which I don't care to do. I've put the benefits/reasons of a 2k blocksize at the bottom of this reply just to show some reverse examples of the ones you replied with. Some of the reasons should look familiar to you! ;) Regards, Satar 1. The OLTP app has lots of small rows (<2k) 2. The OLTP app does massive scattered reads (you don't full scan in OLTP). It reads a block here, a block there based on some primary key (after doing lots of scattered reads to walk an index structure) 3. The OLTP app has 1,000 connected users -- each wants their current row(s) they are using in the cache. 4. A smaller block size in a database that does lots of scattered IO's (eg: an OLTP system) and random keyed reads can definitely benefit from smaller blocksizes. 5. The indexes for the critical access paths fit exactly into an N-layer tree and allow an optimum buffering strategy when memory is limited. 6. In almost all cases the data for a single index value will be (significantly) less than 2K and will all be found in the same block. 7. Memory is at a premium. --- Jonathan Lewis <[EMAIL PROTECTED]> wrote: > > That's a fairly sweeping statement to make without > any justification - after all, at 2K: > > The block header is a much larger percentage > of the block size - so you lose space. > > The probability of wasting space from the > PCTFREE > setting increases - so you lose space. > > The memory overhead due to x$bh is fixed per > block, > so you use more memory to hold the same volume > (block size x block count) of data. > > Index depth may increase through having fewer > entries per branch block. > > Latch activity is likely to increase on range > scans > because leaf blocks hold fewer entries - you get > the same effect on tables with clustered data - > and the effectiveness of IOTs will particularly > be > reduced. > > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Host to The Co-Operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > Author of: > Practical Oracle 8i: Building Efficient Databases > > -----Original Message----- > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > Date: 02 October 2001 21:56 > > > |If your application allows it, and if the > Application > |will not change in the future, then use a 2k block > |size for OLTP database. > | > |If you are not sure on the application needs, then > |stick with 4k to be safe. > | __________________________________________________ Do You Yahoo!? Listen to your Yahoo! Mail messages from any phone. http://phone.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: satar naghshineh 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: Christopher Spence 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: Vadim Gorbounov 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).
