Re: oracle-l Digest V4 #223

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 10 Aug 2007 11:30:00 +0100
Message-ID: <001601c7db39$6879cca0$0200a8c0@Primary>

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_at_xxxxxxx> To: "oracle-l" <oracle-l_at_xxxxxxxxxxxxx>
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

Received on Fri Aug 10 2007 - 05:30:00 CDT