Multiple Block Size Options

[gauravdba]

Hi, I have a question about multiple block sizes availabe in Oracle9i
What is the advantage of having multiple block sizes?
and
Why we can not assign multiple block sizes to temporary tablespaces?

[burleson]

Hi Grarav,

Multiuple blocksizes are GREAT, but ONLY if your database is I/O-bound. Check your top-5 STATSPACK timed events first.

Also, you must ensure that your database is using direct I/O:

http://www.dba-oracle.com/oracle_tips_direct_io.htm

The performence benefits are obvious, and most of the world-record Oracle benchmarks used multiple blocksizes:

http://www.dba-oracle.com/oracle_tips_benchmarks.htm

Finally, research has proved that Oracle indexes build cleaner in large blocksizes:

http://www.dba-oracle.com/oracle_tips_index_io.htm

As for the TEMP tablespace, many Oracle DBAs use a default 32k tablespace to get around this issue.