Oracle ASMM

Oracle Automatic System Memory Management

This feature attempts to automatically manage memory usage of the components that make up the System Global Area (SGA).

Use of this feature IS recommended.

If this feature is enabled (SGA_TARGET is non-zero) then the following parameters will be automatically sized. Any non-zero value in the following parameters will be treated as a minimum value.

  • SHARED_POOL_SIZE

  • LARGE_POOL_SIZE

  • JAVA_POOL_SIZE

  • DB_CACHE_SIZE

  • STREAMS_POOL_SIZE

Parameters

SGA_TARGET

SGA_MAX_TARGET

Check

SHOW PARAMETER SGA_TARGET

SHOW SPPARAMETER SGA_TARGET

Check

SHOW PARAMETER SGA_MAX_TARGET

SHOW SPPARAMETER SGA_MAX_TARGET

Change

ALTER SYSTEM SET SGA_TARGET='0' SCOPE=spfile;

Restart the database

Or

only if SGA_MAX_TARGET is set at a higher value than MEMORY_TARGETAND SGA_MIN_TARGET is set at a lower value than SGA_TARGET...

ALTER SYSTEM SET SGA_TARGET='0' SCOPE=memory;

ALTER SYSTEM SET SGA_TARGET='0' SCOPE=both;

Change

ALTER SYSTEM SET MEMORY_MAX_TARGET='0' SCOPE=spfile;

Restart the database

Sizing the SGA

SELECT (

(SELECT SUM(value)

FROM V$SGA) -

(SELECT CURRENT_SIZE

FROM V$SGA_DYNAMIC_FREE_MEMORY)

)/1024/1024 "SGA_TARGET (MB)"

FROM DUAL;

V$SGA

  • Fixed Size

  • Variable Size

  • Database Buffers

  • Redo Buffers

V$SGA_DYNAMIC_FREE_MEMORY

SELECT *

FROM v$sga_target_advice;

V$SGA_TARGET_ADVICE

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE CON_ID---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- ------ 1764 .75 89015444 26.0776 3175832150 160 1104 0 2352 1 3413483 1 2445016668 384 1504 0 2940 1.25 3407680 .9983 2430346568 512 1968 0 3528 1.5 3404949 .9975 2430346568 512 2544 0 4116 1.75 3403925 .9972 2422767016 768 2832 0 4704 2 3403925 .9972 2422767016 1184 2976 0

Monitoring the SGA

SELECT *

FROM V$SGAINFO;

V$SGA_INFO

NAME BYTES RES CON_ID-------------------------------- ---------- --- ------Fixed SGA Size 8717688 No 0Redo Buffers 8056832 No 0Buffer Cache Size 536870912 Yes 0In-Memory Area Size 0 No 0Shared Pool Size 1459617792 Yes 0Large Pool Size 318767104 Yes 0Java Pool Size 100663296 Yes 0Streams Pool Size 33554432 Yes 0Shared IO Pool Size 134217728 Yes 0Data Transfer Cache Size 0 Yes 0Granule Size 16777216 No 0Maximum SGA Size 2466250752 No 0Startup overhead in Shared Pool 199573928 No 0Free SGA Memory Available 0 0

SELECT *

FROM v$sgastat

ORDER BY bytes;

V$SGASTAT

Partial output...POOL NAME BYTES CON_ID-------------- -------------------------- ---------- ------shared pool PLMCD 22189752 0streams pool free memory 32758576 0large pool session heap 43139064 0shared pool KGH: NO ACCESS 46651680 0shared pool KGLHD 47318688 0java pool JOXLE 82587456 0 shared_io_pool 134217728 0shared pool free memory 213965312 0shared pool KGLH0 224587416 0large pool free memory 275136520 0 buffer_cache 402653184 0shared pool SQLA 589895168 0

SET PAGESIZE 30

SET LINESIZE 200

COLUMN component FORMAT A30

SELECT *

FROM v$sga_dynamic_components;

V$SGA_DYNAMIC_COMPONENTS

Shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.(1)
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE CON_ID------------------------------ ------------ ---------- ---------- ------------------- ---------- ------------- --------- --------- ------------ ----------shared pool 1459617792 503316480 1459617792 0 31 GROW DEFERRED 24-JUN-21 16777216 0large pool 318767104 16777216 402653184 0 90 SHRINK DEFERRED 09-NOV-21 16777216 0java pool 100663296 16777216 100663296 0 6 SHRINK DEFERRED 24-JUN-21 16777216 0streams pool 33554432 0 33554432 0 3 SHRINK DEFERRED 24-JUN-21 16777216 0DEFAULT buffer cache 402653184 318767104 1895825408 0 131 GROW DEFERRED 09-NOV-21 16777216 0KEEP buffer cache 0 0 0 0 0 STATIC 16777216 0RECYCLE buffer cache 0 0 0 0 0 STATIC 16777216 0DEFAULT 2K buffer cache 0 0 0 0 0 STATIC 16777216 0DEFAULT 4K buffer cache 0 0 0 0 0 STATIC 16777216 0DEFAULT 8K buffer cache 0 0 0 0 0 STATIC 16777216 0DEFAULT 16K buffer cache 0 0 0 0 0 STATIC 16777216 0DEFAULT 32K buffer cache 0 0 0 0 0 STATIC 16777216 0Shared IO Pool 134217728 0 134217728 0 1 GROW IMMEDIATE 20-APR-21 16777216 0Data Transfer Cache 0 0 0 0 0 STATIC 16777216 0In-Memory Area 0 0 0 0 0 STATIC 16777216 0In Memory RW Extension Area 0 0 0 0 0 STATIC 16777216 0In Memory RO Extension Area 0 0 0 0 0 STATIC 16777216 0ASM Buffer Cache 0 0 0 0 0 STATIC 16777216 0

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SET PAGESIZE 20

SET LINESIZE 200

COLUMN component FORMAT A20

COLUMN oper_type FORMAT A8

COLUMN parameter FORMAT A18

COLUMN con_id FORMAT 999

SELECT *

FROM v$sga_resize_ops;

V$SGA_RESIZE_OPS

V$SGA_CURRENT_RESIZE_OPS

COMPONENT OPER_TYP OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME CON_ID-------------------- -------- --------- ------------------ ------------ ----------- ---------- --------- -------------------- -------------------- ------large pool GROW IMMEDIATE large_pool_size 16777216 33554432 33554432 COMPLETE 06-AUG-2021 11:35:31 06-AUG-2021 11:35:31 0large pool GROW IMMEDIATE large_pool_size 33554432 50331648 50331648 COMPLETE 06-AUG-2021 11:37:22 06-AUG-2021 11:37:22 0DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 687865856 671088640 671088640 COMPLETE 06-AUG-2021 11:37:22 06-AUG-2021 11:37:22 0DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 671088640 654311424 654311424 COMPLETE 09-AUG-2021 12:38:40 09-AUG-2021 12:38:40 0large pool GROW IMMEDIATE large_pool_size 50331648 67108864 67108864 COMPLETE 09-AUG-2021 12:38:40 09-AUG-2021 12:38:40 0large pool SHRINK DEFERRED large_pool_size 67108864 16777216 16777216 COMPLETE 09-AUG-2021 12:45:34 09-AUG-2021 12:45:34 0DEFAULT buffer cache GROW DEFERRED db_cache_size 654311424 704643072 704643072 COMPLETE 09-AUG-2021 12:45:34 09-AUG-2021 12:45:34 0DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 704643072 687865856 687865856 COMPLETE 09-AUG-2021 12:56:35 09-AUG-2021 12:56:35 0large pool GROW IMMEDIATE large_pool_size 16777216 33554432 33554432 COMPLETE 09-AUG-2021 12:56:35 09-AUG-2021 12:56:35 0DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 687865856 671088640 671088640 COMPLETE 10-AUG-2021 11:07:41 10-AUG-2021 11:07:41 0large pool GROW IMMEDIATE large_pool_size 33554432 50331648 50331648 COMPLETE 10-AUG-2021 11:07:41 10-AUG-2021 11:07:41 0large pool SHRINK DEFERRED large_pool_size 50331648 16777216 16777216 COMPLETE 11-AUG-2021 09:56:43 11-AUG-2021 09:56:43 0DEFAULT buffer cache GROW DEFERRED db_cache_size 671088640 704643072 704643072 COMPLETE 11-AUG-2021 09:56:43 11-AUG-2021 09:56:43 0large pool GROW IMMEDIATE large_pool_size 16777216 33554432 33554432 COMPLETE 09-NOV-2021 11:22:39 09-NOV-2021 11:22:39 0DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 704643072 687865856 687865856 COMPLETE 09-NOV-2021 11:22:39 09-NOV-2021 11:22:39 0DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 687865856 671088640 671088640 COMPLETE 09-NOV-2021 11:47:04 09-NOV-2021 11:47:04 0large pool GROW IMMEDIATE large_pool_size 33554432 50331648 50331648 COMPLETE 09-NOV-2021 11:47:04 09-NOV-2021 11:47:04 0