In 11g, Oracle database manages the PGA and SGA by default. memory_target decides the overall memory size. In this case, parameters concerning PGA and SGA will be like:
show parameter target;
NAME TYPE VALUE
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 1584M
memory_target big integer 1584M
parallel_servers_target integer 16
pga_aggregate_target big integer 0
sga_target big integer 0
show parameter workarea;
NAME TYPE VALUE
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
workarea_size_policy string AUTO
Here, memory_target specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed. However, Total memory usage can grow beyond the value of MEMORY_TARGET. For example,memory is allocated to PL/SQL tables and varrays regardless of the value of MEMORY_TARGET as long as memory is available at the operating system level.
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_retained_size integer 0
sort_area_size integer 65536
In cases where user needs to maually allocate large amount of memory for hashing and sorting, one can manually manage the PGA by:
alter session set workarea_size_policy =manual;
alter session set hash_area_size =xxxxxxxx;
alter session set sort_area_size =xxxxxxxx;
To change max memory size that Oracle can manage:
show parameter target;
NAME TYPE VALUE
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 1584M
memory_target big integer 1584M
parallel_servers_target integer 16
pga_aggregate_target big integer 0
sga_target big integer 0
show parameter workarea;
NAME TYPE VALUE
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
workarea_size_policy string AUTO
Here, memory_target specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed. However, Total memory usage can grow beyond the value of MEMORY_TARGET. For example,memory is allocated to PL/SQL tables and varrays regardless of the value of MEMORY_TARGET as long as memory is available at the operating system level.
Even when memory is automatically managed, user still find values assigned to parameter HASH_AREA_SIZE AND SORT_AREA_SIZE, don't get puzzled, this does not mean PGA is manually managed:
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_retained_size integer 0
sort_area_size integer 65536
In cases where user needs to maually allocate large amount of memory for hashing and sorting, one can manually manage the PGA by:
alter session set workarea_size_policy =manual;
alter session set hash_area_size =xxxxxxxx;
alter session set sort_area_size =xxxxxxxx;
To change max memory size that Oracle can manage:
connect "/as sysdba";
ALTER SYSTEM SET MEMORY_MAX_TARGET = 2048M SCOPE = SPFILE;
shutdown immeidate;
startup;
ALTER SYSTEM SET MEMORY_TARGET = 2048M;