Re: CBO - hash join vs nested loops


You seem to confuse object statistics, which gather statistics about tables, columns, and indexes, with system statistics which gather statistics about cpu speed, average # of block in a multi-block read, average speed of single-block and of multi-block reads and statistics about parallel IO operations.
With system statistics in place and cpu costing enabled (the default if system statistics are present) the optimizer will have information about the different costs of single-block and multi-block IO and will also take the cpu cost of operations into account.

Laimutis Nedzinskas wrote:

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
The first STRATEGIC step is to get the system statistics
set so that the optimizer has a truthful image about the
time cost of multiblock reads, and the CPU cost of
doing tablescans.

Can you elaborate a bit more what can be considered for this truthful
image apart from the straightforward "compute all for all cascade all"? The only thing left seems to be the histograms which, yes, opens a lot


-- Regards

Wolfgang Breitling
Centrex Consulting Corporation

Other related posts: