DBAsupport.com Forums - Powered by vBulletin

View Poll Results: What do you think about the article?

Voters
6. You may not vote on this poll
  • completely true

    0 0%
  • mostly true but over simplified

    0 0%
  • somewhere in the middle

    4 66.67%
  • mostly false and useless

    0 0%
  • useless and false

    2 33.33%
Page 2 of 7 FirstFirst 1234 ... LastLast
Results 11 to 20 of 64

Thread: indexes love big blocks

  1. #11
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    * Ahem *

    Moving along, there are three main points made.

    • Indexes love big blocks
    • Temp loves big blocks
    • TPC-C benchmarks use multiple block sizes and they're very heavily optimized by experts

    But TPC-C doesn't seem to use big blocks for indexes, nor for Temp. So the whole house of cards comes tumbling down.

    Getting Mike and Don to acknowledge this is not possible using currently available technology -- maybe in another ten years.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  2. #12
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    Getting Mike and Don to acknowledge this is not possible using currently available technology -- maybe in another ten years.
    Anyone working on a cyber-rack?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #13
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Originally posted by Alchemy
    OracleDoc, your sensitivity is causing me to weep uncontrolably
    Senitivity??? Pfffftttt..... that tear was from laughter. My side is hurting!
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  4. #14
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Let me put forward a situation:
    let's say there is an IOT with millions of rows.This table is also highly insert/delete intensive(sort of staging table).
    now there are queries which causes to read almost all the rows.
    In such a situation wont a big block size reduce PIOs ??
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  5. #15
    Join Date
    Apr 2005
    Posts
    29
    Originally posted by simply_dba
    Let me put forward a situation:
    let's say there is an IOT with millions of rows.This table is also highly insert/delete intensive(sort of staging table).
    now there are queries which causes to read almost all the rows.
    In such a situation wont a big block size reduce PIOs ??
    the only answer to the question "won't a big block size reduce PIOs" is:

    a) it will reduce PIOs
    b) it will increase PIOs
    c) PIOs will not budge one way or the
    other

    If the cache is devoid of blocks and you need to read all 10,000,000 rows that are in there, you will read X megabytes of data from disk.

    It won't matter if the blocks are 2k or 32k, you will read X megabytes of data from disk.

    Now, if you are doing a fast full scan of the IOT structure, Oracle will read db_file_multi_block_read_count * default_block_size bytes of data at a time.

    If you have dbfmbrc = 16 and default block size is 8 K, Oracle will do reads of 128k. Regardless of block sizes. 128k of 8k blocks, 128k of 4k blocks, 128k of 32k blocks. So, for a fast full scan from disk using multiblock IO -- the math is easy.


    So, that leaves single block IO. At the end you will still have read at least X megabytes of data from disk. Using an 8k block you will have made 4 times as many read calls as for 32k in a perfect world. In an imperfect world however, you may well have to re-read a block more than once (you have not said how you are using this IOT to read every row. If you were reading all rows regardless of order, I would assume a fast full scan, if you are reading all rows in Order, an index full scan, if you are reading this as a look up table, then we have on the order of some 10,000,000 range scans perhaps)

    So, we hit the fast full scan above.

    The index full scan (read root, branch/branch... get to left most leaf and read the leaves one by one), that one might have -- might have some advantage as it is doing the equivalent of a mini-multi-block-read on the IOT structure. Each single block IO against the 32k block is like 4 block reads of 8k blocks in one IO call.


    The index range scan is where the 8k blocks would probably best the 32k. (guess). Why, because of the smaller blocks -- when I read in a leaf block that contains the key I was looking for, I read into the cache 1/4 the data. I did not read in the equivalent of 3 more blocks of data that I don't need right now -- so I did not push out some other data that I might need right now (roots and branches). I might get better cache utilization and do less overall PIO.

    then again, I might not -- it could go either way and would be very very data dependent.


    So, this thing that reads all of the rows, how does it read all of the rows.

    And don't forget, the bigger blocksize will

    o increase contention on this index structure, N times as many rows fit there, N times as many people will be hitting it. current mode get contention goes up.

    o did you set your initrans appropriately for that.

    o and of course the latch contention that would be increasing as well. especially in a mixed read/write environment

    o and don't forget read consistency, going from 8 to 32k increased the probability of us having to do a rollback of the block(s) in order to read them by alot


    but on the bright side, the blocks would split less often and block splitting is expensive.


    This is why I voted directly in the middle. There is some truths out there.


    One other thing about Mikes "Rules" for large blocksizes paper. He says
    This is very true. All temp segment access is sequential and 32k blocksizes greatly reduce logical I/O and disk sort times. I'm working an a benchmark right now that shows a one-third speed improvement of disk sorts in a 32k vs. an 8k blocksize.
    True statement: temp IO is sequential.

    Also true is that temp is read using an IO size, not a block at a time. So while true, it is not really relevant. There is an equivalent of dbfmbrc (_DB_FILE_DIRECT_IO_COUNT) for temp and if you have 32k or 8k blocks -- a read of 128k is a read of 128k. I'd rather play with that setting to see if larger IO's against temp would help or hurt me before rebuilding my entire database to get temp into a bigger block.

    False Statement: 32k blocksizes greatly reduce logical I/O

    I don't know what LIO has to do with temp, has anyone ever seen LIO to temp? It is all direct reads, we don't need LIO because it isn't in the buffer cache it is in temp.


    Unfortunately, like most of dba-oracle.com that paper is not dated so we don't know how far away a benchmark is from completion. (but if you already know what you are going to show -- a 1/3 improvement -- it means you've already run the benchmark? doesn't it?)

  6. #16
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    So, this thing that reads all of the rows, how does it read all of the rows.

    And don't forget, the bigger blocksize will

    o increase contention on this index structure, N times as many rows fit there, N times as many people will be hitting it. current mode get contention goes up.

    o did you set your initrans appropriately for that.

    o and of course the latch contention that would be increasing as well. especially in a mixed read/write environment

    o and don't forget read consistency, going from 8 to 32k increased the probability of us having to do a rollback of the block(s) in order to read them by alot.
    true , all this is perfectly true .I face them a lot
    But
    if you are reading all rows in Order, an index full scan,
    Consider a 320M IOT , using 32k, 10 IO calls(asynchronous) , using 8k 40 IO calls asychronous).Isn't this a gain worth speaking about?
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  7. #17
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by TomKyte
    Now, if you are doing a fast full scan of the IOT structure, Oracle will read db_file_multi_block_read_count * default_block_size bytes of data at a time.
    ...

    Also true is that temp is read using an IO size, not a block at a time. So while true, it is not really relevant. There is an equivalent of dbfmbrc (_DB_FILE_DIRECT_IO_COUNT) for temp and if you have 32k or 8k blocks -- a read of 128k is a read of 128k. I'd rather play with that setting to see if larger IO's against temp would help or hurt me before rebuilding my entire database to get temp into a bigger block.

    False Statement: 32k blocksizes greatly reduce logical I/O

    I don't know what LIO has to do with temp, has anyone ever seen LIO to temp? It is all direct reads, we don't need LIO because it isn't in the buffer cache it is in temp.
    And just to add to that, should the IOT be large enough to warrant a parallel fast full index scan then we're into direct i/o and _DB_FILE_DIRECT_IO_COUNT, and the block size is no more relevant than it is for TEMP -- I could prove that with a script if you believe in that kind of thing

    So whenever anyone talks about table or FFI scans of even modestly large objects the blocksize again becomes irrelevant.

    Do I recall correctly that prior to v8 or v8i the TEMP i/o was not direct? And if the temporary tablespace was created (incorrectly) in 8i/9i/10g as a permanent tablespace then i/o would not be direct? (just hypothetically that is)
    Last edited by slimdave; 04-15-2005 at 09:10 AM.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #18
    Join Date
    Apr 2005
    Posts
    29
    Originally posted by simply_dba
    true , all this is perfectly true .I face them a lot
    But

    Consider a 320M IOT , using 32k, 10 IO calls(asynchronous) , using 8k 40 IO calls asychronous).Isn't this a gain worth speaking about?

    but that is exactly why I wrote:

    The index full scan (read root, branch/branch... get to left most leaf and read the leaves one by one), that one might have -- might have some advantage as it is doing the equivalent of a mini-multi-block-read on the IOT structure. Each single block IO against the 32k block is like 4 block reads of 8k blocks in one IO call

    And exactly why I voted right in the middle, there are some truths.


    I am not against (or really for) multiple block size tablespaces.

    I am against people saying "they are so cool, use them" without any sort of information to go with it.

  9. #19
    Join Date
    Apr 2005
    Posts
    29

    And just how many posts

    And just how many posts would I need to do to get rid of that "junior" tag

  10. #20
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: And just how many posts

    Originally posted by TomKyte
    And just how many posts would I need to do to get rid of that "junior" tag
    Don't go bringin' no ego to "Obfuscation Unlimited", Kyte. Many have tried that before ... it's not pretty, let me tell you.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width