Re: pga_aggregate_target and CBO plan

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Wed, 8 Aug 2007 10:28:13 +0300
Message-ID: <6e49b6d00708080028x500c74d5q723ef1cb2405902d@mail.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_at_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

Received on Wed Aug 08 2007 - 02:28:13 CDT