Important tips for tuning SQL joins

Most Oracle professionals are aware of the "logical" ways to join tables, equi-joins, outer-joins and semi-joins, but Oracle also has internal ways of joining tables together, physical join methods that are critical to understanding SQL tuning.
 
Oracle has a vast array of internal algorithms including several types of nested loops and merge joins, plus six types of hash joins.  We also see data access methods such as many types of internal sorts and table access methods. 

When we examine an execution plan for a SQL statement we may see the “physical” join implementations, with names like nested loops, sort merge and hash join.

The propensity of the SQL optimizer to invoke a hash join is heavily controlled by the setting for the hash_area_size and pga_aggregate_target Oracle parameter.  The larger the value for hash_area_size, the more hash joins the optimizer will invoke. In some releases of Oracle, the hash_area_size defaults to double the value of the sort_area_size parameter, but it highly dependent upon parameter settings and your release level. 
 
When tuning SQL we must always remember that the optimizer can sometimes fail to choose the “best” table join method.  This is especially true for cases where a hash join is wrongly chosen over nested loops. 

This is frequently the case when we have sub-optimal schema statistics (especially column histograms) can lead to cases where the optimizer makes an incorrect guess about the cardinality of a result set and wrongly invokes a join that requires a full-table scan, not choosing nested loops. 

See below for expert SQL tuning tricks for forcing nested loops joins over hash joins:

http://www.dba-oracle.com/t_tuning_sql_nested_loops_joins.htm

*****************************************
Try Ion for Oracle database trends!

Using the principles of decision support systems technology, BC has developed an important new Oracle tuning tool, Ion for Oracle.  Ion quickly isolate the reports on the important optimization issues, separating the wheat from the chaff like no other performance tuning software.

After several years of hard work, Ion for Oracle is finally available for general release.  Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.

Predictive analytics if the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access.  To try Ion for free, see here:

www.ion-dba.com

*****************************************

Need a health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf