Oracle hash_area_size is simple. The hash_area_size
parameter value defaults to 1.5 times sort_area_size and
is used for performing hash joins of Oracle tables. The
higher the value for hash_area_size, the higher the
propensity for the CBO to use a hash join.
The values for hash_area_size is quickly shown with the
Oracle show parameters command:
SQL> show parameters
hash_area_size
NAME TYPE VALUE
------------------------------
hash_area_size integer 1048576
The hash_area_size is obsolete if you are using
pga_aggregate_target, but in Oracle9i with
pga_aggregate_target, a hash area size cannot exceed 5%
of the PGA area.
Many increase the hash_area_size with "alter session
get hash_area_size" or with a use_hash hint.
The above text is
an excerpt from "Creating
a Self Tuning Oracle Database", by Rampant
TechPress. It is only $9.95 and all scripts in this
tips can be immediately downloaded.
http://www.rampant-books.com/book_2003_1_oracle9i_sga.htm
Scripts for measuring
changes to hash_area_size can be found in Mike Ault's
script collection:
http://www.rampant-books.com/download_adv_mon_tuning.htm
