AMM (pga_aggregate_target)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: AMM (pga_aggregate_target)

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    AMM (pga_aggregate_target)

    I am considering converting to AMM but I have a few questions hopefully
    that can be answered here first.

    If I am using AMM I set pga_aggregate_target=0 does this mean oracle will
    allocate/dealllocate memory as needed for the the pools that make up
    the PGA?

    I have the following settings:

    NAME TYPE VALUE

    ------------------------------------ -------------------------------- ------------------------------

    bitmap_merge_area_size integer 1048576
    create_bitmap_area_size integer 8388608
    hash_area_size integer 0
    sort_area_size integer 0
    workarea_size_policy string auto


    show parameter pga_aggregate_target

    NAME TYPE VALUE

    ------------------------------------ -------------------------------- ------------------------------

    pga_aggregate_target big integer 0


    cat init.ora

    *.workarea_size_policy='auto'

    I read some where

    http://arjudba.blogspot.com/2008/05/...parameter.html

    If the pga_aggregate has a non zero value (my example it does not). The
    workarea_size_policy should be set to manual. In my example, it is set
    to auto (most likely because I have it set in the init.ora file).

    My question is what forces areas that use memory-intensive SQL operators such as sort, group-by, hash-join, bitmap merge, and bitmap to be
    automitically sized? Is it setting the pga_aggregate_target=0 or i is it
    having a workarea_size_policy=auto. Do I really need to set workarea_size_policy='auto' in my init.ora file if I am using AMM?


    My second question, is there a way to monitor AMM vs setting parameters
    manually. I read that AMM should really not be used for OLTP type of systems,
    though the artice was written for Oracle 10g and I am running 11.1.0.7

    Thanks to all who answer

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    AMM did not come out until 11g. Which AMM in 10g are you referring to? There is ASMM, but that is not the same as AMM in 11g. Close, but different enough. Where did you read not to use AMM for OLTP systems?

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    http://www.dba-oracle.com/t_amm_auto...ory_resize.htm

    Here is one article... As for the OLTP, I will keep searching and if I find
    the article I will post the thread.

    Has anybody experienced any issues with AMM? Is there a way to measure,
    which method is better or worse?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width