Oracle Tips   Donald K. Burleson

Oracle Multiple Blocksizes

Another new Oracle9i addition you should be aware of when planning your storage structures is the multiple block size tablespace feature. Prior to Oracle9i, every tablespace assumed the database’s block size.

With Oracle9i, a DBA can now create tablespaces that have smaller or larger blocksizes than the database. Why would you want to make use of this feature, and how can it help performance? While many objects in an Oracle database will benefit from the standard 4K or 8K blocksize, there are other objects and structures that benefit from having a large blocksize. Before Oracle9i, you could not mix the two, but now you can. Some objects that benefit from a larger blocksize (16K to 32K) include:

• Most indexes because of the serial nature of index range scans
• Large tables that are the target of full table scans
• Tables with large object (BLOB, CLOB, etc.) data
• Tables with large row sizes that might blossom into chained/migrated rows
• Temporary tablespaces used for sorting

Keep in mind, to utilize the new multi-blocksize feature; you must enable the new memory caches (db_16k_cache_size, db_32k_cache_size, etc.) that house the blocks from the different-sized tablespaces.

 

The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.

It's only $19.95 and you can order it and get instant access to the Oracle scripts here:

http://www.rampant-books.com/book_2003_1_perf.htm

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.