Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 
     Oracle hash joins

 
The most common table join mechanisms are nested loop joins and hash joins.  In a nested loop join, the database reads and index, builds a list of ROW ID’s and then probes into the second table for the matching rows.
 
 
In a hash join, Oracle uses RAM memory to speed the join.
 
 
In a hash join, the database does a full-scan of the driving table, builds a RAM hash table, and then probes for matching rows in the other table.  For certain types of SQL, the hash join will execute faster than a nested loop join, but the hash join uses more RAM resources.  The propensity of the SQL optimizer to invoke a hash join is heavily controlled by the setting for the hash_area_size Oracle parameter.  The larger the value for hash_area_size, the more hash joins the optimizer will invoke. As we will soon learn in IT482, the hash_area_size defaults to double the value of the sort_area_size parameter.  Here is how to see these values on your database:
 
 
SQL> show parameters area_size
 
NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ----------------------
bitmap_merge_area_size               integer     1048576                       
create_bitmap_area_size              integer     8388608                       
hash_area_size                       integer     1048576                       
sort_area_size                       integer     524288                        
workarea_size_policy                 string      MANUAL                         
 
Pay special attention to the readings on sort operations, and understanding how Oracle first attempts to sort row results sets inside the RAM memory of the SGA or PGA.  Sorting can occur whenever an SQL statement contains an order by, or group by clause. If there is no room in the RAM memory region to sort the results set quickly, Oracle will go to the temporary tablespace, and complete the sort operation using disk storage.  The management of sorting is a very critical part of SQL tuning because RAM memory sorts are many thousands of times faster than sort that have to be done inside the temporary table space.


 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.