||Oracle Tips by Burleson
Large Blocks and Oracle Indexes blocksize
When an SQL query retrieves a result set from
an Oracle table, it is probably gathering the table rows through an
index. Many Oracle tuning experts have recommended that databases
created prior to Oracle9i be re-defined with large block sizes. The
performance gains realized from switching a 2K block size database to
an 8K block size have perplexed many.
Resistance to increasing the block size was
typically expressed as “Why will moving to a large block size improve
a database that only randomly fetches small rows”? The answer to this
question is not so simple, but it involves indexes.
Many DBAs fail to consider index trees and the
index range scan process of sequential retrieval of the index when
choosing a block size. Nested loop joins usually evidence an index
range scan, and the vast majority of rows are accessed using indexes.
Locating indexes in larger size blocks reduces
I/O and further improves throughput for the entire database because
index range scans gather index nodes sequentially. If this is the
case, why not just create the database with large block sizes and
forget about multiple block sizes?
The answer here is also complex. RAM buffer
memory cannot be utilized with maximum efficiency unless the tables
are segregated according to the distribution of related data between
them. In allocating block sizes, we can still apply the same general
rules, with some modification in our understanding.
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.