Re: Optimizer Choices - Part Two

["jaromir nemec" <jaromir@xxxxxxxxxxxx>]

Hi Robert,



>The question was, why didn't Oracle choose the plan with the smaller
numbers
>of IO's.



The comparison of LIO in this case may be misleading. The LIO corresponds to
the cost of *reading* of the two row sources, not to the cost of the *join*.

In theory the costs of reading row sources are something O(n) compared to
cost of join O(n2).  To illustrate it - using an example of one proof of
concept - a nontrivial hash join took 5 minutes to read the two tables and
45 minutes to join them (at least it was my interpretation of what was going
on while comparing the curves of I/O and CPU consumption).

While verifying the optimiser's job you must take in account  the CPU cost
(contained in elapsed time, as correctly pointed out by Wolfgang);
additionally if the hash join cannot be completely performed in memory there
are direct path reads/writes. The cost of reading of the row sources can be
in most cases (except for very small tables)  neglected.



In my experience (experimenting on 8i), I didn't observe a significant
influence of parallel hash join order of two tables of different size on the
elapsed time (in case that both tables are orders of magnitude larger than
the degree of parallelism - e.g. 1 million records and 5 millions records).

If one of the tables is very small you may significantly profit using
broadcast distribution. Negatively formulated: don't use a parallel hash
join with DOP higher than the number of records in the smaller table without
enabling broadcast distribution:)



But there are lots of new features relevant to hash join since 8i including:
* PGA memory management

* partition wise join
so my observation doesn't need to be really up to date.



I'd appreciate to hear about cases where it is really possible to *tune* a
hash join, i.e. to get via hints a significantly better performance than the
default one selected by the optimiser.

Any idea on the list?



regards



Jaromir D.B. Nemec



----- Original Message ----- 
From: "Freeman Robert - IL" <FREEMANR@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, October 01, 2004 8:59 PM
Subject: Optimizer Choices - Part Two


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

• References:
  • Optimizer Choices - Part Two
    • From: Freeman Robert - IL

Other related posts:

Optimizer Choices - Part Two

Re: Optimizer Choices - Part Two

Re: Optimizer Choices - Part Two

Re: Optimizer Choices - Part Two