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).
