Faulty Quotes 5 – Block Sizes

January 31, 2010 (Updated Feb 1, 2010)

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

The topic of deviating from the default 8KB block size in Oracle Database, or using multiple block sizes in a single database seems to surface every couple of months in the OTN forums, Oracle-L, comp.databases.oracle.server Usenet group, and similar discussion forums.  I think that I understand why.  A lot of information has been written that advocates using multiple block sizes in a single Oracle database, or using the largest possible block size to improve “full scan” or “range scan” performance.  Such information is found in blogs, news articles, discussion forums, expert sites, books, and even Oracle’s download.oracle.com website.  So, why do people ask questions about using larger than default block sizes or multiple block sizes in discussion forums if there are so many sources of information that say “just do it”.  Well, chances are that the Google (or other search engine) search that found all of the sources recommending the use of non-standard settings also found several pages where people basically stated “stop, think, understand before making any changes.”  See the Faulty Quotes 3 blog article.

So, you might be curious what my Google search found.  Is it a best practice to implement multiple block sizes in a single database, and is it a best practice to move all of your indexes to a tablespace using the largest supported block size?  (See chapter 1 of Expert Oracle Practices for a discussion on the topic of implementing “best practices”.)  In the following quotes, I have attempted to quote the bare minimum of each article so that the quote is not taken too far out of context (I am attempting to avoid changing the meaning of what is being quoted).

http://download.oracle.com/docs/cd/E13214_01/wli/docs102/dbtuning/dbtuning.html

“Oracle9i introduced a new feature that allowed a single instance of the database to have data structures with multiple block sizes. This feature is useful for databases that need the flexibility of using a small block size for transaction processing applications (OLTP); and a larger block size to support batch processing applications, decision support systems (DSS), or data warehousing. It can also be used to support more efficient access to larger data types like LOBs.”

http://www.virtual-dba.com/pdfs/Xtivia_WP_Oracle_Best_Practices_2008.pdf  (page 14)

“In Oracle databases 9i, 10g, and 11g, it is a best practice to use multiple block sizes; this allows you to tailor the block size to a specific type of access. Place tables and indexes in tablespaces sized (block size) according to access…”

http://www.oracle.com/technology/products/database/clustering/pdf/bp_rac_dw.pdf (page 19)

“Larger oracle block sizes typically give fewer index levels and hence improved index access times to data. A single I/O will fetch many related rows and subsequent requests for the next rows will already be in the data buffer. This is one of the major benefits of a larger block size. Another benefit is that it will decrease the number of splits.”

dba-oracle.com/art_so_blocksize.htm

“Because the blocksize affects the number of keys within each index block, it follows that the blocksize will have an effect on the structure of the index tree. All else being equal, large 32k blocksizes will have more keys per block, resulting in a flatter index than the same index created in a 2k tablespace.”
“As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache. Clearly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle9i and above are worth your investigation and trials in your own database.”

rampant-books.com/t_oracle_blocksize_disk_i_o.htm

“B-tree indexes with frequent index range scans perform best in the largest supported block size.  This facilitates retrieval of as many index nodes as possible with a single I/O, especially for SQL during index range scans.  Some indexes do not perform range scans, so the DBA should make sure to identify the right indexes”

praetoriate.com/t_oracle_tuning_data_buffer_pools.htm

“This is an important concept for Oracle indexes because indexes perform better when stored in large block size tablespaces.  The indexes perform better because the b-trees may have a lower height and mode entries per index node, resulting in less overall disk overhead with sequential index node access.”

remote-dba.cc/s56.htm

“Indexes want large block sizes – B-tree indexes perform best in the largest supported block size and some experts recommend that all indexes should reside in 32K block size tablespaces. This facilitates retrieval of as many index nodes as possible with a single I/O, especially for SQL performing index range scans.”
“Many DBAs make their default db_block_size 32k and place indexes, the TEMP tablespace and tables with large-table full-table scans in it, using other block sizes for objects that require a smaller fetch size.”

remote-dba.net/unix_linux/multiple_block_sizes.htm

” Large blocks – Indexes, row-ordered tables, single-table clusters, and table with frequent full-table scans should reside in tablespaces with large block sizes.”

oracle-training.cc/s54.htm

“Larger block sizes are suitable for indexes, row-ordered tables, single-table clusters, and tables with frequent full-table scans. In this way, a single I/O will retrieve many related rows, and future requests for related rows will already be available in the data buffer.”

oracle-training.cc/oracle_tips_block_sizes.htm

“Indexes want large block sizes – Indexes will always favor the largest supported blocksize. You want to be able to retrieve as many index nodes as possible in a single I/O, especially for SQL that performs index range scans.  Hence, all indexes should reside in tablespaces with a 32k block size.”

oracle-training.cc/t_oracle_multiple_buffers.htm

“One of the first things the Oracle9i DBA should do is to migrate all of their Oracle indexes into a large blocksize tablespace. Indexes will always favor the largest supported blocksize.”

http://forums.oracle.com/forums/thread.jspa?messageID=2445936

“It’s pretty well established that RAC performs less pinging with 2k blocksizes”
“Large blocks gives more data transfer per I/O call.”
“Indexes like big blocks because index height can be lower and more space exists within the index branch nodes.”

dbapool.com/articles/040902.html

“Index Branches: Larger oracle block sizes typically give fewer index levels and hence improved index access times to data .This is one of the major benefits of a larger block size.”

toadworld.com/LinkClick.aspx?fileticket=fqDqiUsip1Y=&tabid=234  (page 8 )

“In Oracle9i and Oracle10g it is a good practice to use multiple block sizes, this allows you to tailor the block size to a specific type of access. Place tables and indexes in tablespaces according to access. For single block read type OLTP access, use 8k block sizes. For full table scan access such as with data warehouses use 16-32K block sizes. For index lookups use 8-16K block sizes. For indexes that are scanned or bitmap indexes, use 16-32K block sizes.”

dbaforums.org/oracle/index.php?s=87341768e1865563322676a1bd504db6&showtopic=83&pid=133&mode=threaded&start=#entry133

“Multiuple blocksizes are GREAT, but ONLY if your database is I/O-bound… Finally, research has proved that Oracle indexes build cleaner in large blocksizes.”

searchoracle.techtarget.com/tip/Oracle-tuning-Blocksize-and-index-tree-structures

“Because the blocksize affects the number of keys within each index block, it follows that the blocksize will have an effect on the structure of the index tree. All else being equal, large 32k blocksizes will have more keys per block, resulting in a flatter index than the same index created in a 2k tablespace… You can use the large (16-32K) blocksize data caches to contain data from indexes or tables that are the object of repeated large scans.”

dbazine.com/oracle/or-articles/burleson2

“Hence, one of the first things the Oracle9i database administrator will do is to create a 32K tablespace, a corresponding 32K data buffer, and then migrate all of the indexes in their system from their existing blocks into the 32K tablespace… Indexes will always favor the largest supported blocksize.”

statspackanalyzer.com/sample.asp

“You are not using large blocksizes for your index tablespaces. Oracle research proves that indexes will build flatter tree structures in larger blocksizes.”

http://books.google.com/books?id=xxx0KAwY_ZMC&pg=PT133#v=onepage&q=&f=false

“If you have large indexes in your database, you will need a large block size for their tablespaces.”
“Oracle provides separate pools for the various block sizes, and this leads to better use of Oracle memory.”

noriegaaoracleexpert.blogspot.com/2007/08/advances-in-multiple-block-size-caches.html

“… and using multiple block caches act as an intelligent cache differentiator that automatically leverage cache performance optimization. I have successfully tested, like many other DBAs and developers, that beyond any possible SGA tuning that using multiple-block-size database can certainly improve performance through this performance approach.”

http://books.google.com/books?id=Wx6OmllCfIkC&pg=PA164#v=onepage&q=&f=false

“Simply by using the new 16K tablespace and accompanying 16K data cache, the amount of logical reads has been reduced by half.  Most assuredly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle9i and later, are worth examination and trials in the DBA’s own database.”

http://books.google.com/books?id=bxHDtttb0ZAC&pg=PA406#v=onepage&q=&f=false

“Objects that experience full scans and indexes with frequent range scans might benefit from being placed in a larger block size, with db_file_multiblock_read_count set to the block size for that tablespace.”

http://books.google.com/books?id=Uf2pb1c1H2AC&pg=RA1-PA317#v=onepage&q=&f=false

“Indexes want large block sizes: Indexes will always favor the largest supported block size… Hence, all indexes should reside in tablespaces with a 32K block size.”

dba-oracle.com/oracle_tips_multiple_blocksizes.htm (Added Feb 1, 2010):

“At first, beginners denounced multiple block sizes because they were invented to support transportable tablespaces.  Fortunately, Oracle has codified the benefits of multiple blocksizes, and the Oracle 11g Performance Tuning Guide notes that multiple blocksizes are indeed beneficial in large databases to eliminate superfluous I/O and isolate critical objects into a separate data buffer cache:

‘With segments that have atypical access patterns, store blocks from those segments in two different buffer pools: the KEEP pool and the RECYCLE pool…'”

————————-

Before deciding whether or not to implement a large block size (or a very small block size), or add either a larger or smaller than default block size tablespace, I suggest reviewing the following:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/iodesign.htm#i20394  (directly relates to Faulty Quotes 3)

“The use of multiple block sizes in a single database instance is not encouraged because of manageability issues.”

“Expert Oracle Database Architecture”
http://books.google.com/books?id=TmPoYfpeJAUC&pg=PA147#v=onepage&q=&f=false

“These multiple blocksizes were not intended as a performance or Tuning feature, but rather came about in support of transportable tablespaces…”

http://www.freelists.org/post/oracle-l/32K-block-size-tablespace-for-indexes,4

“But in most cases the administration overhead is much bigger than the performance benefit. You can easily end up with over- or undersized db_XXk_cache_size and the database can’t do anything about it. Then the performance will be better in some parts of the day and worse later on.”

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1468781700346675276

“I would not recommend going into a system planning on using multiple blocksizes – they were invented for one thing, to transport data from a transactional system to a warehouse (where you might be going from 8k OLTP to 16/32k warehouse) and to be used only to extract/transform/load the OLTP data.”

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1468781700346675276

“My block size is 4096 and my db_32k_cache_size=67108864
I want to create a tablespace with 32K and rebuild all indexes into this tablespace. These are
frequently used indexes. Do you think is there any benefit for using 32K block size in this scenerio”

“before you do something, you should have an identified goal in mind
so, tell us all – WHY would you do this? Don’t say “cause I read on some website it makes things super fast” (it doesn’t), tell us WHY you think YOU would derive benefit from this?
I do not think there is in general benefits to be gained from using multiple block size tablespaces – short of TRANSPORTING data from one block size to another for an ‘extract transform and load’ process.”

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:729373100346730466

“BUT – do not use multiple block sizes for anything other than transporting data from database A to database B where the block size in A is different from B. No silver bullets with this ‘trick’, nothing you want to do in real life. The cases whereby multiple blocksizes are useful are typically limited to benchmarks, old wives tales, and very exceptionally rare conditions.”

http://jonathanlewis.wordpress.com/2009/03/22/block-size-again/
“ORA-01555: snapshot too old” caused by large block size

“Oracle9i Performance Tuning Tips & Techniques”
http://books.google.com/books?id=59ks3deVd0UC&pg=PA9#v=onepage&q=&f=false

“Warning: Oracle development does not support the notion of using multiple block sizes for performance tuning. The nonstandard block caches are not optimized.”

http://forums.oracle.com/forums/thread.jspa?messageID=2445936

“How can I determine which block size is correct for my database.”

“Use 8k. This is right in the middle, and won’t put you in an edge condition. Call it the Goldilocks block, not to small, not to big, just right.
For both OLTP and DSS, 8k is an optimal size. I use 8k, always.
There is minimal gains to be had in messing with block sizes. Having good db design and good execution plans is a better place to worry about performance.”

Series of related articles (there are at least 5 related articles in this series where the author directly addresses many of the claimed benefits of fiddling with block sizes):
http://richardfoote.wordpress.com/category/index-block-size/

Summary of an OTN forums thread – what was likely the longest thread ever on the topic of block sizes (and very likely multiple block sizes in the same database) from June 2008.  The message thread was too large to be supported on the new OTN software due to performance reasons.  Fortunately, Jonathan Lewis obtained a copy of the thread content in a PDF file:
http://jonathanlewis.wordpress.com/2008/07/19/block-sizes/

Related to the above mentioned OTN thread:
http://structureddata.org/2008/08/14/automatic-db_file_multiblock_read_count/
http://structureddata.org/2008/09/08/understanding-performance/

I posted a number of test cases in the above mentioned OTN thread where I simulated some of the activity in a data warehouse, and activity in an OLTP type database.  To a large extent, the performance was very close to being identical in the databases with the default 8KB and 16KB tablespaces, with just a few exceptions.  As I recall, the 16KB database encountered performance problems when a column with a NULL value was updated, and when a rollback was performed.

Below you will find the scripts to reproduce my test cases that appeared in the above mentioned OTN thread, and the performance results that I obtained.  The OLTP test required roughly 10-12 hours to complete:
Block Size Comparison (save with a .XLS extension and open with Microsoft Excel).

I guess the message is that you should verify that the swimming pool contains water before diving in head first.