 |
|
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.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
