Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Hash Joins

Re: Hash Joins

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 21 Jan 2004 09:21:46 +0000 (UTC)
Message-ID: <bulgba$kt4$1@hercules.btinternet.com>

Oracle doesn't supply any easily accessible statistics about hash joins, but for detailed investigation of particular queries you can set event 10104 before running the query.

In the trace file, look for the lines:

    Memory after hash table overhead:

        (about 3 lines in)
    Estimated Input Size:

        (about 14 lines in)

If the latter is larger than the smaller, then you hash area is (according to Oracle's estimates) going to be too small. Note - if the statistics are poor quality, or if Oracle has got a bad estimate of the number of rows that will come out of the first table, then the estimated input size could be badly wrong.

A bit further down there's a run-time section which will have some lines like:

    Total number of partitions:
    Number of partitions that fit in memory:

If the number that fits in memory is
smaller than the total number, then,
again, the hash area size is too small
for an optimal hash - and you need to
multiply the hash area size by

    total number / number that will fit
to get a size that is about right.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


<no-apm_at_sapm.com> wrote in message
news:400e3487.168560276_at_news.demon.co.uk...

> On Tue, 20 Jan 2004 18:39:35 +0100, Sybrand Bakker
> <gooiditweg_at_sybrandb.demon.nl> wrote:
>
> >On Tue, 20 Jan 2004 11:47:04 GMT, no-spam_at_no-spam.com wrote:
> >
> >>Hi
> >>
> >>Our oracle server uses hash joins for some queries I have allocated
> >>hash_size area in the init.ora file.
> >>
> >>My question is if the has joined table does not fit in to the memory
> >>specified does it try and use the disk ? if so how to do I find out if
> >>my hash memeory alloaction is enough.
> >>
> >>This may be a completeluy dum question as I am new to Oracle
> >>
> >>Thanks
> >
> >If the hash join doesn't fit in memory the temporary tablespace will
> >be used.
> >The second question is very difficult to answer as you don't mention
> >-as many many many others- which version you are using and the answer
> >is version specific.
>
> Sorry, silly msitake.. We use oracle 8.1.7.4 running on Sparc Solaris
> 8. I would be extremely gratefull if you could point me in the
> direction as to how to find out if my hash area size is big enough.
> >
> >
> >--
> >Sybrand Bakker, Senior Oracle DBA
>
Received on Wed Jan 21 2004 - 03:21:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US