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


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

The hash_join_enabled Parameter
Oracle Tips by Burleson
 

In Oracle7 and Oracle8, the hash_join_enabled parameter must be set to TRUE to invoke a hash join. In Oracle8i hash_joined_enabled=true is the default value.

The hash_multiblock_io_count Parameter

Because hash joins often read sequential blocks from the TEMP tablespace, the hash_multiblock_io_count parameter allows multiblock reads against the TEMP tablespace. It is advisable to set the NEXT extent size to greater than the value for hash_multiblock_io_count to reduce disk I/O. This is the same behavior we see when setting the db_file_multiblock_read_count parameter for data tablespaces, except that this applies only to the multiblock access of segments in the TEMP tablespace.

The star_transformation_enabled Parameter

The star_transformation_enabled parameter determines whether a cost-based query transformation will be applied to star queries. If set to TRUE, the optimizer will consider performing a cost-based query transformation on the n-way table join. This parameter also relates to the optimizer_search_limit parameter, as this parameter must be LESS THAN the number of tables in the n-way table join in order for the optimizer to consider a star join. With the default value of five, only queries that join six or more tables are eligible for star optimization.

Also, star optimization requires that a fully concatenated index exist on the fact table. In other words, if you have a fact table with 10 dimension tables, you must have a single index on the fact table with all 10 foreign keys into the dimension tables.

The optimizer_feature_enable Parameter

The optimizer_feature_enable parameter is a meta-parameter set to the defaults for other initialization parameters. Introduced in Oracle 8.0.4, it is a generic parameter that is set to the current version of your database.

The value for optimizer_feature_enable is set for your release of Oracle (optimizer_feature_enable =8.1.6), and it will default to your database version for Oracle releases greater than 8.0.4. The optimizer_feature_enable is a meta-parameter that set the values of many other initialization parameters. When optimizer_feature_enable is set, the following initialization parameters are set to TRUE.

  • b_tree_bitmap_plans

  • complex_view_merging

  • fast_full_scan_enabled

  • push_join_predicate

Of course, you will always want these features, so it is best to leave this parameter at the existing setting.


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