Oracle Block size Vs OS block size
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 32

Thread: Oracle Block size Vs OS block size

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    101

    Oracle Block size Vs OS block size

    Hi Friends,

    I have few doubts about Oracle Block size and OS block size.

    Typically, we will set the OS block size as Oracle block size. Solaris supports two block sizes. One is 4k another is 8k, default is 8k. If i go for Transaction processing database, i will have to set db_block_size as 4k. When OS is 8k size. For one OS write, Oracle will have to write twice. There will be performance issue on this.

    If, this is the case. Generally, any Unix installation will go with default BLOCK size. SUN and Oracle is working closer. Suppose, i want to go for TRANSACTION processing type.

    What should be my block size, when OS block size is 8k.


    Thanks
    gandhi

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Oracle Block size Vs OS block size

    Solaris (and most UNIX) OS block size is usually 512 bytes.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Nov 2000
    Posts
    101
    Hi Julian,

    I agree with you, but, when we create a file system with "mkfs" or through any GUI utility like Veritas. The default file system type is 4k or 8k.This is not the issue.

    If i have 8k as OS block size and 4k is the Data block size, what could the performance ? Will it go down or not?


    Thanks
    gandhi

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Its irrelevent.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    The guys from Quest say big block sizes are better for DBA's . . .
    http://www.quest.com/newsletter/v2i5/block.html

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Lemme be clear with this post
    Originally posted by abhaysk
    Its irrelevent.
    I meant for the statement below...
    If i have 8k as OS block size and 4k is the Data block size, what could the performance ? Will it go down or not?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Hm, I wouldn't say it is irrelevant. It would be (more or less) irrelevant if you had OS block size of 4K and DB_BLOCK_SIZE of 8K, but not the other way arround as the original poster asked.

    If you have 8K OS block size and 4K database block size, then for every single block I/O operation the database requests, the OS will read an extra 4K of data the database hasn't asked for.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by DaPi
    The guys from Quest say big block sizes are better for DBA's . . .
    http://www.quest.com/newsletter/v2i5/block.html
    Hmmm... what that article doesn't mention is what version of oracle this was tested on. If there were in fact two different databases with two different blocks sizes I'd have an easier time believing the "I/O Time per Block", section. but if it was performed in 9i using two different blocks sizes on different tablesapces and caches, I'd say it's a potential ill-conceived point and incorrect observation.

    I have performing numberous test's on multi-size tablespace in 9i myself, and found the results unusual, which could explain the document by Eyal Aronoff, and therefore his tabled results deemed irrelevant.

    IF the DB_FILE_MULTIBLOCK_READ_COUNT = 32 as in the Eyal's test case and this was run on an non default 8K_Tablespace, this scattered_read of 8 would expected. i.e. 8 (reported MBRC) x 8192 = 64K. (32 * 2048 = 64K)

    When I performed a FTS large table located in a NON-DEFAULT 32K block tablespace and I got the following result.

    alter session set db_file_multiblock_read_count = 32;

    WAIT #1: nam='db file scattered read' ela= 7451 p1=11 p2=69735 p3=8
    WAIT #1: nam='db file scattered read' ela= 9883 p1=11 p2=69743 p3=8
    WAIT #1: nam='db file scattered read' ela= 7587 p1=11 p2=69751 p3=8
    WAIT #1: nam='db file scattered read' ela= 7858 p1=11 p2=69759 p3=8


    Therefore, you'd assume that my OS is only capable of returning 8 blocks per read... but it's not, cause it is in fact 8 * 32768 = 256K

    When I test a FTS on the SAME TABLE on an 8K DEFAULT tablespace... I get..

    WAIT #1: nam='db file scattered read' ela= 5335 p1=10 p2=299851 p3=32
    WAIT #1: nam='db file scattered read' ela= 5588 p1=10 p2=299883 p3=32
    WAIT #1: nam='db file scattered read' ela= 5163 p1=10 p2=299915 p3=32
    WAIT #1: nam='db file scattered read' ela= 5827 p1=10 p2=299947 p3=32


    Indicating I can actually read 32 Blocks in a read. Which is also still 256K (32 * 8K).

    So what should I conclude, my OS is only capable of returning 256K per I/O as indicated in the document.

    No, what I concluded running 9i, is that when using different MFBR figures in non_default tablespaces the FTS's scattered read will always (from my testing) perform the same number of "reads" that the DEFAULT tablespaces use.

    Therefore, in this case, as stated, there isn't an advantage to large block size from a READ performance point of view in 9i using larger sized NON-DAFUALT tablespaces.

    The point in the article "The reason was that in our machine, with an 8K block, we could not read more than 8 blocks (64K bytes), regardless of how the MBRC was actually set." is unsubstantialed and may not be the limitation on the OS, and my simply be what is expect in 9i.
    Last edited by grjohnson; 08-12-2003 at 08:59 AM.
    OCP 8i, 9i DBA
    Brisbane Australia

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by grjohnson
    No, what I concluded running 9i, is that when using different MFBR figures in non_default tablespaces the FTS's scattered read will always (from my testing) perform the same number of "reads" that the DEFAULT tablespaces use.

    Therefore, in this case, as stated, there isn't an advantage to large block size from a READ performance point of view in 9i using larger sized NON-DAFUALT tablespaces.
    That's the correct conclusion with respect to default TS and DMRC, but consider what happens if you "up" the multiblock read count -- then you will start leveraging the larger non-default block size.

    For a db with system TS of 8k blocks and a DMRC of 32, operations on a TS with 32k blocks are disadvantaged because they are effectively operating with a DMRC of 8. If much of your io is coming from the 32k TS you would do better to raise your DMRC -- say to 64.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  10. #10
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    I ceiling on my OS is 128 * 8192

    alter session set db_file_multiblock_read_count = 1000;

    Table in 8K Default Block

    WAIT #1: nam='db file scattered read' ela= 21993 p1=24 p2=115529 p3=128
    WAIT #1: nam='db file scattered read' ela= 21748 p1=24 p2=115657 p3=128

    Table n Non-Default 32K tablespace:

    WAIT #1: nam='db file scattered read' ela= 18865 p1=11 p2=28807 p3=32
    WAIT #1: nam='db file scattered read' ela= 18285 p1=11 p2=28839 p3=32


    Exactly the same amount of data returned each read. And teh FTS on the 32K block size table was 6 seconds slower.
    OCP 8i, 9i DBA
    Brisbane Australia

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