what size of db_block_size should I use when building a datawarehouse db?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: what size of db_block_size should I use when building a datawarehouse db?

  1. #1
    Join Date
    Oct 2000
    Posts
    144

    what size of db_block_size should I use when building a datawarehouse db?

    Hi,
    I'm new to data warehouse db. The current data warehouse db size is 300gig which has the db block size of 4kb which I think it is small and not sure why the privious dba build the small size of the db. I got assign to build the new datawarehouse db which I'm not sure should I build with 8kb or 16k block size based on the size of 300gig+ db so I can export the current db and import to the new db.
    Any suggestion is greatly appreciate it.

    Trina

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool 16k

    16k block size seems ok.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Platform?... can't you go for 32K?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Feb 2009
    Location
    Kayseri, Turkey
    Posts
    15
    These are main principles when deciding the db block size

    If the rows are small and access to the rows are random , then choose a smaller block size.
    If the rows are small and access is sequential, then choose a larger block size.
    If the rows are small and access is both random and sequential, then it might be effective to choose a larger block size.
    If the rows are large, such as rows containing large object (LOB) data, then choose a larger block size.

  5. #5
    Join Date
    Oct 2000
    Posts
    144
    the platform is aix 5.3

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by metinergoktas View Post
    These are main principles when deciding the db block size

    If the rows are small and access to the rows are random , then choose a smaller block size.
    If the rows are small and access is sequential, then choose a larger block size.
    If the rows are small and access is both random and sequential, then it might be effective to choose a larger block size.
    If the rows are large, such as rows containing large object (LOB) data, then choose a larger block size.

    ... true but, poster already said it - it's a Data Warehouse.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Oct 2000
    Posts
    144
    I found some info on google website regardinng to db block size and thought to share with everyone.

    Oracle Database 10g Performance Tuning Tips and Techniques
    -- as a rule of thumb, a DW should use the maximum block size available for your platform (either 16k or 32kb), while a transaction procesing system should use an 8kb block size.

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Trina View Post
    I found some info on google website regardinng to db block size and thought to share with everyone.

    Oracle Database 10g Performance Tuning Tips and Techniques
    -- as a rule of thumb, a DW should use the maximum block size available for your platform (either 16k or 32kb), while a transaction procesing system should use an 8kb block size.
    Well... if you look at the answers to your post you may be surprised that's exactly what everybody here was telling you.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Oct 2000
    Posts
    144

    Smile

    I want to thank all of you providing me suggestion of what db block size to use. Have a great day.

    Trina

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