Oracle Automatic memory Management SGA, PGA detailed __oracle

ASMM Automatic shared memory management:

Automatically adjusts to workload changes

Maximize Memory utilization

Helps to eliminate Out-of-memory errors

Sys@prod>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
Sys@prod>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
Sys@prod>show parameter PGA

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

ASMM's working principle:

ASMM is based on the workload information that Mmon captures in the background.

Mmon uses memory guidance.

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

If you use SPFILE: Save component size at shutdown, saved value for bootstrapper component size, no need to determine best value

The automatic shared memory management feature uses the SGA memory mediations implemented by the following two background processes: the Manageability Monitor (Mmon) and the memory Manager (Mman). Mmon periodically captures statistics and memory-guidance data into memory. Mman adjusts the size of the memory component according to the Mmon decision. The SGA memory broker keeps track of the size of the components and the sizing operations to be processed.

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

On the basis of workload information, the automatic Shared memory management feature will:

Regularly capture statistics in the background

Using Memory guidance
Make assumption analysis to determine optimal memory allocation scheme

Move memory to where it is most needed

If SPFILE is used, save the component size at shutdown (these sizes can be reused before the last shutdown)

To enable the automatic Shared memory management feature:

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

1. Get the value of 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 SGA component that automatically sets the size to 0.

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

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

Automatically 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 an automatically optimized parameter is zero, and the specified value is used as the lower bound size

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

The behavior of manually optimized SGA parameters:
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 manually configured 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:
--A dynamic parameter
--
Can be increased to sga_max_size up to

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

Changing the value of Sga_target only affects components that are automatically resized

Sga_target is a dynamic parameter that can be changed either through the 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. Sga_target can be increased to sga_max_size values. It can be reduced until any automatically optimized component reaches its lower limit size: A user-specified lower bound value or an internally determined lower value.

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

If the value of the sga_target is reduced, this portion of memory is extracted from one or more automatically optimized components according to the automatic optimization policy.

Suppose the Sga_max_size setting is set to 8 GB gb,sga_target. If the db_keep_cache_size is set to 1 GB and the sga_target is increased to 9 GB, the increased 1 GB will only be allocated between the components that are sga_target controlled. The value of the db_keep_cache_size is unaffected. Similarly, if the sga_target is reduced to 7 GB, the 1 GB will only be extracted from those components that are controlled by Sga_target. This reduction does not affect the settings of manually controlled parameters such as db_keep_cache_size.

Disable ASMM:

Setting Sga_target to 0 disables the automatic optimization feature.

Automatically optimized parameters are set to their 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 component to 2 GB, setting the Sga_target to 0 causes the shared_pool_size to be set to 2 GB, overwriting the user-defined original values.

Manually adjust the size of the dynamic SGA parameter

For automatically optimized parameters, manual sizing will:
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 manually optimized parameters only affects the adjustable portion of the SGA.

When you adjust the size of an automatically optimized parameter and set the Sga_target value, this adjustment causes the size of the component to change immediately if the new value is greater than the current size of the component. For example, if you set the Sga_target to 8 GB and set the Shared_pool_size to 2 GB, you can ensure that the shared pool is always no less than 2 GB to meet the necessary memory allocation requirements. After that, even adjusting the shared_pool_size value to 1 GB does not have a direct effect on the size of the shared pool. Only allows the automatic memory optimization algorithm to reduce the shared pool size to 1 GB (if required) later. Conversely, if the size of the shared pool is initially set to 1 GB, the shared pool component's size increases immediately to 2 GB when the Shared_pool_size value is adjusted to 2 GB. The memory used in this sizing operation is extracted from one or more automatically optimized components, and the size of the manually optimized components is unaffected.

The parameters of a manually resized component 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 a manually tuned component increases, the added portion of memory is extracted from one or more automatically sized components. If the size of a manually tuned component is reduced, the freed memory is provided to the automatically sized component.

Program Global Area (PGA)

The program Global Area (PGA) is a memory area that contains data and control information for a server process. This is the unshared memory that is created by the Oracle server when the server process is started, and only the server process can access it. The total amount of PGA memory allocated by all server processes associated with an Oracle instance, also known as the aggregated PGA memory allocated by that instance.
When using a shared server, part of the PGA can be located in the SGA.

PGA memory typically contains the following:

Private SQL Area:
A private SQL zone contains data such as binding information and run-time memory structures. This information is specific to the SQL statement invocation of each session, and in other respects the binding variable has different values and the cursor's 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 zone, which uses a shared SQL zone. As a way, many dedicated SQL zones can be associated with the same shared SQL zone. The location of the private SQL zone depends on the type of connection established for the session. If the session is connected through a dedicated server, the private SQL zone is located in the PGA of the server process. However, if the session is connected through a shared server, some of the dedicated SQL zones remain in the SGA.

Cursors and SQL extents
：
Application developers of Oracle PRO*C programs or Oracle OCI programs can explicitly open cursors or handles for a particular private SQL zone and use them as named resources throughout the execution of the program. A recursive cursor implicitly emitted by a database for some SQL statements also uses a shared SQL area.

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

Automatic PGA memory Management

According to the Pga_aggregate_target parameters, dynamically adjust the PGA memory for the working area

Helps maximize performance for all memory-intensive SQL operations

is enabled by default

PGA Management Resources
Manage statistics for Pga_aggregate_target initialization parameters, such as percentage of PGA cache Hits

You can view statistics about the allocation and use of workspace memory 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 PGA workspace size 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 up Memory_target to trigger automatic memory management, you can still set a lower bound value for various caches. Therefore, if the child parameters are set by the user, these parameter values will be the lower bound value when the Oracle DB server automatically optimizes the component.

Effective use of memory: guidelines

Try to fit the SGA to physical memory.

Optimized to achieve high buffer cache hit rates, note the following points:

-even effective and required full table scans can reduce the hit rate.
--There may be instances where the hit rate is dashed due to unnecessary repetition of the same piece.

Use memory guidance.

Memory optimization guidelines for library caching: improving hit Ratio
Develop format usage conventions for developers so that SQL statements meet the requirements of caching.

Use binding variables.

Eliminates unnecessary duplication of SQL.

Consider using cursor_sharing.

Use pl/sql whenever possible.

The cache sequence number.

The object in the connection library cache.