Oracle Automatic memory management SGA, PGA explained

ASMM Automatic shared memory management:

Automatically adjusts to workload changes

Maximize Memory utilization

Helps eliminate out-of-memory errors

[email protected]>show parameter SGA

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
LOCK_SGA Boolean FALSE
PRE_PAGE_SGA Boolean FALSE
Sga_max_size Big Integer 1G
Sga_target Big Integer 1G
[email protected]>show parameter Memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Hi_shared_memory_address integer 0
Memory_max_target Big Integer 0
Memory_target Big Integer 0
Shared_memory_address integer 0
[email protected]>show parameter PGA

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Pga_aggregate_target Big Integer 208M

How the ASMM works:

ASMM is based on workload information captured by Mmon in the background.

Mmon uses memory guidance.

Move the memory to the Mman where it is most urgently needed.

If you use SPFILE: Save the component size on shutdown, save the value for the bootstrapper component size, and no longer determine the best value

The automatic shared memory management feature uses the SGA memory mediation implemented by the following two background processes: Manageability Monitor (Mmon) and memory Manager (Mman). Mmon regularly captures statistical information and memory guidance data into memory. Mman adjusts the size of the memory components according to the Mmon decision. The SGA memory mediation keeps track of the size of the component and the sizing action to be processed.

The SGA memory intermediary observes the system and workload to determine the ideal memory allocation scheme. The SGA memory intermediary performs this check every few minutes so that memory is always used where it is needed. If you do not have automatic shared memory management capabilities, you must anticipate the memory requirements for each component at peak time, and then adjust its memory size.

Based on the workload information, the automatic Shared memory management feature will:

Periodically capture statistics in the background

Using Memory guidance
Perform a hypothetical analysis to determine the optimal memory allocation scheme

Move memory to where it is most urgently needed

If SPFILE is used, the component size is saved on shutdown (these sizes can be re-used before the last shutdown)

To enable the automatic Shared memory management feature:

To enable ASMM from the manual shared memory management mode, do the following:

1. Get the value of the Sga_target:

Select (select SUM (value) from V$SGA) – (select Current_size from V$sga_dynamic_free_memory)) ' Sga_target ' from DUAL;

2. Use this value to set the Sga_target.

3. Set the value of the auto-size SGA component to 0.

To switch from automatic memory management mode to ASMM, do the following:
1. Set the initialization parameter Memory_target to 0.
2. Set the value of the auto-size SGA component to 0.

Automatically optimize the behavior of the SGA parameter:
Sga_target is not set or set to 0 o'clock:

Auto-Optimized parameters specify the actual size of the corresponding component
, may need to increase shared_pool_size

SELECT SUM (bytes)/1024/1024 size_mb from v$sgastat WHERE pool = ' shared pool ';

When Sga_target is set to a value other than 0:
The default value for auto-optimized parameters is zero, and the specified value is used as the lower limit size

SELECT component, current_size/1024/1024 size_mb from V$sga_dynamic_components;

Behavior of the SGA parameter optimized manually:
Some components are not automatically optimized.

--db_keep_cache_size and Db_recycle_cache_size

--Non-standard block size buffer cache, db_nk_cache_size
--
Log_buffer

These components must be configured manually using database parameters.

The memory used by these components reduces the amount of memory that can be used to automatically optimize the SGA.

To modify the Sga_target parameter:
--is a dynamic parameter
--
Can grow up to sga_max_size

--can be reduced until all components reach their lower limit size

Changing the value of Sga_target only affects automatically resized components

Sga_target is a dynamic parameter that can be changed by Database Control or by using the Alter SYSTEM command.

Ga_max_size is the upper limit of the amount of memory that can be allocated to the SGA. After you change this value, you must restart the database to take effect. The sga_target can be increased to a maximum of sga_max_size values. It can be reduced until either of the automatically optimized components reaches its lower limit size: The user-specified lower value or the internally determined lower value.

If you increase the value of sga_target, the increased portion of memory is allocated between components that are automatically optimized based on the automatic optimization policy.

If you decrease the value of sga_target, this portion of memory is extracted from one or more auto-optimized components according to the automatic optimization policy.

Assume that Sga_max_size is set to Gb,sga_target to 8 GB. If Db_keep_cache_size is set to 1 GB and the sga_target is increased to 9 GB, the increased 1 GB is only allocated between components that are sga_target controlled. The value of the db_keep_cache_size is not affected. Similarly, if you reduce sga_target to 7 GB, this 1 GB will only be extracted from those components that are controlled by Sga_target. This reduction does not affect the setting of manually controlled parameters such as db_keep_cache_size.

Disable ASMM:

Set Sga_target to 0 to disable the automatic optimization feature.

The auto-optimized parameter is set to its current size.

The SGA size is not affected in general.

eg
The value of Sga_target is 1 GB for a value of 8 gb,shared_pool_size. If the system adjusts the size of the shared pool components internally to 2 GB, setting Sga_target to 0 causes Shared_pool_size to be set to 2 GB, overwriting user-defined raw values.

To manually adjust the size of the dynamic SGA parameter

For parameters that are automatically optimized, the manual resizing is:
Causes the component size to be adjusted immediately (if the new value is greater than the current value), change the lower limit size (if the new value is less than the current size)

Adjusting the size of the manually optimized parameters only affects the adjustable portions of the SGA.

After adjusting the size of the auto-optimized parameters and setting the Sga_target value, this adjustment causes the component's size to change immediately when the new value is greater than the component's current size. For example, if Sga_target is set to 8 GB and Shared_pool_size is set to 2 GB, you can ensure that the shared pool is never less than 2 GB to meet the necessary memory allocation requirements. After that, even if the shared_pool_size value is adjusted to 1 GB, the size of the shared pool does not have a direct impact. Only causes the automatic memory optimization algorithm to reduce the size of the shared pool to 1 gigabytes (if required) later. Conversely, if the size of the shared pool is initially set to 1 GB, the size of the shared pool component is increased to 2 GB immediately when the Shared_pool_size value is adjusted to 2 GB. The memory used in this sizing operation is extracted from one or more auto-optimized components, and the size of the component that is manually optimized is not affected.

Parameters for manually resized components can also be changed dynamically, but the difference is that the value of the parameter immediately specifies the exact size of the corresponding component. Therefore, if the size of the manually resized component increases, the added portion of memory is extracted from one or more auto-sizing components. If the size of the manually resized component decreases, the freed memory is provided to the automatically resized component.

Program Global Zone (PGA)

The program Global Zone (PGA) is a memory area that contains data and control information for a server process. This is the non-shared memory that is created by the Oracle server when the server process starts, and is accessible only to that server process. The total PGA memory allocated by all server processes associated to an Oracle instance, also known as the clustered PGA memory allocated by that instance.
When using a shared server, some PGA can be located in the SGA.

PGA memory typically contains the following items:

Dedicated SQL zone:
The dedicated SQL area contains data such as binding information and the runtime memory structure. This information is unique to the SQL statement invocation of each session, and in other respects the binding variable has different values and the cursor state is different. Each session that emits an SQL statement has a dedicated SQL area. Each user who submits the same SQL statement also has its own private SQL area, which uses a shared SQL zone. In this way, many dedicated SQL zones can be associated with the same shared SQL zone. The location of the dedicated SQL zone depends on the type of connection established for the session. If the session is connected through a dedicated server, the dedicated SQL area is located in the PGA of the server process. However, if the session is connected through a shared server, a portion of the dedicated SQL area will remain in the SGA.

Cursors and SQL area
：
Application developers of Oracle PRO*C programs or Oracle OCI programs can explicitly open cursors or handles for specific private SQL areas and use them as named resources throughout the execution of the program. The recursive cursor that the database implicitly emits for some SQL statements also uses the shared SQL area.

Work area
：
For complex queries (for example, decision support queries), most of the PGA is dedicated to the workspaces allocated by memory-intensive operators, for example:
Sort-based operators (such as order BY, GROUP by, and ROLLUP) and window functions
Hash join
Bitmap merging
Bitmap creation
Write buffers used by bulk mount operations
The sort operator uses the work area (sort area) to perform in-memory sorting on a set of rows. Similarly, the hash join operator uses the workspace (hash area) to generate a hash based on its left input.
The size of the workspace can be controlled and optimized. Typically, a larger workspace can significantly improve the performance of a particular operator, but at the expense of more memory.
Session Memory
：
Session memory is the memory used to hold the session's variables (login information) and other information related to the conversation. For a shared server, session memory is shared, not private.

Automatic PGA memory Management

Dynamically adjusts the amount of PGA memory dedicated to the workspace based on the Pga_aggregate_target parameter

Helps maximize performance of all memory-intensive SQL operations

Enabled by default

PGA Management Resources
Manage statistics for Pga_aggregate_target initialization parameters, such as the PGA cache hit percentage

You can view statistics about workspace memory allocation and usage in the following dynamic performance views:
V$sysstat

V$sesstat

V$pgastat

V$sql_workarea

V$sql_workarea_active

The views used to adjust the size of the PGA workspace are:

V$pga_target_advice

V$pga_target_advice_histogram
V$sql_workarea_histogram

-----------------Oracle DB Memory parameters

ALTER SYSTEM SET memory_target=300m;
Although you only need to set Memory_target to trigger automatic memory management, you can still set lower values for various caches. Therefore, if the sub-parameters are user-set, these parameter values will be the lower values for the Oracle DB server when the component is automatically optimized.

Efficient use of memory: guidelines

Try to make the SGA suitable for physical memory.

Optimize to achieve high buffer cache hit ratios, but be aware of the following points:

-even a valid and required full-table scan reduces the hit rate.
-There may be a false rise in the hit rate due to unnecessarily repeated reads of the same piece.

Use memory guidance.

Memory optimization Guidelines for library caches: increasing hit ratios
Develop a format usage convention for the developer so that the SQL statement meets the caching requirements.

Use a binding variable.

Eliminate unnecessary duplication of SQL.

Consider using cursor_sharing.

Use PL/SQL whenever possible.

The cache sequence number.

Connects objects in the library cache.

Oracle Automatic memory management SGA, PGA explained