memory management in oracle 11G R2
When we talking about memory management in Oracle, we are refering to SGA and PGA. The management methold including below.
- automatic mangement
- Enable automatic memory management
- monitor and tune automatic memory management
- manual management
- Automatic shared memory management - for the SGA
- Manual shared memory management - for the SGA
- Automatic PGA memory management - for the instance PGA
- Manual PGA memory management - for the instance PGA
automatic management
In oracle 11g r2, the SGA and PGA are managed together. So to enable the automatic memory management, you can just set the a target memory size initialization parameter (MEMORY_TARGET
) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET
).The total memory that the instance uses remains relatively constant, based on the value of MEMORY_TARGET
,(Here it said the memory will be remain relatively constant. Is it possible that the memory--the sum of PGA and SGA will exceed the current setting of memory_target? ) and the instance automatically distributes memory between the system global area (SGA) and the instance program global area (instance PGA). As memory requirements change, the instance dynamically redistributes memory between the SGA and instance PGA.
MEMORY_TARGET
initialization parameter is dynamic, you can change MEMORY_TARGET
at any time without restarting the database.MEMORY_MAX_TARGET
, which is not dynamic, serves as an upper limit so that you cannot accidentally set MEMORY_TARGET
too high, and so that enough memory is set aside for the database instance in case you do want to increase total instance memory in the future. Because certain SGA components either cannot easily shrink or must remain at a minimum size, the instance also prevents you from setting MEMORY_TARGET
too low.
enable automatic memory management
If you didnt enable automatic memory management during the database creation time, you can enable it at any time. This will include a shutdown and restart of the database.
1. connect to database by sys
2. compute the mininum value for memory_target.
Determine the current sizes of SGA_TARGET and PGA_AGGREGATE_TARGET by entering the following SQL*Plus command:
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 0 memory_target big integer 0 parallel_servers_target integer 16 pga_aggregate_target big integer 90M sga_target big integer 272M
Run the following query to determine the maximum instance PGA allocated since the database was started:
select value from v$pgastat where name='maximum PGA allocated';
Compute the maximum value(Here using the max(pga_aggrgate_target,maximum PGA allocated). So it means the PGA allocated can exceed the pga_aggregate_target? Then how about the sga_target and SGA allocated? Memory_target and memory totally allocated?)
memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)
3. For the MEMORY_MAX_TARGET
initialization parameter, decide on a maximum amount of memory that you would want to allocate to the database for the foreseeable future. That is, determine the maximum value for the sum of the SGA and instance PGA sizes. This number can be larger than or the same as the MEMORY_TARGET
value that you chose in the previous step.
4. Now do the following.
If you are using text initialization parameter file. Then you can manually edit the file as below.
memory_max_target = nM memory_target = mM
Then restart the database.
If you are using server parameter file. Then you can set the memory_max_target first.
ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE;
This parameter is not dynamic so you have to specify the scope = spfile.
Then restart your database. After the database is up.
ALTER SYSTEM SET MEMORY_TARGET = nM; ALTER SYSTEM SET SGA_TARGET = 0; ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;
Here you need to know that the preceding steps instruct you to set SGA_TARGET
and PGA_AGGREGATE_TARGET
to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.
Monitor and tune automatic memory management
The dynamic performance view V$MEMORY_DYNAMIC_COMPONENTS
shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.
The view V$MEMORY_TARGET_ADVICE
provides tuning advice for the MEMORY_TARGET
initialization parameter.
SQL> select * from v$memory_target_advice order by memory_size; MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION ----------- ------------------ ------------ ------------------- ---------- 180 .5 458 1.344 0 270 .75 367 1.0761 0 360 1 341 1 0 450 1.25 335 .9817 0 540 1.5 335 .9817 0 630 1.75 335 .9817 0 720 2 335 .9817 0
The row with the MEMORY_SIZE_FACTOR
of 1 shows the current size of memory, as set by the MEMORY_TARGET
initialization parameter, and the amount of DB time required to complete the current workload. In previous and subsequent rows, the results show several alternative MEMORY_TARGET
sizes. For each alternative size, the database shows the size factor (the multiple of the current size), and the estimated DB time to complete the current workload if theMEMORY_TARGET
parameter were changed to the alternative size. Notice that for a total memory size smaller than the current MEMORY_TARGET
size, estimated DB time increases. Notice also that in this example, there is nothing to be gained by increasing total memory size beyond 450MB. However, this situation might change if a complete workload has not yet been run.
manual memory management
Automatic shared memory management
This paragraph including below topics.
-
About Automatic Shared Memory Management
-
Components and Granules in the SGA
-
Setting Maximum SGA Size
-
Setting SGA Target Size
About Automatic Shared Memory Management
Automatic Shared Memory Management simplifies SGA memory management. You specify the total amount of SGA memory available to an instance using theSGA_TARGET
initialization parameter and Oracle Database automatically distributes this memory among the various SGA components to ensure the most effective memory utilization.
Components and Granules in the SGA
We already know that Automatic SGA management means the SGA components can be resized automatically based on requirements. The memory unites used in the allocation and deallocation is called Granules here.The granule size is determined by the amount of SGA memory requested when the instance starts. Specifically, the granule size is based on the value of the SGA_MAX_SIZE
initialization parameter.
Setting maxium SGA size
The SGA_MAX_SIZE
initialization parameter specifies the maximum size of the System Global Area for the lifetime of the instance(So it means that the SGA size will never exceed the SGA_MAX_SIZE? Unlike the PGA_AGGREGATE_SIZE may be exceeded?). You can dynamically alter the initialization parameters affecting the size of the buffer caches, shared pool, large pool, Java pool, and streams pool but only to the extent that the sum of these sizes and the sizes of the other components of the SGA (fixed SGA, variable SGA, and redo log buffers) does not exceed the value specified bySGA_MAX_SIZE
.
If you do not specify SGA_MAX_SIZE
, then Oracle Database selects a default value that is the sum of all components specified or defaulted at initialization time. If you do specify SGA_MAX_SIZE
, and at the time the database is initialized the value is less than the sum of the memory allocated for all components, either explicitly in the parameter file or by default, then the database ignores the setting for SGA_MAX_SIZE
and chooses a correct value for this parameter.
Seting SGA_TARGET
One important thing you should know. The STATISTICS_LEVEL
initialization parameter must be set to TYPICAL
(the default) or ALL
for automatic shared memory management to function.
When you set SGA target, some components can be automatically based on the sga_target. Listed below
The shared pool SHARED_POOL_SIZE
The large pool LARGE_POOL_SIZE
The Java pool JAVA_POOL_SIZE
The buffer cache DB_CACHE_SIZE
The Streams pool STREAMS_POOL_SIZE
Some components need to be manually set, if they are set, they will take some space from the SGA_TARGET and the rest space will be allocated to the components above.
The log buffer LOG_BUFFER The keep and recycle buffer caches DB_KEEP_CACHE_SIZE/DB_RECYCLE_CACHE_SIZE Nonstandard block size buffer caches DB_nK_CACHE_SIZE
After setting the SGA_TARGET you can choose to set the componenet corresponding parameter to zero or not. If you set these parameters to zero, they are fully automatica management. If you set those values to non-zero values, these values will be the mininum value for these component
Monitoring and Tuning SGA Target Size
The V$SGAINFO
view provides information on the current tuned sizes of various SGA components.
The V$SGA_TARGET_ADVICE
view provides information that helps you decide on a value for SGA_TARGET
.
SQL> select * from v$sga_target_advice order by sga_size; SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ---------- --------------- ------------ ------------------- ------------------- 290 .5 448176 1.6578 1636103 435 .75 339336 1.2552 1636103 580 1 270344 1 1201780 725 1.25 239038 .8842 907584 870 1.5 211517 .7824 513881 1015 1.75 201866 .7467 513881 1160 2 200703 .7424 513881
Manual shared memory management
Do not need to care
Automatic PGA management
By default, Oracle Database automatically and globally manages the total amount of memory dedicated to the instance PGA. You can control this amount by setting the initialization parameter PGA_AGGREGATE_TARGET
. Oracle Database then tries to ensure that the total amount of PGA memory allocated across all database server processes and background processes never(Here said never. Actually it could exceed. So very interesting here. What is the point for this parameter? ) exceeds this target.
With automatic PGA memory management, sizing of SQL work areas for all dedicated server sessions is automatic and all *_AREA_SIZE
initialization parameters are ignored for these sessions. At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET
. This amount is set to the value of PGA_AGGREGATE_TARGET
minus the PGA memory allocated for other purposes (for example, session memory). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirements.
There are dynamic performance views that provide PGA memory use statistics. Most of these statistics are enabled when PGA_AGGREGATE_TARGET
is set.
-
Statistics on allocation and use of work area memory can be viewed in the following dynamic performance views:
V$SYSSTAT
V$SESSTAT
V$PGASTAT
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
-
The following three columns in the
V$PROCESS
view report the PGA memory allocated and used by an Oracle Database process:
PGA_USED_MEM
PGA_ALLOC_MEM
PGA_MAX_MEM
Manual PGA management
Don`t need to care.