||Oracle Tips by Burleson
Oracle blocksize and performance
Small block size
Tables containing small rows that are accessed
randomly should be placed into tablespaces with smaller block sizes.
This way, more of the buffer RAM remains available to store rows from
other tables that are referenced frequently.
Larger block size
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.
Some objects that may benefit from a larger
blocksize (16K or 32K) include:
indexes (because of the serial nature of index range scans)
Large tables that are the target of full table scans
Tables with large object (BLOB, CLOB, etc.) data
Tables with large row sizes that might blossom into chained/migrated
Temporary tablespaces used for sorting
The simple goal is to maximize the amount of
RAM available to the data buffers by setting the block size according
to the amount of I/O the table or index sees. Smaller block sizes are
appropriate for randomly accessed small rows, while larger blocks are
more suitable for rows sequentially accessed.
To illustrate, suppose a query retrieves 100
random 80 byte rows from Oracle. Since the rows are randomly accessed,
we can safely assume that no two rows exist on the same block,
implying that it is necessary to read 100 blocks to fulfill the task.
If the blocks are sized 16K, the
db_16k_cache_size buffer will need 16 MB (16K * 100) of RAM. If
the blocks are instead 2K, we only need 2 MB of RAM in the buffer for
the 100 I/Os. Using the smaller block size would save 14 MB of RAM for
this query alone, RAM that will be available elsewhere to hold other
text is an excerpt from "Creating
a Self Tuning Oracle Database", by Rampant TechPress. It is only
$9.95 and all scripts in this tips can be immediately downloaded.
Complete Oracle Script Collection Available
shares their personal arsenal of Oracle data dictionary
scripts in this comprehensive download. With decades of
experience using Oracle monitoring scripts and Oracle tuning
scripts, we shares our secrets for navigating the Oracle
Packed with more than 680 ready-to-use Oracle scripts, this is the
definitive collection for every senior Oracle DBA.
would take man-years to develop these scripts from scratch,
making this download the best value in the Oracle industry.