Pages 338 (hash joins) and 372.

Unfortunately the comment on page 372 was the
first mistake I discovered in the book - see Errata
  http://www.jlcomp.demon.co.uk/cbo_book/ch_13.html#_Max_Area_Size:_(4th_Nov_2005)

For hash joins (at least in 9i), the cost seems to be derived
from the 'hash_area (max=)' reported in the 10053 trace rather
than the 'has_area' figure - but for sorts the cost seems to be the
other way around. The cost is derived from the 'Area Size' rather
than the 'Max Area Size'.

For sorts (9i) the Max Area Size is 5% of the pga_aggregate_target,
so changes very obviously in the trace; but the Area Size is usually
much smaller, and changes in this are less likely to have an impact
on the cost of sorting.


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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


From: "Shamsudeen, Riyaj" <RS2273@(protected)>
To: "oracle-l" <oracle-l@(protected)>
Date: Tue, 7 Aug 2007 13:46:35 -0500

       Version 9.2.0.6 and 9.2.0.8 Sun Solaris.
       What is the effect of pga_aggregate_target in CBO plan
generation, specifically in 9i? We have two environments with same
statitistics and one major difference is that pga_aggregate_target
(PAT). I have compared the section of 10053 trace listing all parameters
considered by CBO and they are exactly matching, even though PAT is very
different (2G to 10G). I have modified the parameter and restarted each
time, still there is no difference in CBO cost or 10053 section.

       Also searched Jonathan book and can't locate any reference
to this parameter. How does CBO uses pga_aggregate_target for cost
calculations?

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