dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted February 28, 2019

Oracle's Automatic Memory Management and the SPFILE

By David Fitzjarrell

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.



Oracle Archives




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM