Oracle tuning Guru achieves 20x performance
improvement
June 17, 2008
Steve Karam, the Oracle wunderkind (the world's youngest Oracle
ACE and Oracle Certified Master), has published an astute case study
describing how he tuned a batch update down from 45 minutes to only
10 seconds!
>> Read "Does
Size Matter? by Steve Karam, OCM, Oracle ACE
In order to have a statistically valid test, Karam used a the
real-world workload, using identical server, disk and instance
settings. Karam notes how moving a high-DML table to a smaller
blocksize resulted in a 20x throughput improvement:
"By going from a 16k blocksize to a 4k blocksize with all
other things being equal, we experienced roughly a twenty times
improvement."
Steve is now investigating further areas in the environment that
could benefit from block optimization, targeting frequently used and
highly concurrent segments as candidates for a smaller blocksize.
Let's take a closer look at the use of multiple blocksizes to reduce
waste and improve performance throughput.
WARNING - THE FOLLOWING INFORMATION IS FOR EXPERIENCED
DBA'S ONLY:
Tuning with multiple blocksizes is an
advanced DBA tuning technique that requires a complete
understanding of the I/O stack. In general, multiple
blocksizes are used in large mission critical databases with
thousands of users and hundreds of transactions per second, and
it's a technique that requires careful testing and validation.
Multiple blocksizes is also complex because the DBA must automate the management of
multiple data buffer caches. If you are considering
implementing multiple blocksizes, you will need to carefully testing and
validate the benefits using real-world workloads under representative
production conditions. Savvy Oracle professionals will use
a recognized database benchmarking simulation technique (See the book
Oracle Benchmarking
for details) or use the new Oracle 11g SQL
Performance Analyzer (SPA),
Oracle's official testing tool for verifying the impact of
system-wide changes.
For complete details on I/O tuning for Oracle, see my tips on
using multiple blocksizes for details are the book "Oracle
Tuning: The Definitive Reference" for a complete methodology for
managing your I/O subsystem in Oracle.
All about Oracle blocksizes
While not every shop is going to experience a 20x response time
improvement by using non-standard blocksizes, your operating system
vendor and Oracle publish
tips for setting the correct blocksize.
Any experienced DBA will tell you that tuning the external I/O sub-system
can have a profound impact of performance, and this is done in two
places, the external environment, and the device media control layer
(DMCL), the point at which Oracle maps his logical structures to the physical
hardware.
Optimization of I/O requires that you check all areas of the external environment:
- OS Kernel - Tweaking the OS kernel configuration (setting
direct I/O,
using specialized I/O drivers)
- Disks - Optimizing disks (optimal RAID, load balancing
of disks and
controllers, monitor disk enqueues (e.g. EMC Symmetrics),
deploying SSD.
- Network - Tuning the interconnect in RAC environment
and packet behavior in client server connections
We also have the DMCL (Device-Media Control Layer), the point
where the logical Oracle structures (tablespaces) map to their
physical data files (by optimizing db_block_size,
db_file_multiblock_read_count, dbms_stats.gather_system_stats,
filesystemio_options, disk_asynch_io, db_writer_processes,
and other Oracle filesystem I/O options. It is these critical settings that determine the
"actual" read size for the Oracle database. Remember, the
db_file_multiblock_read_count also has a huge effect of I/O,
often as much as the blocksize itself.
But you don't always see a performance improvement by using a
smaller blocksize, you can see the opposite as well. Oracle
consultant Ben Prusinski notes that batch jobs can see a 3x
performance improvement when moved to a larger blocksize:
"For a past customer a large financial company, we improved
database performance by increasing block size from 8k blocksize
to 16k blocksize.
Performance for nightly data loads went down from 22 hours to 6
hours when we increased the database block size."
In general, different blocksizes can improve performance in a
variety of ways, but every system is different, and only a
real-world workload benchmark can show what benefits you may
experience. See my
2008 survey on
multiple blocksizes for details.
- 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 as long as the I/O does not exceed the capabilities of
the disk subsystem.
Oracle also notes that systems with a
"hybrid" of I/O access patterns may benefit from
implementing multiple blocksizes. For details, see
my
Oracle Multiple blocksize tips.
Related block size notes
See my related notes on tuning the Oracle I/O subsystem:
|
and include the URL for the page.
