Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Optimizer Choices - Part Two

Re: Optimizer Choices - Part Two

From: jaromir nemec <>
Date: Sun, 3 Oct 2004 22:06:36 +0200
Message-ID: <0c0101c4a984$7eef0f70$3c02a8c0@JARAWIN>

Hi Robert,

>The question was, why didn't Oracle choose the plan with the smaller
>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

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?


Jaromir D.B. Nemec

Received on Sun Oct 03 2004 - 15:05:25 CDT

Original text of this message