Oracle Multiple data cache buffers
Oracle Tips by Burleson Consulting
While multiple blocksizes are new to Oracle, I?ve been using them
since the 1980?s (In IDMS) where the benefit of buffer segregation
and I/O reduction are well-understood. If you are planning to
try-out multiple blocksizes, first make sure that your database is
using direct I/O.
From the research of author Robin Schumacher, we now
know that moving Oracle indexes to a larger blocksize can make more
efficient use of limited RAM regions (db_cache_size,
db_32k_cache_size, etc.) and the intelligent
objects to reduce logical I/O (consistent gets) for
Because the blocksize affects the number of keys within each index
block, it follows that the blocksize will have an effect on the
structure of the index tree. All else being equal, large 32k
blocksizes will have more keys per block, resulting in a flatter
index than the same index created in a 2k tablespace.
Today, most Oracle tuning experts utilize the multiple blocksize
feature of Oracle because it provides buffer segregation and the
ability to place objects with the most appropriate blocksize to
reduce buffer waste. Some of the world record
Oracle benchmarks use
very large data buffers and multiple blocksizes.
According to an
article by Christopher Foot, author of the
OCP Instructors Guide for Oracle DBA Certification, larger block
sizes can help in certain situations:
"A bigger block size means more space for key storage in the
branch nodes of B-tree indexes, which reduces index height and
improves the performance of indexed queries."
In any case, there appears to be evidence that block size affects
the tree structure, which supports the argument that data blocks
affect the structure of the tree.
Today, MOSC notes that the multiple blocksize parameters are
among the most important in Oracle tuning, and noted experts such as
Robin Schumacher has demonstrated that Oracle indexes will build
more-optimal b-tree structures within a large blocksize. We also see
end-user reports showing
I/O reductions of 20% with this simple ?Oracle
Here is one real-world comment about index segregation into a larger
"My favourite recent article was on
32KB indexes - Our client (200GB+) saw a 20% reduction in I/O from
this simple change... "
The server had 8 CPUs 32gb RAM with db_cache_size = 3g and
db_32k_cache_size = 500mb. The database was over 200 gigabytes.
Technical Services Manager EMEA
Eagle Investment Systems Corp.
However, widespread acceptance of using
multiple blocksizes has been hindered because the I/O reduction
cannot be ?proven? using simple SQL*Plus scripts on personal
computers and because multiple blocksizes were originally created to
support transportable tablespaces:
For example, some Research DBA?s attempt to prove the Rule-of-thumb
that ?Indexes run better in a large blocksize? using small,
artificial single-user experiments. They falsely suggest that
multiple blocksizes are unlikely to help in a real-world database.
Minimal difference in elapsed time. . . . So the number of waits
has halved, but performance is not much changed. Queries against
both tablespaces are performing pretty much the same as before.
Some Oracle researchers say that using
multiple blocksizes is ?Voodoo? tuning, for some interesting
1 - Because multiple blocksizes were never
intended as a performance tool.
2 - Because they cannot replicate the
performance benefit on a personal computer.
And bear in mind I stress that you should TEST the use of
multiple block sizes. They were actually only invented to make
transportable tablespaces work properly. So you are pushing them
into realms they were never really intended for if you use them as a
performance tuning tool. And that's despite one well-known leading
author claiming all indexes should be rebuilt into 32K block size
tablespace (a lovely example of totally Voodoo Tuning advice)