AMM (pga_aggregate_target)

[BeefStu]

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

[stecal]

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?

[BeefStu]

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?