interpreting 10053 trace for hash join

From: <ctcgag_at_hotmail.com>
Date: 29 Sep 2003 17:30:14 GMT
Message-ID: <20030929133014.083$yg@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

Received on Mon Sep 29 2003 - 12:30:14 CDT