FAQ
Here are some facts that may contribute to your discussion:

The Oracle Database Machine databases are created and tested with 8k
blocks. That goes for DW and OLTP databases. 21GB/s scan rate from disk,
50GB/s scan rate from flash are with 8k blocks. These are published numbers,
I'm not giving away any secrets here.
A data file using 8k blocks will hold a little less data than the same
sized data file using 16k blocks due to the block overhead.
The larger the block size, the lower the concurrency, generally
speaking. So beware of using very large block sizes as any potential future
updates (never say never...) may suffer from concurrency issues.

I think the larger block size arguments come from believing that DW
databases are generally larger than OLTP databases and so you want to avoid
the additional block overhead and pack more data into the same files by
avoiding block overhead. DWs don't usually have the concurrency requirements
that OLTP does, so the larger blocks don't hurt anything.

I don't have a particular suggestion or recommendation myself except to note
that the Oracle Database Machine uses 8k blocks for all standard
deployments. In my own experiences, the vast majority of Oracle databases in
use today use 8k block size, though I admit that I don't know of any
block-size-specific bugs or issues.

Dan

On Mon, Oct 26, 2009 at 1:32 PM, Niall Litchfield <
niall.litchfield_at_gmail.com> wrote:
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?"

In other words *why* do people think this? Note I'm specifically not after
pointers to religious wars (entertaining though these are) but to particular
logical arguments. This chap is bright (and like me somewhat loquacious) and
I disagree with him, the resultant discussion could go on a while so I'm
hoping to short circuit it.

Platform is RHEL 4 64bit, 64bit Oracle 11gR1. This is *not* a warehouse
with huge amounts of data (say, 500mb per year).

--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Lyallbarbour_at_sanfranmail.com at Oct 26, 2009 at 7:42 pm
    I'm not a DW guy at all. We have one at our company, but, since DW are mostly read databases, isn't that what the big block size would be good for. Grab a ton of info and through it into cache/memory? OLTP, smaller block, less writing for insert, updates and deletes. That's the only thing that comes to mind for me.
    Lyall

    -----Original Message-----
    From: Niall Litchfield
    To: ORACLE-L
    Sent: Mon, Oct 26, 2009 2:32 pm
    Subject: block size

    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?"


    In other words *why* do people think this? Note I'm specifically not after pointers to religious wars (entertaining though these are) but to particular logical arguments. This chap is bright (and like me somewhat loquacious) and I disagree with him, the resultant discussion could go on a while so I'm hoping to short circuit it.

    Platform is RHEL 4 64bit, 64bit Oracle 11gR1. This is *not* a warehouse with huge amounts of data (say, 500mb per year).

    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info

    --
    http://www.freelists.org/webpage/oracle-l
  • Greg Rahn at Oct 26, 2009 at 8:31 pm

    On Mon, Oct 26, 2009 at 11:32 AM, Niall Litchfield wrote:
    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?"

    In other words *why* do people think this?
    Lets start with the last question first: People think this probably because of:
    1) they think better is better for an unknown, unquantified reason
    2) someone somewhere on the Internet wrote it, so it must be true

    The reality (facts) of it is this:
    - a larger block *may* give a slightly better compression ratio as
    more values are packed within a block and Oracle uses block level
    compression
    - in a DW where physical table/partition scans are prevalent, the IO
    size matters most, not the block size (Oracle can do 1MB reads no
    matter what the block size)
    - 8k blocks are the default and more tested value and block size can
    influence execution plan and most people know that a suboptimal
    execution plan will easily offset any *potential* gains from a non-8k
    block
    - with RAC an 8k block fits within a jumbo frame (MTU 9000) and that
    helps reduce system time for interconnect traffic (bigger blocks will
    get split)

    When people make claims on something, I generally put my engineering
    hat on and ask, "What data did you look at to draw those conclusions
    (and can I see it too)?". That question alone is enough to end many
    (silly) discussions.
  • Mark W. Farnham at Oct 26, 2009 at 10:31 pm
    The bigger the block size, the greater the percentage of user data
    that is stored per block in tables. (Regardless of compression, this is
    simply a question of block overhead for Oracle's bookkeeping of what is in
    the block. Compression might enhance this.)

    The bigger the block size, the more keys will fit per non-leaf
    index block so the blevel MAY be less (and it will never be more than with a
    smaller block size).

    The bigger the block size, the more key value/rowid references are
    stored per unit volume in index leaf blocks. (Block bookkeeping overhead and
    possibly better compression. So index scans will be faster.)

    The bigger the block size, the fewer the multi-block rows (But
    maybe Greg's point about good design also cures that.)

    What you mentioned about the MTU size of 9000 and an 8K block fitting. That
    is an excellent point and if your DW is RAC I think that might well outweigh
    any other advantages.

    Now points 1 through 3 are just simple facts about how Oracle stores stuff.
    Point number 2, while true, might not often be observed to matter for an
    actual dataset, because spillage from one blevel to another covers a huge
    and exponentially growing range. When it is true for an individual dataset
    however, it saves what is usually a full single block access. That can be a
    big deal and your mileage will vary.

    There may indeed be a difference between "the largest blocksize you can
    have" and the largest blocksize that has been proven reliable on a given
    platform. And it is possible to set up the disk farm so it double clutches
    routinely on larger block sizes.(Don't.) But with the prevalence of 1 MB
    stripe (and i/o) sizes, that is getting pretty rare these days. Greg's point
    on the i/o size of scan is right on target. You're not going to see much, if
    any difference in physical i/o to get 128 8K blocks versus 64 16K blocks. If
    you do, then I think someone made a mistake building the disk farm. I
    suppose 16K might even be slightly slower, since it has a greater possibly
    to have the block on two different stripes. I think all that, at the sizes
    we're talking about, is splitting hairs. I think I am violently agreeing
    (not dis-agreeing) with Greg on that point.

    Now, for a test suggestion on some hefty data: Have Oracle snap off their
    own ebiz database just before a payroll run. Reload that at a larger block
    size. Compare the time to completion for the real and the test larger block
    size payroll runs. That should be illustrative. Even though that is an OLTP
    job, it is batch enough to make the point (or fail to make the point).

    Now, is it worthwhile to stray from the "standard" 8K block size (funny that
    2K was the standard in Oracle 6 and I had to argue hard to get to use 8K at
    2K versus 8K the blevel argument was very solid)?

    Maybe not. I'd GUESS the effects of fitting more user data per unit volume
    of disk will get you no more than 10-20%. A good design and good queries in
    place of a bad design and bad queries produce an improvement bounded only by
    the total time of the bad way. Maybe yes: If you're going to do a good
    design and good queries either way, then you should indeed win at the margin
    with a larger block size. Measure it yourself. Build a 8K database, load a
    big table. FTS it. Repeat at 16K. If you're RAC, well then the MTU argument
    might dominate. Otherwise I'm going to be very surprised if 16K is not
    faster and wonder how you rigged the test.

    Regards,

    mwf



    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Greg Rahn
    Sent: Monday, October 26, 2009 4:32 PM
    To: niall.litchfield_at_gmail.com
    Cc: ORACLE-L
    Subject: Re: block size

    On Mon, Oct 26, 2009 at 11:32 AM, Niall Litchfield
    wrote:
    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?"

    In other words *why* do people think this?
    Lets start with the last question first: People think this probably because
    of:
    1) they think better is better for an unknown, unquantified reason
    2) someone somewhere on the Internet wrote it, so it must be true

    The reality (facts) of it is this:
    - a larger block *may* give a slightly better compression ratio as
    more values are packed within a block and Oracle uses block level
    compression
    - in a DW where physical table/partition scans are prevalent, the IO
    size matters most, not the block size (Oracle can do 1MB reads no
    matter what the block size)
    - 8k blocks are the default and more tested value and block size can
    influence execution plan and most people know that a suboptimal
    execution plan will easily offset any *potential* gains from a non-8k
    block
    - with RAC an 8k block fits within a jumbo frame (MTU 9000) and that
    helps reduce system time for interconnect traffic (bigger blocks will
    get split)

    When people make claims on something, I generally put my engineering
    hat on and ask, "What data did you look at to draw those conclusions
    (and can I see it too)?". That question alone is enough to end many
    (silly) discussions.
  • Michael Fontana at Oct 26, 2009 at 9:02 pm

    Niall wrote:
    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?"
    In other words *why* do people think this? Note I'm specifically not after pointers to religious wars (entertaining though these are) but to particular logical arguments. This chap is bright (and like me somewhat >loquacious) and I disagree with him, the resultant discussion could go on a while so I'm hoping to short circuit it.
    There are numerous examples in the documentation suggesting larger blocksizes for data warehousing, including this one from Oracle:

    http://www.oracle.com/technology/products/database/clustering/pdf/bp_rac_dw.pdf

    This is not the only one, there are many examples of this recommendation in the literature on best DW practices.
    While it doesn't say to use the LARGEST blocksize possible, it does emphasize it's advantages in many DW scenarios, but of course, not all.

    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.

    Michael Fontana

    Sr. Technical Consultant

    Enkitec M: 214.912.3709

    enkitec

    oracle_certified_partner
  • Randolf Geist at Oct 27, 2009 at 2:44 pm

    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
  • Jared Still at Oct 27, 2009 at 5:48 pm

    On Mon, Oct 26, 2009 at 9:25 PM, dbvision_at_iinet.net.au < dbvision_at_iinet.net.au> wrote:


    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.
    Indeed, a search of the bug database on 4k, 16k and 32k block size
    returns 99 hits;

    *http://tinyurl.com/oracle-block-size-bugs*
    *
    *

    This URL is a shortcut to a ML search, so it won't work if you are not
    already logged into to ML.

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    Oracle Blog: http://jkstill.blogspot.com
    Home Page: http://jaredstill.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 26, '09 at 7:02p
activeOct 27, '09 at 5:48p
posts7
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase