Re: pga_aggregate_target and CBO plan

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Wed, 8 Aug 2007 11:44:39 +0200
Message-ID: <4ef2fbf50708080244w7095230ctcd0e2431d66e0efa@mail.gmail.com>

On 8/7/07, Shamsudeen, Riyaj <RS2273_at_att.com> 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"
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 08 2007 - 04:44:39 CDT