Re: block size
Date: Tue, 27 Oct 2009 15:44:42 +0100
Message-Id: <894305397_at_web.de>
> We.re 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 11.1.0.7, 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: http://oracle-randolf.blogspot.com/2009/05/understanding-different-modes-of-system_24.html
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.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT! Jetzt freischalten unter http://movieflat.web.de
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 27 2009 - 09:44:42 CDT