Re: pga_aggregate_target and CBO plan

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