Rules for setting large Oracle blocksizes

1 ? Indexes like large tablespaces

This is quite true. Robin Schumacher proved that the index tree builds cleaner and that range scans happen with less consistent gets:

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

Note the test that proves it.

2 ? TEMP likes large blocksizes

This is very true. All temp segment access is sequential and 32k blocksizes greatly reduce logical I/O and disk sort times. I'm working an a benchmark right now that shows a one-third speed improvement of disk sorts in a 32k vs. an 8k blocksize.

While the database will have to be created with the blocksize this large to use temporary tablespaces this size, most databases that require large sorts will be data warehouse or DSS and thus will also benefit from large blocksizes.

3 ? Multiple blocksizes

Multiple blocksizes are used in ALL the Oracle10g TPC-C benchmarks because they allow far faster transaction execution with less I/O. These vendors spent hundreds of thousands of dollars to get the fastest performance and their choice of multiple blocksizes speaks for itself.

UNISYS benchmark: 250,000 transactions per minute.

http://www.tpc.org/results/FDR/TPCC/unisys_es7000-420_291K_040209_fdr.pdf

db_16k_cache_size = 15010M

db_8k_cache_size = 1024M

db_cache_size = 8096M

db_keep_cache_size = 78000M

HP benchmark: 1,000,000 transactions per minute.

http://www.tpc.org/results/FDR/TPCC/HP%20Integrity%20rx5670%20Cluster%2064P_FDR.pdf

db_cache_size = 4000M

db_recycle_cache_size = 500M

db_8k_cache_size = 200M

db_16k_cache_size = 4056M

db_2k_cache_size = 35430M