Important Oracle Blocksize Performance bugs
In general, different blocksizes can improve performance in a
variety of ways for segments with specialized I/O requirements, but
they are normally reserved for large mission-critical databases with
high transaction loads:
- Contention reduction - small rows in a large block
perform worse under heavy DML than large rows in a small
blocksize.
- Faster updates - Heavy insert/update tables can see
faster performance when segregated into another blocksize which
is mapped to a small data buffer cache. Smaller data buffer
caches often see faster throughput performance.
- Reduced Pinging - RAC can perform far faster with
smaller blocksizes, reducing cache fusion overhead.
- Less RAM waste - Moving random access small row
tables to a smaller blocksize (with a corresponding small
blocksize buffer) will reduce buffer waste and improve the
chance that other data blocks will remain in the cache.
- Faster scans - Tables and indexes that require full
scans can see faster performance when placed in a large
blocksize
The selective use of different
blocksizes can result in stunning performance gains for objects with
specialized I/O needs, but careful testing is require before
implementing multiple blocksizes, and it is critical to test
different blocksizes under real-world workloads, with a multi-user
test on a representative copy of the production database.
Inadequate testing?
Oracle does not reveal the depth of their quality assurance
testing, but many Oracle customers believe that Oracle does complete
regression testing on major features of their software, such as
blocksize. However, Oracle ACE Director Daniel Morgan, says
that “The
right size is 8K because that is the only size Oracle tests”, a
serious allegation, given that the Oracle documentation, Oracle
University and MOSC all
recommend non-standard blocksizes under special circumstances:
-
Large blocks gives more data transfer per
I/O call.
-
Larger blocksizes provides less
fragmentation (row chaining and row migration) of large objects
(LOB, BLOB, CLOB)
-
Indexes like big blocks because index
height can be lower and more space exists within the index
branch nodes.
-
Moving indexes to a larger blocksize saves
disk space. Oracle says "you will conserve about 4% of data
storage (4GB on every 100GB) for every large index in your
database by moving from a 2KB database block size to an 8KB
database block size."
So, does Oracle really not do testing with non-standard
blocksizes? Oracle ACE Director Morgan says that he was
quoting
Bryn Llewellyn of Oracle Corporation:
“Which brings us full circle to the statement Brynn
made to me and that I have repeated several times in this
thread. Oracle only tests 8K blocks.”
While Oracle does not release details of their testing with
multiple blocksizes, there are many reports of important performance
bugs relating to different blocksizes, some dating back to Oracle9i.
If true, these allegations might make new shops think twice about
deploying software that has not been properly tested.
Oracle guru
Steve Karam recently achieved a 20x performance gain from moving
a database from a 16k blocksize to a 4k blocksize, and there are
other industry reports that there are significant bugs in Oracle
relating to blocksize, especially when used with large blocksizes
and bitmap freelists (ASSM).
- There may be an
important bug in 16k blocksizes when used with ASSM which
has been unfixed since Oracle 9i.
- This
OTN thread suggests a bug in Windows servers running 16k
blocksizes.
- Blogger David Aldridge also noted a blocksize bug that is exclusive
to 32k blocksizes:
"I have previously encountered bugs on bitmap index
corruption on a 32kb data segment blocksize that made me
cautious of the risks of using extreme values for limited
benefits”. It was a 32kb bug, not a 16kb bug."
In sum, different blocksizes can work wonders under certain
circumstances, but special care must be taken to ensure that you run
proper benchmark tests before deploying a non-standard Oracle
blocksize.
To learn more, see my related notes on Oracle blocksize
performance:
-
The
benefits of using multiple blocksizes
-
Vendor notes on multiple block sizes
-
A
simple example of using multiple block sizes
-
DBA
reports of differing response time with different block sizes
-
Are
multiple block sizes right for your database?
|
