Tag Archive: Instance Memory

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>