RE: pga_aggregate_target and CBO plan
- From: "Kerber, Andrew W." <Andrew.Kerber@xxxxxxx>
- To: RS2273@xxxxxxx, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 7 Aug 2007 14:23:25 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Shamsudeen, Riyaj
Sent: Tuesday, August 07, 2007 1:47 PM
To: oracle-l
Subject: pga_aggregate_target and CBO plan
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
------------------------------------------------------------------------------
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.
==============================================================================
Other related posts:
