Oracle’s Automatic Memory Management and the SPFILE

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.

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles