Manage Instance Memory
The goals of instance memory management are:
- Minimize I/O
- Minimize hard parses
- Minimize disk sorts
They are accomplished by setting initialization parameters which allocate memory to instance structures. This is an iterative process. The initial allocation can be made based on rules of thumb. Refinements can then be made based on results of memory monitoring during typical workloads. Note that instance memory tuning should be undertaken only after application tuning has been completed.
1. Identify available memory
Let’s assume a Linux machine has 12 GB of memory. As a starting point, leave 3 GB (25%) for the operating system, and allocate the remaining 9 GB to Oracle. Monitor swapping and make further adjustments. The goal is to have little or no swapping. Reduce file system buffers to a minimum, as they are not useful to Oracle.
2. Implement Initial Allocation
Using AMM (automatic memory management) is easier and provides more flexibility. However, it has some pitfalls. Under certain workloads, the PGA can balloon and squeeze out the buffer and library caches to ridiculously low levels! For this reason, some safeguards are required. First, set a minimum for sga_target. This should reflect the minimum memory required to eliminate/minimize library cache misses, and keep the buffer hit ratio above 90 percent. Second, set a maximum for the PGA. This may cause the database to terminate some processes, but this is preferable to the entire database hanging.
If an spfile is being used, create an init file from it. Edit the init file and set the following parameters, as a starting point. Delete all other memory parameters.
memory_target=9g # total memory available for instance sga_target=2304m # minimum 25% for SGA pga_aggregate_limit=6912m # maximum 75% for PGA
3. Shared Server Precautions
When using shared server, some PGA memory components are moved to the SGA. For this reason, setting a minimum for the SGA is not sufficient. To protect the library and dictionary caches, and the buffer cache, a minimum needs to be set for the shared pool and the buffer cache. In addition, the large pool needs to be configured. As a first step, set these additional memory parameters in the init file.
shared_pool_size=460m # 20% of sga_target db_cache_size=1382m # 60% of sga target large_pool_size=230m # 10% of sga target
4. Monitor and Adjust
After running a typical workload, review diagnostic data to determine if adjustments are required. When using AMM, v$memory_target_advice is the first stop. It contains tuning advice for memory_target. Note that this information represents all data since instance startup, and may not be very useful; it may average out and thus hide peaks which need attention. To get data for specific time periods, us dba_hist_memory_target_advice.
In most cases, this setup will provide satisfactory performance, as long as there is sufficient memory available to handle the workload. If performance is not satisfactory, or memory consumption needs to be reduced, then available memory needs to be manually divided into SGA and PGA, as described in the next step.
5. Set SGA and PGA
Instead of using AMM as described above, an alternate approach is to manually allocate memory to the SGA and PGA. The first step is to determine the kind of workload. For OLTP or general purpose applications, allocate 80% to the SGA, and 20% to the PGA. These applications tend to query the same data, and saving it in memory helps reduce IO. This requires more memory for the SGA.
For data warehouse applications, divide the available memory equally between the SGA and PGA. These applications have different characteristics. They perform sorts and merges on large amounts of data. These operations are performed in work areas, which are part of the PGA. The data they retrieve is not usually shared, and often uses direct path reads which bypass the SGA. For these reasons, they benefit from a larger PGA. The SGA can be reduced because it provides limited benefits.
Start by setting sga_target and pga_aggregate_target to the values calculated above. After running a typical workload, adjust the size of the SGA based on data in v$sga_target_advice and dba_hist_sga_target_advice. The PGA can be adjusted based on v$pga_target_advice and dba_hist_pga_target_advice. The procedure is similar to the one for adjusting memory target, as described in the previous step.
6. Manual Approach
If setting SGA and PGA sizes does not yield acceptable results, then you can implement a fully manual approach. This requires detail knowledge about the application. There is a variety of features available to implement this approach. The following is by no means an exhaustive treatment of the subject. It merely serves to briefly identify the most common options available to the DBA.
The first step is to configure the shared pool. Typically, this does not require much memory. However, an undersized shared pool can significantly impact performance. Set the initial size of shared_pool_size based upon application knowledge, and then monitor and make adjustments. Information for these adjustments is available in v$shared_pool_advice, dba_hist_shared_pool_advice, v$libraryache, dba_hist_librarycache, v$rowcache, and dba_hist_rowcache_summary. The goal is to eliminate, or minimize cache misses by making sure that there is enough memory to store all required SQL, PLSQL, and dictionary information.
The large pool should be configured if you are using shared server, parallel query, or RMAN. Information for sizing the large pool is available in v$sesstat in rows for ‘session uga memory’ and ‘session uga max memory’.
The second step is to configure the data buffer. This is done by setting the db_cache_size parameter. The initial value can be fine-tuned using information in v$db_cache_advice and dba_hist_db_cache_advice. The goal is to have a hit ratio above 90 percent. If you have multiple block sizes, then you need a different pool for each one of them. These are configured using the db_nk_cache_size parameters. Information for tuning them is available in v$buffer_pool_statistics and dba_hist_buffer_pool_stat.
Sometimes, the hit ratio can be improved by configuring the optional keep and recycle pools, and assigning database objects to them. Using the result cache can improve performance by eliminating unnecessary executions of queries and functions. The in memory column store is another feature which can improve performance for some queries; however, it takes up memory in the buffer cache.
The third and final step is to size the PGA. Oracle does not recommend manually sizing work areas, unless the instance is configured with the shared server option. This means pga_aggregate_target should be set to the value determined for PGA size, and allow Oracle to automatically determine work area sizes. The goal is to eliminate multi-pass executions. Ideally, all executions should be in optimal mode. However, if available memory is not sufficient, then a one-pass execution is used, increasing response time. Tuning information is available in v$sql_workarea_histogram and dba_hist_sql_workarea_hstgrm.
Demo
The examples below use a Linux guest on Oracle VBox. The first example implements the Automated Memory Management approach described in this post. The second one demonstrates how memory required for an instance can be reduced to a bare minimum.
Example 1
The Linux host has 12 GB of memory, out of which 9 GB area initially allocated to the instance. All memory settings and precautions described above for using AMM are included in the init file.
$ uname -a Linux d12c1.localdomain 4.1.12-37.5.1.el6uek.x86_64 #2 SMP Thu Jun 9 15:56:37 PDT 2016 x86_64 x86_64 x86_64 GNU/Linux $ . oraenv ORACLE_SID = [db1] ? db1 The Oracle base remains unchanged with value /u01/app/oracle $ cd $ORACLE_HOME/dbs oracle@d12c1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs [db1] $ ls -ltr *db1*.ora -rw-r--r--. 1 oracle oinstall 791 Jul 4 11:18 initdb1.ora
$ cat initdb1.ora audit_file_dest='/u01/app/oracle/admin/db1/adump' audit_trail='db' compatible='12.1.0.2.0' control_files='/u01/oradata/db1/db1/control01.ctl','/u01/oradata/db1/db1/control02.ctl' db_block_size=8192 db_domain='' db_name='db1' diagnostic_dest='/u01/app/oracle' dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)' undo_tablespace='UNDOTBS1' # # memory management parameters # memory_target=9g # total memory available for instance sga_target=2304m # minimum 25% for SGA pga_aggregate_limit=6912m # maximum 75% for PGA shared_pool_size=460m # 20% of sga_target db_cache_size=1382m # 60% of sga target large_pool_size=230m # 10% of sga target
$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 4 11:21:10 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance.
SQL> startup ORACLE instance started. Total System Global Area 9663676416 bytes Fixed Size 2934168 bytes Variable Size 8053066344 bytes Database Buffers 1577058304 bytes Redo Buffers 30617600 bytes Database mounted. Database opened. SQL>
Example 2
This example demonstrates what happens when no memory parameters are set. The instance uses only 260 MB of SGA and a PGA target of 10 MB.
$ uname -a
Linux d12c1.localdomain 4.1.12-37.5.1.el6uek.x86_64 #2 SMP Thu Jun 9 15:56:37 PDT 2016 x86_64 x86_64 x86_64 GNU/Linux
$ . oraenv
ORACLE_SID = [db1] ? db1
The Oracle base remains unchanged with value /u01/app/oracle
$ cd $ORACLE_HOME/dbs
oracle@d12c1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs [db1]
$ ls -ltr *db1*.ora
-rw-r--r--. 1 oracle oinstall 377 Jul 4 13:17 initdb1.ora
$ cat initdb1.ora
audit_file_dest='/u01/app/oracle/admin/db1/adump'
audit_trail='db'
compatible='12.1.0.2.0'
control_files='/u01/oradata/db1/db1/control01.ctl','/u01/oradata/db1/db1/control02.ctl'
db_block_size=8192
db_domain=''
db_name='db1'
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'
undo_tablespace='UNDOTBS1'
#
# use defaults for all memory parameters
#
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 4 13:18:59 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 272629760 bytes
Fixed Size 2923336 bytes
Variable Size 213910712 bytes
Database Buffers 50331648 bytes
Redo Buffers 5464064 bytes
Database mounted.
Database opened.
SQL> select name,value/1024/1024 MB from v$parameter where name in ('memory_target','sga_target','pga_aggregate_target');
NAME MB
------------------------ ----------
sga_target 0
memory_target 0
pga_aggregate_target 10
SQL> select * from v$sgainfo;
NAME BYTES RES CON_ID
------------------------------- ---------- --- ----------
Fixed SGA Size 2923336 No 0
Redo Buffers 5464064 No 0
Buffer Cache Size 50331648 Yes 0
In-Memory Area Size 0 No 0
Shared Pool Size 209715200 Yes 0
Large Pool Size 0 Yes 0
Java Pool Size 4194304 Yes 0
Streams Pool Size 0 Yes 0
Shared IO Pool Size 4194304 Yes 0
Data Transfer Cache Size 0 Yes 0
Granule Size 4194304 No 0
Maximum SGA Size 272629760 No 0
Startup overhead in Shared Pool 145462768 No 0
Free SGA Memory Available 0 0
14 rows selected.
SQL>