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




The push_join_predicate Parameter
Oracle Tips by Burleson

Prior to Oracle8i, the push_join_predicate parameter is used when views participate in a table join. This parameter is obsolete starting with Oracle8i. It controls whether the optimizer will attempt to push join predicates into a view. Predicates can only be pushed if push_join_predicate=true and the prerequisites conditions are met. The view must contain one of the following conditions:

  • A group by clause

  • An aggregation function such as sum or avg

  • A select distinct clause

  • A join hint

Note that prior to Oracle8i, this feature is automatically enabled by setting the optimizer_features_enable parameter. In Oracle8i and beyond, push_join_predicate is the default behavior.

The optimizer_index_caching Parameter

The optimizer_index_caching parameter is a percentage parameter with valid values between zero and 100. This parameter lets you adjust the behavior of the cost-based optimizer to select nested loop joins more often or less often. The cost of executing a nested loop join where an index is used to access the inner table is highly dependent on the caching of that index in the buffer cache. The amount of index caching depends on factors, such as the load on the system and the block access patterns of different users, that the optimizer cannot predict. Of course, you may cache an index by placing the data block in the KEEP pool, thereby ensuring that the blocks are always cached.

Setting optimizer_index_caching to a higher percentage makes nested loop joins look less expensive to the optimizer, which will be more likely to pick nested loop joins over hash or sort merge joins.

The default value for the optimizer_index_caching parameter is 0, which gives the highest preference to hash joins and sort merge joins. Resetting this parameter can be very dangerous if you are not using stored outlines because it could change the execution plans for thousands of SQL statements. Also, because the cost-based optimizer will generally only invoke sort merge joins when there are no indexes on the joined tables, this parameter has the most effect on the invocation of hash joins.

Initialization Parameters Affecting CBO Behavior for Hash Joins

The hash join is one of the trickiest to implement because of the dependencies with the Oracle initialization parameters. Here is a brief summary of the hash join parameters. For more information on hash join hints, see Chapter 12, and for details on tuning hash joins, see Chapter 16.

The hash_area_size Parameter

The hash_area_size parameter specifies the maximum amount of memory, in bytes, to be used for the hash join. If this parameter is not set, its value defaults to twice the value of the sort_area_size parameter. Unlike many other initialization parameters that you cannot change without bouncing the database, hash_area_size is changed with the alter session set hash_area_size=nnn command.

The RAM for hash_area_size is allocated from the User Global Area (UGA) of a user process. For dedicated server connections, this is part of process memory (PGA). For multithreaded servers, the UGA is allocated from either the Shared Pool or the Large Pool, if the Large Pool is configured.

The hash_area_size is allocated for a process that executes a hash join, and it does not cause increased RAM consumption for queries that are not executing hash joins. As a general rule, the hash_area_size should be set to the size of the smaller table in the query, because the contents of this table will be hashed into the RAM region.

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