Re: block size
- From: "dbvision@xxxxxxxxxxxx" <dbvision@xxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 27 Oct 2009 12:25:50 +0800
When I hear that one about "this is datawarehouse", I'm always reminded of the
argument "datawarehouses are large and do a lot of reads".
I wonder how they became large in the first place? Perhaps through "lots of
reads"?
And if they don't get updated often, I doubt the "lot of reads" would be of any
interest...
Coming back to block sizes: in the mainframe environment, I'd definitely
consider
a larger block size for a large db, datawarehouse or not. This mostly because
one can configure the I/O of mainframes to bypass most of the intervening
handlers and indeed realize the benefits of a larger block size form the POV of
a
db.
With other environments, this is much more difficult: the I/O path of any given
process in Linux, Unix and so on, is not well defined or known and may change at
a patch's notice. Hardly the best way to handle arcane I/O configuration
decisions.
Outside of mainframes, I'd leave the block size at 8k except for special cases:
for a small-ish db, I'd check first if it has to store lots of volatile LOBs.
If
so, then increase the block size. Volatile LOBs don't go well with small block
sizes, particularly with Oracle.
I doubt the common advantages a larger block size can bring (reduced header
overhead, reduced metadata maintenance) can be realized at smaller scales.
I also nowadays have serious doubts of Oracle's storage layer stability when
using anything other than 8K blocks. The number of bugs in the online place
related to handling non-8k block sizes is staggering.
All practical considerations, really. Nothing to do with theoretical potential
advantages of larger block sizes for certain workloads. These can be relevant
IF
all other factors are controlled and pegged down. Hardly the case when your OS
is from one stable, your I/O SAN is from another, your db is yet from another.
Cheers
Nuno
On Tue Oct 27 2:32 , Niall Litchfield sent:
>so, discussion with DW architect today
>
>"but this isn't OLTP,this is DW, in a datawarehouse you want the biggest block
size you can have".
>
>my question to you (and him)
>
>"why? What's the theory and evidence for this?"
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
