Re: block size
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 27 Oct 2009 15:44:42 +0100
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
