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
