Grokbase › Groups › Oracle › oracle-l › August 2007
FAQ
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

Search Discussions

  • Kerber, Andrew W. at Aug 7, 2007 at 7:23 pm ⇧
    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_at_freelists.org
    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.

    --
    http://www.freelists.org/webpage/oracle-l
  • Shamsudeen, Riyaj at Aug 7, 2007 at 7:39 pm ⇧
    Andrew

    I realize how 5% of this parameter is a maximum limit for a
    serial process. But, I am really looking to understand how CBO using
    this parameter in the calculations. I thought, CBO lists all the
    parameters in the 10053 section and this parameter is quietly missing in
    that section. It looks like I might need to write a test case that will
    need huge amount hash and sort areas, to see the difference.

    But, if anybody has this knowledge already, that will be
    great!



    Thanks
    Riyaj

    From: Kerber, Andrew W.
    Sent: Tuesday, August 07, 2007 2:23 PM
    To: Shamsudeen, Riyaj; oracle-l
    Subject: RE: pga_aggregate_target and CBO plan



    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_at_freelists.org
    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.

    --
    http://www.freelists.org/webpage/oracle-l
  • Kerber, Andrew W. at Aug 7, 2007 at 7:42 pm ⇧
    Oh, well if you need to figure out what happens, why don't you try
    shrinking the pga aggregate target down to some ridiculously small size
    (like 256k) and see what happens? I would think that would be easier
    than trying to use up all of a huge area...



    -----Original Message-----
    From: Shamsudeen, Riyaj
    Sent: Tuesday, August 07, 2007 2:40 PM
    To: Kerber, Andrew W.; oracle-l
    Subject: RE: pga_aggregate_target and CBO plan



    Andrew

    I realize how 5% of this parameter is a maximum limit for a
    serial process. But, I am really looking to understand how CBO using
    this parameter in the calculations. I thought, CBO lists all the
    parameters in the 10053 section and this parameter is quietly missing in
    that section. It looks like I might need to write a test case that will
    need huge amount hash and sort areas, to see the difference.

    But, if anybody has this knowledge already, that will be
    great!



    Thanks
    Riyaj

    From: Kerber, Andrew W.
    Sent: Tuesday, August 07, 2007 2:23 PM
    To: Shamsudeen, Riyaj; oracle-l
    Subject: RE: pga_aggregate_target and CBO plan



    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_at_freelists.org
    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.

    --
    http://www.freelists.org/webpage/oracle-l
  • Gints Plivna at Aug 8, 2007 at 7:28 am ⇧
    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 :


    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
  • Alberto Dell'Era at Aug 8, 2007 at 9:44 am ⇧

    On 8/7/07, Shamsudeen, Riyaj 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
  • Amar kumar padhi at Aug 8, 2007 at 5:18 pm ⇧
    Hi,
    Jonathan has a good article at
    http://www.jlcomp.demon.co.uk/untested.html that may give some good
    insight into how memory plays with different PGA settings. I believe
    this would show up on the execution plan cost.

    Also do consider WORKAREA_SIZE_POLICY in conjunction with
    PGA_AGGREGATE_TARGET to switch on the auto memory management. Sorry I do
    not have any test cases at this moment to show significant effects on
    execution plans.

    Thanks!
    amar

    Shamsudeen, Riyaj wrote:
    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
  • Alberto Dell'Era at Aug 8, 2007 at 6:15 pm ⇧
    And if you want to go very deep, there's another paper that
    explains the algorithms in a very, very detailed way:

    "SQL Memory Management in Oracle9i" by Dageville et others:
    http://www.cs.ust.hk/vldb2002/VLDB2002-proceedings/papers/S29P03.pdf
    On 8/8/07, amar kumar padhi wrote:
    Hi,
    Jonathan has a good article at
    http://www.jlcomp.demon.co.uk/untested.html that may give some good
    insight into how memory plays with different PGA settings. I believe
    this would show up on the execution plan cost.
    --
    Alberto Dell'Era
    "the more you know, the faster you go"
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 7, '07 at 6:46p
activeAug 8, '07 at 6:15p
posts8
users5
websiteoracle.com

5 users in discussion

Shamsudeen, Riyaj: 2 posts Alberto Dell'Era: 2 posts Kerber, Andrew W.: 2 posts Amar kumar padhi: 1 post Gints Plivna: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase