Sometimes I get comments from
other Oracle experts who challenge even the most obvious
assertions about Oracle behavior. Recently I received a
comment from an Oracle employee who challenged the widely-held
belief using a large db_block_size will speed-up data
loads. Here was my original statement, based on my
clients
experiential evidence:
Use a large blocksize - Data loads onto 32k
blocksizes will run far faster because Oracle will be able
to insert more rows into an empty block before a write.
Well, whenever I say something obvious, my
teenage daughter says it best, replying "Well Duh".
Mind you,
I've never been in the habit to having to "prove" the obvious,
but I thought it might be fun to run a small experiment.
Of course, these tiny
experiments on single CPU server with a single user are rarely
valid for large-scale production systems, but hey, it worked. Here is my small single-CPU, single-user
benchmark showing the performance of loads into a larger
blocksize:
alter system set db_2k_cache_size=64m
scope=spfile;
alter system set db_16k_cache_size=64m scope=spfile;
startup force
create tablespace twok blocksize 2k; <-- using ASM defaults
to 100m
create tablespace sixteenk blocksize 16k;
create table load2k tablespace twok as select * from
dba_objects; < creates 8k rows
drop table load2k; <- first create was to preload buffers
set timing
on;
create table load2k tablespace twok as select * from
dba_objects;
create table load16k tablespace sixteenk as select * from
dba_objects;
For a larger sample, I re-issued the create
processes with:
select *
from dba_source; -- (80k rows)
Even with this super-tiny
sample on Linux using Oracle10g (with ASM) the results where
impressive:
2k 16k
blksze blksze
8k table size 4.33 secs 4.16 secs
80k table size 8.74 secs 8.31 secs
So, does this "prove"
that my assertion is correct? Not really.
In real-world databases
with thousands of concurrent users and hundreds of
transactions per second, small experimental "proofs" are
largely meaningless.
In sum, I am always wary of anyone who claims that they can prove the way that
Oracle behaves with a code snippet.
In the real-world, the
universal answer to any Oracle-related assertion is "it
depends". I have no doubt that some clients might
experience faster load speeds with smaller blocksizes,
depending on their unique configuration. Such is the
nature of Oracle tuning!
