DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:user-agent:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding;
        b=Usio2akGGVwIsmlO5AcrUyLIiQa7LUwpVD51ZW+JlCmRlr7w8c5m1CF8MMoIQztt9E7nJbPjZn5MUpZNXCySiOAAEsM9rl+IqIS5scRU16nKPevBzgy5yLa5wYbgFzGteSUsDc0lvi/X30n+h7n17Q0ISHhz1Yt/buyMSwmXUTk=
Message-ID: <46B9FADA.20707@gmail.com>
Date: Wed, 08 Aug 2007 21:18:18 +0400
From: amar kumar padhi <amar.padhi@gmail.com>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: oracle-l <oracle-l@freelists.org>
Subject: Re: pga_aggregate_target and CBO plan
References: <6A4102F59ECFA248B81F7D08F0317978017F675A@TBDCEXCH01.US.Cingular.Net>
In-Reply-To: <6A4102F59ECFA248B81F7D08F0317978017F675A@TBDCEXCH01.US.Cingular.Net>
Content-Type: text/plain; charset=windows-1252; format=flowed
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: amar.padhi@gmail.com

Hi,
Jonathan has a good article at 
http://www.jlcomp.demon.co.uk/untested.html that may give some good 
insight into how memory plays with different PGA settings. I believe 
this would show up on the execution plan cost.

Also do consider WORKAREA_SIZE_POLICY in conjunction with 
PGA_AGGREGATE_TARGET to switch on the auto memory management. Sorry I do 
not have any test cases at this moment to show significant effects on 
execution plans.

Thanks!
amar


Shamsudeen, Riyaj wrote:
> Hi
> 
>             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?
> 
>  
> 
>             Doc id 246336.1 has following lines, which is not matching 
> with my observations:
> 
>  
> 
> ===============
> 
> Common Init.ora parameters that affect differences in Plan Generation are:
> 
>  
> 
> SORT_AREA_SIZE
> 
> HASH_AREA_SIZE
> 
> DB_FILE_MULTIBLOCK_READ_COUNT
> 
> PGA_AGGREGATE_TARGET (9i)
> 
> OPTIMIZER_FEATURES_ENABLED (this setting affects the default of many parameters)
> 
>  
> 
> If these are null or not set, then the default values will take effect.  
> 
> They are listed in your Oracle Database Reference manual.
> 
>  
> 
> Also, note that if you are using PGA_AGGREGATE_TARGET, all the *_AREA_SIZE
> 
> parameter settings are ignored in the init.ora and determined dynamically.
> 
> ===============
> 
>  
> 
> Thanks
> 
> Riyaj "Re-yas" Shamsudeen
> ERP Financials DBA, New AT&T
> 
>  
> 
--
http://www.freelists.org/webpage/oracle-l