||Oracle Tips by Burleson
Maximizing Oracle Block Space Usage
We will have to manage the RAM that is
allocated to the data buffers until memory becomes cheap enough to
cache the entire database. Properly allocating tables and indexes
according to block size is a balancing act. If the data blocks are set
too large, valuable buffer space is wasted holding row data that will
never be referenced. If the blocks are set too small, Oracle is forced
to perform more disk I/O to satisfy a query.
Here are some further general guidelines for
allocating data block sizes:
Segregate large-table full-table scans – Tables subject to
large-table, full-table scans will benefit from the largest
supported block size. They should be placed in a tablespace with the
largest block size.
db_recycle_cache_size carefully – If db_cache_size is
not set to the largest supported block size, do not use the
db_recycle_cache_size parameter. Instead, create a
db_32k_cache_size (or whatever the max is), and assign all
tables and indexes subject to large-table, full-table scans to the
largest data buffer in the database.
Bear in mind that the data dictionary will use
the default block size. Make sure that the dictionary (e.g. the SYSTEM
tablespace) is always fully cached in a data buffer pool. The block
size, per se, of the dictionary is less important than having enough
RAM in the SYSTEM tablespace buffer to fully cache all of the
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.