Re: block size

From: Randolf Geist <>
Date: Tue, 27 Oct 2009 15:44:42 +0100
Message-Id: <>

> no longer handcuffed. One can have multiple block sizes in the same
> database, which is what I've noticed in most well-tuned DW instances.

Regarding multiple block sizes: When using multiple block sizes for "tuning" purposes one needs to be aware of the present limitations of the Cost-Based Optimizer (up to, not tested yet with 11.2) when dealing with such objects.

In a nutshell, the cost calculations for full table scans residing in non-default block sizes are simply wrong and do not reflect at all the actual behaviour at execution time, where the size of a multi-block read request is always scaled in such a way that it corresponds to the I/O request size used with the default block size, e.g. having 8KB as default block size with 16 blocks to read per multi-block request (db_file_multiblock_read_count, DBFMBRC, or "_db_file_exec_read_count" from 10g on), using 16KB non-default block size will result in the DBFMBRC scaled down to 8 for this object, resulting in the same multi-block I/O request size of 128KB.

So from an I/O request size perspective the larger/smaller non-default block size doesn't make a difference at all, but if you look at the cost calculation you'll be in for a surprise, depending on the mode used (traditional I/O costing, WORKLOAD / NOWORKLOAD System Statistics).

For those interested in all the details, I have them covered in my blog series about this topic - this is covered in part 4:

This is also part of my "CBO fundamentals: Understanding the different modes of System Statistics" presentation which I will be giving next time at the UKOUG conference in a couple of weeks.


Oracle related stuff blog:

SQLTools++ for Oracle (Open source Oracle GUI for Windows):

GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT! Jetzt freischalten unter
Received on Tue Oct 27 2009 - 09:44:42 CDT

Original text of this message