Re: Cost of hash join

From: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
Date: Fri, 10 Jun 2005 12:16:56 -0400
Message-ID: <52a152eb05061009169497ae@mail.gmail.com>

Christian,

Where did you get the number of slots available and needed from?

"In the second case we need about 116 slots but only 17 are available"

Which trace told you that ? I cant seem to find tehse numbers (especially 116) in the trace outputs you gave.

Christo

On 6/9/05, Christian Antognini <Christian.Antognini_at_trivadis.com> wrote:

> Hi Jared

>=20
>=20

> Some observations show that the cost is not proportional with the number = of partitions.
>=20

> Below you find a simple test. Of course any comment is welcome!!!

>=20
>=20
>=20
>=20

> >>

>=20

> >>Why is the access cost of A multiplied by no. of hash

>=20

> >>partitions?

>=20

> >

>=20

> >In my opinion this is not a good one (perhaps for this reason it's no mo= re in the 10g
>=20

> >documentation...).

>=20

> >

>=20

> >In fact the cost is something like:

>=20

> >

>=20

> >cost =3D access cost A + access cost B + cost join.

>=20

> >

>=20

> >If the hash table is "small", the cost of the join itself will be close = to 0.
>=20

> >If the hash table is "large", the cost is strongly dependent on the amou= nt of "available" PGA
>=20

> >memory.

>=20
>=20

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

>=20

--=20
Christo Kutrovsky
Database/System Administrator
The Pythian Group

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

Received on Fri Jun 10 2005 - 12:22:00 CDT