Automatic Memory Management (AMM) has been available since Oracle version 11, and it does a respectable job of managing the SGA and PGA. When it’s configured, Oracle will manage both the SGA and PGA dynamically, changing pool allocations automatically as memory needs shift. If a pfile is in use the settings will revert to the initial allocations when the database is shut down and restarted. Using an spfile that behavior changes as Oracle keeps track of those memory settings so, on restart, Oracle can start where it left off. Let’s look at how, and when, Oracle records that information.
The spfile allows dynamic changes to various settings while the database is running. There are some limitations but, in general, altering the spfile also alters settings in memory. Settings such as sga_target, sga_max_size, pga_aggregate_target can be altered ‘on the fly’ for a running database in the spfile but such changes will need to wait for a restart to go into effect. Oracle takes advantage of this by automatically recording such settings in the spfile.
Given the following pfile:
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_big_table_cache_percent_target='80'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.memory_target=598m
*.memory_max_target=800m
*.undo_tablespace='UNDOTBS1'
*.use_large_pages='FALSE'
an spfile is created and put into use; at this point Oracle can write a large number of parameters to the spfile so it can begin the task of automatic memory management. Oracle begins by setting sga_target to 60% of memory_target and pga_aggregate_target to 40% of memory_target. Oracle manages allocations to both the PGA and SGA dynamically, which can be monitored through the V$MEMORY_DYNAMIC_COMPONENTS view:
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE
----------------------------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- --------- ------------
shared pool 201326592 150994944 201326592 0 3 GROW IMMEDIATE 22-JAN-19 16777216
large pool 33554432 33554432 150994944 0 1 SHRINK DEFERRED 22-JAN-19 16777216
java pool 16777216 16777216 16777216 0 0 STATIC 16777216
streams pool 0 0 0 0 0 STATIC 16777216
SGA Target 637534208 637534208 637534208 637534208 0 STATIC 16777216
DEFAULT buffer cache 352321536 234881024 352321536 0 5 GROW DEFERRED 22-JAN-19 16777216
KEEP buffer cache 0 0 0 0 0 STATIC 16777216
RECYCLE buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 2K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 4K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 8K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 16K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 32K buffer cache 0 0 0 0 0 STATIC 16777216
Shared IO Pool 16777216 0 16777216 0 1 GROW IMMEDIATE 22-JAN-19 16777216
Data Transfer Cache 0 0 0 0 0 STATIC 16777216
In-Memory Area 0 0 0 0 0 STATIC 16777216
PGA Target 218103808 218103808 218103808 218103808 0 STATIC 16777216
ASM Buffer Cache 0 0 0 0 0 STATIC 16777216
18 rows selected.
As the database runs Oracle adjusts the PGA, SGA and buffer cache to meet demand, updating the current size and, possibly, the maximum size as needs dictate.
Weekly, on Sunday mornings, a cold backup is run which shuts down the database. At that time Oracle modifies the spfile to preserve existing memory settings. If the spfile contents are examined after the shutdown the instance-specific memory parameters Oracle includes are the following (settings are instance-specific and apply to this example):
*.__data_transfer_cache_size=0
*.__db_cache_size=288M
*.__java_pool_size=16M
*.__large_pool_size=32M
*.__oracle_base='/u01/app/oracle' # ORACLE_BASE set from environment
*.__pga_aggregate_target=208M
*.__sga_target=608M
*.__shared_io_pool_size=32M
*.__shared_pool_size=224M
*.__streams_pool_size=0
Notice that these are ‘__’ parameters. These set the lower limits for these parameters, values that Oracle will not go below when adjusting memory settings. As the database resource usage changes (when session load increases/decreases, for example) Oracle will adjust these values in the spfile so that the next time the database is restarted the memory configuration starts where it left off.
Should the memory configuration of the server change (normally to increase RAM) it may be a good idea to create a pfile, minus the dynamic changes Oracle had made, to start the database after the server changes are in place. Once started an spfile can be created to start the AMM cycle anew; restarting the database puts that new spfile to use. Oracle will use the defaults when the database is started; memory areas will be adjusted based on the new memory configuration and, as expected, Oracle will update the spfile with those dynamic settings.
Even though these dynamic parameters provide minimum settings for Oracle to use, it’s not uncommon for Oracle to reduce these minimums based on the demands on the database. An increase in the number of sessions will usually result in the PGA values increasing while the SGA values decrease. Oracle won’t put itself in jeopardy with settings that are too low to handle the workload, so seeing a buffer pool decrease should not cause alarm.
An interesting observation is that, even though sga_target is set to 0 in the spfile, a ‘show parameter sga_target’ reveals the current dynamic setting Oracle has applied; this holds true for pga_aggregate_target as well. Unless changes have occurred, the values for the ‘normal’ (non-underscore) parameters will match the corresponding ‘__’ parameters written to the spfile (as illustrated above):
SYS @ orcl > show parameter target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
...
pga_aggregate_target big integer 208M
sga_target big integer 608M
SYS @ orcl $gt;
Since Oracle is managing the various memory areas, it should be expected that reallocation of resources will occur. These dynamic changes may not be immediately realized as sessions and processes may be using memory segments that are eventually destined for other pools. Looking again at the output from V$MEMORY_DYNAMIC_COMPONENTS we see:
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE
----------------------------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- --------- ------------
shared pool 201326592 150994944 201326592 0 3 GROW IMMEDIATE 22-JAN-19 16777216
large pool 33554432 33554432 150994944 0 1 SHRINK DEFERRED 22-JAN-19 16777216
...
DEFAULT buffer cache 352321536 234881024 352321536 0 5 GROW DEFERRED 22-JAN-19 16777216
...
Shared IO Pool 16777216 0 16777216 0 1 GROW IMMEDIATE 22-JAN-19 16777216
...
18 rows selected.
The large pool and default buffer cache adjustments are shown as DEFERRED; those adjustments will be made once enough memory in those components is available for re-allocation and the date and time those adjustments are made will be updated in the LAST_OPER_TIME column. Unless it was set at database creation to be anything other than the default the time will be reported as GMT, which will probably be several hours off of local clock time. [Executing “select dbtimezone from dual;” will report the offset the database is using with an offset of “+00:00”.] If the diagnostic and tuning packs are licensed, then a query of dba_hist_memory_resize_ops will provide a chronological report of component changes along with the start and end times for those changes:
COMPONENT OPER_TYPE START_TIM END_TIME TARGET_SIZE OPER_MODE INITIAL_SIZE FINAL_SIZE STATUS
---------------------------------------- ------------- --------- --------- ----------- --------- ------------ ---------- ---------
...
large pool SHRINK 24-JAN-19 24-JAN-19 33554432 DEFERRED 150994944 33554432 COMPLETE
DEFAULT buffer cache SHRINK 24-JAN-19 24-JAN-19 268435456 IMMEDIATE 285212672 268435456 COMPLETE
shared pool GROW 24-JAN-19 24-JAN-19 184549376 IMMEDIATE 167772160 184549376 COMPLETE
DEFAULT buffer cache SHRINK 24-JAN-19 24-JAN-19 251658240 IMMEDIATE 268435456 251658240 COMPLETE
DEFAULT buffer cache GROW 24-JAN-19 24-JAN-19 369098752 DEFERRED 251658240 369098752 COMPLETE
large pool SHRINK 24-JAN-19 24-JAN-19 33554432 DEFERRED 150994944 33554432 COMPLETE
DEFAULT buffer cache SHRINK 24-JAN-19 24-JAN-19 352321536 IMMEDIATE 369098752 352321536 COMPLETE
shared pool GROW 24-JAN-19 24-JAN-19 201326592 IMMEDIATE 184549376 201326592 COMPLETE
DEFAULT buffer cache SHRINK 24-JAN-19 24-JAN-19 335544320 DEFERRED 352321536 335544320 COMPLETE
shared pool GROW 24-JAN-19 24-JAN-19 218103808 DEFERRED 201326592 218103808 COMPLETE
...
Remember that Oracle will, at the time the database is shutdown, update these dynamic initialization parameters in the spfile with most recent values. Since the spfile is a binary file it’s best to run a utility that can extract text from binary files and on *nix systems that would be strings. Checking the spfile before and after shutdown should reveal the changes Oracle made to preserve the last known memory configuration.
Oracle-initiated spfile changes will change the file date/time at the operating system level (which should be expected). Of course DBA-initiated changes will also exhibit that behavior, so regular checks of the spfile contents may be warranted in active, production systems.
Database management has changed tremendously over the years and AMM is proof of that. Rarely will a DBA need to spend time calculating the memory requirements of a database, which is good as it frees the DBA up for more important tasks such as query tuning. Regular monitoring of V$MEMORY_DYNAMIC_COMPONENTS should provide all the information a DBA needs to ensure there are enough memory resources available to handle the database workload, thus keeping the user community happy. And, that is the ultimate goal the DBA has at the end of the day.
