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

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Other Initialization Parameters that Affect SQL Execution
Oracle Tips by Burleson

There are also other initialization parameters that affect the speed of SQL queries, and these should be established prior to searching for high-impact SQL statements.

  • db_block_buffers Of course, the size of the Oracle data buffers will impact the performance of SQL statements. The data buffer hit ratio should always be checked, and the data buffer hit ratios for the buffer_pool_keep and buffer_pool_recycle should also be reviewed.

  • hash_join_enabled This defaults to TRUE in Oracle8 and is required to utilize hash joins.

  • Hash_area_size If this value is too small, the optimizer will often invoke a nested loop join instead of a hash join. A too-small hash_area_size is one of the most common reasons that a use_hash hint fails to invoke a hash join.

  • hash_multiblock_io_count Because a hash join often invokes a full-table scan, the hash_multiblock_io_count parameter determines the read-ahead capabilities for hash joins.

Once you have established the baseline optimizer_mode, you should already see a significant overall performance improvement for your SQL. This author has cut response time in half in Oracle7 databases, simply by changing the default optimizer_mode=rule. Of course, it gets trickier in Oracle8 and Oracle8i because of the improvements to the CBO.

Running the SQL Baseline Test

When performing a SQL baseline test, it is often a good idea to involve the end-user community. The SQL tuning professional generally meets with the end-user representatives and arranges to run the database on different days with different default optimizer modes. The end users are assured that the optimizer_mode can be changed back quickly by bouncing the database. Remember, for OLTP databases, the end users will have a very quick idea how effective the baseline change is on their system. You also ask the end users to note which transactions appear faster or slower, since this information will be invaluable to you when tuning the individual SQL statements.

By allowing the end-user community to determine the default optimizer_mode according to their perception of overall SQL response time, the DBA wins, and the end users win as well. The end users get an immediate improvement in SQL performance, and the DBA has less SQL statements to manually tune.

When measuring the overall performance of the database, you need to remember that the perception of the end users is far more important than internal Oracle statistics. By allowing your end users to determine the default optimizer_mode, you ensure their buy-in for your SQL tuning effort, and you also get some valuable tips on where to begin the manual tuning. If you request that your end users keep track of those transactions that run slower in each optimizer_mode, you can get right to work and immediately tune the SQL statements on your end users' list.

Establishing the SQL baseline is only the first step in SQL tuning. Next, let’s look at how to locate significant SQL statements for tuning.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Oracle performance tuning software 

Oracle performance tuning book


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



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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter