DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=X2E6wUCeTLSVIpQJG91MT4M7zCM+etZjTNKoAzmjC3FT4DHiEsOsaRXy7q0Xp4uBEO2ypqfjb7cUwq7pk/sfHckzKv51Cqt97LfkFtdAZ3r2i1R5IjcAnqGyEv/90JzEZwSaovET9sAEGRie/XuyqgdFft1aPWtxAjjaCc0nSlA=
Message-ID: <6e49b6d00708080028x500c74d5q723ef1cb2405902d@mail.gmail.com>
Date: Wed, 8 Aug 2007 10:28:13 +0300
From: "Gints Plivna" <gints.plivna@gmail.com>
To: RS2273@att.com
Subject: Re: pga_aggregate_target and CBO plan
Cc: oracle-l <oracle-l@freelists.org>
In-Reply-To: <6A4102F59ECFA248B81F7D08F0317978017F675A@TBDCEXCH01.US.Cingular.Net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Disposition: inline
References: <6A4102F59ECFA248B81F7D08F0317978017F675A@TBDCEXCH01.US.Cingular.Net>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: gints.plivna@gmail.com

pga_aggregate_target matters, see example below (for the original
example thanks to Christian Antognini)
SQL> create table source as select * from dba_source;

Table created.

SQL> insert into source select * from source;

621218 rows created.

SQL> /

1242436 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user, 'source')

PL/SQL procedure successfully completed.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0       Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

SQL> set autot traceonly explain
SQL> alter system set pga_aggregate_target = 100M;

System altered.

SQL> select sum(s.line) from source s, source s1
  2  where s.owner = s1.owner
  3    and s.name = s1.name
  4    and s.type = s1.type
  5    and s.text = s1.text
  6    and s.line = s1.line;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=38231 Card=1 Byt
          es=302)

   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=38231 Card=1 Bytes=302)
   3    2       TABLE ACCESS (FULL) OF 'SOURCE' (Cost=7072 Card=248487
          2 Bytes=375215672)

   4    2       TABLE ACCESS (FULL) OF 'SOURCE' (Cost=7072 Card=248487
          2 Bytes=375215672)


SQL> alter system set pga_aggregate_target = 1000M;

System altered.

SQL> select sum(s.line) from source s, source s1
  2  where s.owner = s1.owner
  3    and s.name = s1.name
  4    and s.type = s1.type
  5    and s.text = s1.text
  6    and s.line = s1.line;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=26452 Card=1 Byt
          es=302)

   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=26452 Card=1 Bytes=302)
   3    2       TABLE ACCESS (FULL) OF 'SOURCE' (Cost=7072 Card=248487
          2 Bytes=375215672)

   4    2       TABLE ACCESS (FULL) OF 'SOURCE' (Cost=7072 Card=248487
          2 Bytes=375215672)

If your pga_aggregate_target is big and involved tables small enough
you won't see any difference in costs, i.e. it seems there is some
threshold when increasing pga_aggregate_target won't decrease cost any
more.

Gints Plivna
http://www.gplivna.eu


2007/8/7, Shamsudeen, Riyaj <RS2273@att.com>:
>
>
>
> 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
>
>
--
http://www.freelists.org/webpage/oracle-l