Hi Christopher, Like I said, Oracle experts can argue this issue until they are blue in the face, kinda like the Certification debate. Without any information on the data or application, I suggest a 2k block size. Everyone is entitled to thier own opinion, and I hope the author of the original post becomes successful in choosing his/her block size.
By the way, you are agreeing and disagreeing with Jonathan Lewis at the same time. The index efficiency on small block size is Jonathan's point he made a couple of years ago when he was stating why he recommends a 2k block size on an OLTP type database. The other index point (scanning an index on a small block size) is from Thomas Kyte on the same thread...If you disagree with him, you can write him an e-mail so you can correct him. I hope you can appreciate the irony. Regards, Satar PS Nice website, I like the goth look! Now you should play some Bauhaus or Christian Death in the background. ;) --- Christopher Spence <[EMAIL PROTECTED]> wrote: > 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 > > > Christopher R. Spence > Oracle DBA > > -----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 > > __________________________________________________ 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).
