DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to calculate best db_block_size

  1. #1
    Join Date
    Dec 2002
    Posts
    28

    How to calculate best db_block_size

    i have a 10G db which is used for datawarehouse transactions. Is there a formula to use to determine which objects will benefit from a larger block size. My Unix OS will allow 4,8,16,32,64. How Can I determine which of these sizes to set as certain tables are over 500GB and other are less than 5GB.

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    There's this fantastic Oracle knowledge base website called Metalink. If you search for "database block size" you'll be amazed at all the information you get. Here's the answer to your question.

    I'm kind of confused though I thought all Oracle DBA's knew about Metalink or is it that they're just lazy and want other people to do thier research for them?
    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

  3. #3
    Join Date
    Dec 2002
    Posts
    28

    Db_block_size

    This article does not explain how to determine which size to use for different tablespaces i.e I currently have 8k default and can use 16 or 32 per tablespace block size in 10g. How can I determine which size is best.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Use 16kb
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    Dec 2002
    Posts
    28

    formula

    Thanks for the reply, however is there a formula to determine which tablespace is a candidate for 8k,16k block sizes

  6. #6
    Join Date
    Apr 2006
    Posts
    377

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Choosing a Database Block Size (DB_BLOCK_SIZE) Parameter

    The first line of htis, "Bigger blocks means more data transfer per I/O call." is not correct, particularly in a data warehouse that makes extensive use of direct multiblock i/o.



    There is no formula - larger block sizes may give you benefits in increased compression ratio for data segments, but at the risk of operating on a much less often used configuration. 32kb has suffred problems in the past from index corruption and other issues -- I wouldn't go higher than 16kb myself.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  8. #8
    Join Date
    Apr 2006
    Posts
    377
    The ultimate db_block_size depends on several factors and therefore, it is hard to provide an unambiguous answer without all the details.

    Can you elaborate on this assertion?
    Quote Originally Posted by slimdave
    The first line of htis, "Bigger blocks means more data transfer per I/O call." is not correct, particularly in a data warehouse that makes extensive use of direct multiblock i/o.

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