Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Tracking Oracle hash joins

Oracle Tips by Burleson Consulting

Interrogating table join methods

The choice between a hash join and a nested loop join depends on several factors:

§       The relative number of rows in each table

§       The presence of indexes on the key values

§       The settings for static parameters such as index_caching and cpu_costing

§       The current setting and available memory in pga_aggregate_target

Hash joins frequently do not use indexes and perform full-table scans, often using parallel query.  Hence, the use of hash joins with parallel full-table scans tend to drive-up CPU consumption.


Also, PGA memory consumption becomes higher when hash joins are used, but if AMM is enabled, it is not usually a a problem.


The following query produces a report alerting an Oracle DBA when hash join operations count exceeds some threshold (See book errata for details):


Note:  These scripts will only track SQL that you have directed Oracle to capture via your threshold settings in AWR or STATSPACK, and STATSPACK and AWR will not collect "transient SQL" that did not appear in v$sql at snapshot time.  Hence, not all SQL will appear in these reports.  See my notes here on adjusting the SQL capture thresholds and what SQL is included in AWR/STATSPACK tables?


§       awr_hash_join_alert.sql



col c1 heading ‘Date’                format a20

col c2 heading ‘Hash|Join|Count’     format 99,999,999

col c3 heading ‘Rows|Processed’      format 99,999,999

col c4 heading ‘Disk|Reads’          format 99,999,999

col c5 heading ‘CPU|Time’            format 99,999,999


accept hash_thr char prompt ‘Enter Hash Join Threshold: ‘


ttitle ‘Hash Join Threshold|&hash_thr’


    'yy-mm-dd hh24'
  )                                 snap_time,
  count(*)                          ct,
  sum(st.rows_processed_delta)      row_ct,
  sum(st.disk_reads_delta)          disk,
  sum(st.cpu_time_delta)            cpu
   dba_hist_snapshot   sn,
   dba_hist_sqlstat    st,
   dba_hist_sql_plan   sp


   st.snap_id = sn.snap_id
= sn.dbid
= sn.instance_number
= st.sql_id
= st.dbid
= st.plan_hash_value
group by
   to_char(sn.begin_interval_time,'yy-mm-dd hh24')
        count(*) > &hash_thr;



The sample output might look the following, showing the number of hash joins during the snapshot period along with the relative I/O and CPU associated with the processing.  The values for rows_processed are generally higher for hash joins which do full-table scans as opposed to nested loop joins with generally involved a very small set of returned rows.


                   Hash Join Thresholds




                Join        Rows        Disk         CPU

Date           Count   Processed       Reads        Time

-------------------- ----------- ----------- -----------

04-10-12 17       22       4,646         887  39,990,515

04-10-13 16       25       2,128         827  54,746,653

04-10-14 11       21      17,368       3,049  77,297,578

04-10-21 15       60       2,805       3,299   5,041,064

04-10-22 10       25       6,864         941   4,077,524

04-10-22 13       31      11,261       2,950  46,207,733

04-10-25 16       35      46,269       1,504   6,364,414



Hash Join Tuning is tricky!

The sorting default is that no single task may consume more than 5% of the pga_aggregate_target region before the sort pages out to the TEMP tablespace for a disk sort. 


The Oracle cost-based optimizer will determine whether a hash join would be beneficial over a nested-loop join, so making more PGA available for hash joins will not have any detrimental effect since the optimizer will only invoke a super-sized hash join if it is better than a nested-loop join. In a system like the example above, the following settings would increase the default sizes for large sorts and hash joins while limiting those for parallel sorts.

·       pga_aggregate_target = 4g

·       _pga_max_size = 400m

·       _smm_px_max_size = 333m

With these hidden parameters set we see significant size increase for serial sorts and a throttling effect for parallel queries and sorts. However, bear in mind that it only valid for a specific release of Oracle10g, on a specific hardware and OS environment, and not using any optional features such as the MTS.

Here is a STATSPACK version of the script to track hash joins:

col c1 heading 'Date'                format a20
col c2 heading 'Hash|join|Count'     format 99999,99999,99999
col c3 heading 'Rows|Processed'      format 99999,99999,99999
col c4 heading 'Disk|Reads'          format 99999,99999,99999
col c5 heading 'CPU|Time'            format 99999,99999,99999
accept hash_thr char prompt 'Enter Hash Join Threshold: '
ttitle 'Hash Join Threshold|&hash_thr'
     to_char(sn.snap_time,'yy-mm-dd hh24')      c1,
     count(*)                                   c2,
     sum(st.rows_processed)                     c3,
     sum(st.disk_reads)                         c4,
     sum(st.cpu_time)                           c5
    stats$snapshot sn,
    stats$sql_plan_usage  p,
     stats$sql_summary   st,
     stats$sql_plan  sp
     st.sql_id = p.sql_id
     p.plan_hash_value = sp.plan_hash_value
     sn.snap_id = st.snap_id
     sn.dbid = st.dbid
     sp.operation like '%HASH%'
     count(*) > &hash_thr
  group by



The Ion tool is also excellent for identifying SQL to tune and it can show SQL execution over time with stunning SQL graphics.


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

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



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational