Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Using larger blosksizes

Oracle Tips by Burleson 

Note: Oracle technology is always evolving, so make sure to review the latest notes on the benefits of large blocksizes for indexes. 

See The 2008 consensus on multiple blocksizes. for more current tips and techniques.

Large Blocks and Oracle Indexes

Prior to Oracle9i, many Oracle tuning experts recommended that a database be re-defined with a larger blocksize. Many people were mystified when a database with a 2K block size was increased to an 8K block size and the entire database ran faster. A common justification for resisting a block size increase was “This database randomly fetches small rows. I can’t see why moving to a larger block size would improve performance.” So, then, what explains the performance improvement with larger block sizes?

When choosing a block size, many DBAs forget about the index trees and how Oracle indexes are accessed sequentially when doing an index range scan.  An index range scan is commonly seen in nested loop joins, and the vast majority of row access involved indexes.

Because index range scans involve gathering sequential index nodes, placing the indexes in a larger block size reduces disk I/O and improves throughput for the whole database.

So then, why not create our entire Oracle database with large block sizes and forget about multiple block sizes?  The answer is not simple.  In order to fully utilize the RAM memory in the data buffers, you must segregate tables according to their distribution of related data.

* Small blocks - Tables with small rows that are accessed in a random fashion should be placed onto tablespaces with small block sizes. With random access and small block sizes, more of the RAM in the data buffer remains available to hold frequently referenced rows from other tables.

* Large blocks – Indexes, row-ordered tables, single-table clusters, and table with frequent full-table scans should reside in tablespaces with large block sizes. This is because a single I/O will fetch many related rows and subsequent requests for the “next” rows will already be in the data buffer.

The goal here is simple; we want to maximize the amount of available RAM memory for the data buffers by setting the block sizes according to the amount of I/O experienced by the table or index.  Random access of small rows suggests small block sizes, while sequential access of related rows suggests large block sizes.

For example, consider a query that accesses 100 random 80-byte rows from Oracle.  Since the accesses are random, we can assume that no two rows exist on the same block, and that 100 block reads are required to access the result set.

If we have 16k blocks, then we would need 16 meg (16k * 100) of RAM space in the db_16k_cache_size data buffer. If we use 2k blocks, then our 100 I/Os only use 2 meg (2k * 100) in the data buffer.  For this query, we would have saved 14 megabytes of RAM to hold other row data.

The above is an excerpt from the "Oracle9i UNIX Administration Handbook" by Oracle press, authored by Donald K. Burleson.


Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA



Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks

Hit Counter