pga_aggregate_target and CBO plan

From: Shamsudeen, Riyaj <RS2273_at_att.com>
Date: Tue, 7 Aug 2007 13:46:35 -0500
Message-ID: <6A4102F59ECFA248B81F7D08F0317978017F675A@TBDCEXCH01.US.Cingular.Net>

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

Received on Tue Aug 07 2007 - 13:46:35 CDT