Re: interpreting 10053 trace for hash join

If my understanding is correct then:

The buildfrag is the number of blocks that would be needed to hash the first table, the probefrag is the number that would be needed for the second.

Note that you have (sides swapped) - Oracle has noted that it would be sensible to hash the second table (90 blocks) and probe it with the first table (73,382 blocks).

If I recall correctly, the hash_area figure is also in blocks - since you are using P_A_T, a quick check is if the max (18,445) works out to be 5% of your P_A_T when counted
as Oracle blocks.

Note that (line 15)

    22,801 = 19 + 19,565 + 3,217

            outer + inner + hashing cost
            (one partition because this is an optimal join)

The oddity that is the first costing is something that I am still working on - the way in which Oracle reports side-swapped hash joins seems to be a little counter-intuitive, but then again the report isn't there for our benefit :(

--
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


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event)
____UK_______December (UKOUG conference)


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


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


<ctcgag_at_hotmail.com> wrote in message
news:20030929133014.083$yg_at_newsreader.com...


> Good morning everybody,

>

> I understand the NL join and (sort of) understand the SM

> sections of 10053 trace files.  But I haven't been able to figure

> out, or find on hotsos or the rest of the web or here, an

 explanation


> for the Hash section.

>

> Here's an selective excerpt from a trace file (line numbers added to

 part)


> collected from 9.2.0.2.

>

> ***************************************

> PARAMETERS USED BY THE OPTIMIZER

> ********************************

> OPTIMIZER_FEATURES_ENABLE = 9.2.0

> OPTIMIZER_MODE/GOAL = Choose

> HASH_AREA_SIZE = 131072

> HASH_MULTIBLOCK_IO_COUNT = 1

> DB_FILE_MULTIBLOCK_READ_COUNT = 16

>

>  1 HA Join

>  2   Outer table:

>  3     resc: 19565  cdn: 1477003  rcz: 395  deg: 1  resp: 19565

>  4  Inner table: ONE

>  5    resc: 19  cdn: 15517  rcz: 35  deg:  1  resp: 19

>  6    using join:8 distribution:2 #groups:1

>  7  Hash join one ptn Resc: 9145   Deg: 1   (sides swapped)

>  8      hash_area:  256 (max=18445)  buildfrag:  73382

>                   probefrag:   90 ppasses:    1

>  9  Hash join   Resc: 28729   Resp: 28729

> 10  Outer table:

> 11    resc: 19  cdn: 15517  rcz: 35  deg: 1  resp: 19

> 12  Inner table: CMPD

> 13    resc: 19565  cdn: 1477003  rcz: 395  deg:  1  resp: 19565

> 14    using join:8 distribution:2 #groups:1

> 15  Hash join one ptn Resc: 3217   Deg: 1

> 16      hash_area:  256 (max=18445)  buildfrag:  90

>                 probefrag:   73382 ppasses:    1

> 17  Hash join   Resc: 22801   Resp: 22801

> 18  Join result: cost: 22801  cdn: 15734  rcz: 430

>

> __END excerpt__

>

> lines 8 and 16 are the ones I'm most confused about.

> The buildfrag and probefrag seem to be the number

> of 8K blocks needed to store the hash tables (But I would

> think the probe table isn't stored, but rather built and

> used on the fly).  But in "hash_area: 256", it is 256 what?

> Surely not bytes.  And same for max=18445.  It doesn't seem

> to be related to the HASH_AREA_SIZE.  I thought it might come

> from PGA_A_T (which isn't listed in the PARAMETERS PARAMETERS USED

 BY THE


> OPTIMIZER section), but connecting through a shared server (which

 should


> rely on HASH_AREA_SIZE, not PGA_A_T) then forcing a hard parse

> didn't change these numbers.

>

> Of course, I also don't understand how the costs on lines 7 or 15

 are


> computed, but that's probably secondary to the sizing issue.

>

> Can anyone provide some insight?

>

> Thanks,

>

> Xho

>

> --

> -------------------- http://NewsReader.Com/ --------------------

> Usenet Newsgroup Service              New Rate! $9.95/Month 50GB