Printable View
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.
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?
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.
Use 16kb
Thanks for the reply, however is there a formula to determine which tablespace is a candidate for 8k,16k block sizes
These two articles may help:
Choosing a Database Block Size (DB_BLOCK_SIZE) Parameter
What block size should I use when creating a database?
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.
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