Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Improving SQL execution plans

Intelligent buffer segregation improves overall execution speed by reducing buffer gets, but there are also some other important reasons to use multiple blocksizes. 


In general, the Oracle CBO is unaware of buffer details, except when the optimizer_index_caching parameter is set where using multiple data buffers will not impact SQL execution plans.  When data using the new cpu_cost parameter in Oracle10g, the Oracle SQL optimizer builds the SQL plan decision tree based on the execution plan that will have the lowest estimated CPU cost. For example, if a 32k data buffer is implemented for the index tablespaces, the DBA can ensure that the indexes are cached for optimal performance and minimal logical I/O in range scans.


For example, if a database has 50 gigabytes of index space, a 60 gigabyte db_32k_cache_size can be defined and then the optimizer_index_caching parameter can be set to 100, telling the SQL optimizer that all of the Oracle indexes reside in RAM.  When Oracle makes the index versus table scan decision, knowing that the index nodes are in RAM will greatly influence the optimizer because the CBO knows that a logical I/O is often 100 times faster than a physical disk read.


In sum, moving Oracle indexes into a fully cached 32k buffer will ensure that Oracle favors index access, reducing unnecessary full table scans and greatly reducing logical I/O because adjacent index nodes will reside within the larger, 32k block.



This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:




 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks