RE: optimize hash join between 2 large tables

From: Gints Plivna <Gints.Plivna_at_softex.lv>
Date: Fri, 21 Jan 2005 17:46:19 +0200
Message-ID: <69721CE0FE0D59478756C2926DF54E500F88CB@saturn.softex.local>

> First of all a hash join use the memory that is configured via
> hash_area_size and not sort_area_size.
By default it is 2*sort_area_size.

If you use automatic memory management (workarea_size_policy =3D Auto if pga_aggregate_target is set for 9.xxx) then you should modify pga_aggregate_target.
If you use manual memory management (workarea_size_policy =3D manual or for versions before 9) then you may set also hash_area_size which by default is 2*sort_area_size. For 8i also there was hash_multiblock_io_count, but I'v never changed it and never played around with it. Maybe someone knows whether to touch it or not.

And btw hash_area_size is dynamic i.e. it can be set (_very large_ :) by alter session for your particular session only.

Gints

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Christian Antognini
> Sent: Friday, January 21, 2005 5:28 PM
> To: Paula_Stankus_at_doh.state.fl.us
> Cc: oracle-l_at_freelists.org
> Subject: RE: optimize hash join between 2 large tables

>=20

> Hi Paula

>=20
>=20

> First of all a hash join use the memory that is configured via
> hash_area_size and not sort_area_size.
>=20

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 21 2005 - 11:49:33 CST