RE: pga_aggregate_target and CBO plan

From: Shamsudeen, Riyaj <RS2273_at_att.com>
Date: Tue, 7 Aug 2007 14:39:32 -0500
Message-ID: <6A4102F59ECFA248B81F7D08F0317978017F6771@TBDCEXCH01.US.Cingular.Net>

Andrew

I realize how 5% of this parameter is a maximum limit for a serial process. But, I am really looking to understand how CBO using this parameter in the calculations. I thought, CBO lists all the parameters in the 10053 section and this parameter is quietly missing in that section. It looks like I might need to write a test case that will need huge amount hash and sort areas, to see the difference.

But, if anybody has this knowledge already, that will be great!

Thanks
Riyaj

From: Kerber, Andrew W. [mailto:Andrew.Kerber_at_umb.com] Sent: Tuesday, August 07, 2007 2:23 PM
To: Shamsudeen, Riyaj; oracle-l
Subject: RE: pga_aggregate_target and CBO plan

Pga_aggregate_target sets the maximum amount of memory available for user processes that run against oracle. I don't know for sure how the cbo uses this setting, but it is possible that one reason the plan doesn't change is that you are not using the maximum amount of memory in either instance, so there would be nothing to force the plan to change.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Shamsudeen, Riyaj Sent: Tuesday, August 07, 2007 1:47 PM
To: oracle-l
Subject: pga_aggregate_target and CBO plan

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

NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

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

Received on Tue Aug 07 2007 - 14:39:32 CDT