Re: pga_aggregate_target and CBO plan
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: RS2273@xxxxxxx
- Date: Wed, 8 Aug 2007 11:44:39 +0200
On 8/7/07, Shamsudeen, Riyaj <RS2273@xxxxxxx> wrote:
> 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.
You should check (I tried in 9.2.0.8) the GENERAL PLANS section;
using a simple sort test case:
create table t (x varchar2(1000));
insert into t select rpad (rownum, 100) from dual connect by level <= 10000;
exec dbms_stats.gather_table_stats (user, 't');
explain plan for select * from t order by x;
I get, with PAT=100M:
GENERAL PLANS
***********************
Join order[1]: T[T]#0
ORDER BY sort
SORT resource Sort statistics
Sort width: 14 Area size: 131072 Max Area size:
5242880 Degree: 1
and with PAT=10M:
GENERAL PLANS
***********************
Join order[1]: T[T]#0
ORDER BY sort
SORT resource Sort statistics
Sort width: 2 Area size: 131072 Max Area size:
524288 Degree: 1
So it's the "Max Area size" that changes, here it's exactly 5% of PAT
[ 0.05 * (10*1024*1024) = 524288] since I'm the only one on this test instance
and so it is set to the max (5% of PAT for a noparallel execution); I could
get less of course if there were other sessions actively sorting/hashing
right now.
In my case the cost of the sort changes, but of course it might not -
if e.g. the data to sort were so small that the sort could complete in memory
both with PAT=100M and PAT=10M.
HTH
Alberto
--
Alberto Dell'Era
"the more you know, the faster you go"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
