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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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 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’

 

select
  to_char(
    sn.begin_interval_time,
    '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
from
   dba_hist_snapshot   sn,
   dba_hist_sqlstat    st,
   dba_hist_sql_plan   sp
where

SEE CODE DEPOT FOR FULL SCRIPTS

   st.snap_id = sn.snap_id
and    
   st.dbid
= sn.dbid
and    
   st.instance_number
= sn.instance_number
and    
   sp.sql_id
= st.sql_id
and    
   sp.dbid
= st.dbid
and    
   sp.plan_hash_value
= st.plan_hash_value
and    
   sp.operation
= 'HASH JOIN'
group by
   to_char(sn.begin_interval_time,'yy-mm-dd hh24')
having
        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

 

 

                Hash

                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.

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

SEE CODE DEPOT FOR FULL SCRIPTS


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:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
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 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.