Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Large Blocks and Oracle Indexes blocksize

When an SQL query retrieves a result set from an Oracle table, it is probably gathering the table rows through an index. Many Oracle tuning experts have recommended that databases created prior to Oracle9i be re-defined with large block sizes. The performance gains realized from switching a 2K block size database to an 8K block size have perplexed many.

Resistance to increasing the block size was typically expressed as “Why will moving to a large block size improve a database that only randomly fetches small rows”? The answer to this question is not so simple, but it involves indexes.

Many DBAs fail to consider index trees and the index range scan process of sequential retrieval of the index when choosing a block size. Nested loop joins usually evidence an index range scan, and the vast majority of rows are accessed using indexes.

Locating indexes in larger size blocks reduces I/O and further improves throughput for the entire database because index range scans gather index nodes sequentially. If this is the case, why not just create the database with large block sizes and forget about multiple block sizes?

The answer here is also complex. RAM buffer memory cannot be utilized with maximum efficiency unless the tables are segregated according to the distribution of related data between them. In allocating block sizes, we can still apply the same general rules, with some modification in our understanding.


The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.

Complete Oracle Script Collection Available

BC shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, we shares our secrets for navigating the Oracle data dictionary.

Packed with more than 680 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.




Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.