Tuning Oracle Memory Usage


Accessing parts of the database already loaded into memory is much faster than accessing from disk. The other important factor is that of distribution of available virtual memory resources to the different memory structures available in Oracle. Memory should be tuned after SQL and application code and before I/O access speed. Oracle divides memory resources into separate buffers.

The Buffer Cache

The database buffer cache needs to be large enough such that neccessary data is not purged from memory and not too large such that performance is affected. The init.ora parameters file contains four parameters affecting memory assigned to an Oracle database instance.

  1. DB_BLOCK_SIZE.
  2. DB_BLOCK_BUFFERS.
  3. SHARED_POOL_SIZE.
  4. SORT_AREA_SIZE.

The following query shows the values for the above parameters.

select name, value from v$parameter
where name in ('db_block_buffers','db_block_size','shared_pool_size','sort_area_size');

NAME			VALUE
-----------------------	-----------------------
shared_pool_size	3500000
db_block_buffers	200
db_block_size		2048
sort_area_size		65536

The SGA contents values are shown in more depth in the query shown below.

select name, value from v$parameter 
where name in
(
	 'db_block_buffers'
	,'db_block_size'
	,'shared_pool_size'
	,'shared_pool_size'
	,'sort_area_size'
	,'sort_area_retained_size'
	,'db_file_multiblock_read_count'
	,'large_pool_size'
	,'log_buffer'
	,'java_pool_size'
	,'buffer_pool_keep'
	,'buffer_pool_recycle'
	,'cpu_count'
	,'sort_multiblock_read_count'
)
order by name; 

cpu_count                       2
shared_pool_size                481380352
large_pool_size                 15782880
java_pool_size                  20971520
db_block_buffers                176286
buffer_pool_keep
buffer_pool_recycle             
db_block_size                   8192
log_buffer                      32768
db_file_multiblock_read_count   8
sort_area_size                  1572864
sort_area_retained_size         0
sort_multiblock_read_count      2
parallel_automatic_tuning       TRUE

The following query is a selection from the V$SGASTAT performance view. The V$SGASTAT view provides a more in depth view of the SGA.

select * from v$sgastat;

POOL        NAME                       BYTES     
----------- -------------------------- ----------
            fixed_sga                       65476
            db_block_buffers           1444134912
            log_buffer                      65536
shared pool free memory                 477268740
shared pool miscellaneous                  362420
shared pool DML locks                       30624
shared pool State objects                  125740
shared pool KQLS heap                      621404
shared pool Checkpoint queue               147528
shared pool PX subheap                       6176
shared pool ktlbk state objects             28248
shared pool latch nowait fails or sle       36000
shared pool java static objs                37208
shared pool db_files                       426280
shared pool KGFF heap                        5900
shared pool db_handles                      36400
shared pool enqueue_resources               94176
shared pool PL/SQL MPCODE                    6288
shared pool fixed allocation callback         640
shared pool PL/SQL DIANA                   226732
shared pool VIRTUAL CIRCUITS                92232
shared pool dlo fib struct                  40980
shared pool KGK heap                        17568
shared pool SYSTEM PARAMETERS               58800
shared pool dictionary cache               274240
shared pool message pool freequeue         124552
shared pool trigger inform                    240
shared pool character set memory            31196
shared pool db_block_buffers             23974896
shared pool library cache                 1137992
shared pool transactions                    58080
shared pool sql area                      1491784
shared pool processes                       37600
shared pool sessions                       118800
shared pool table columns                   16532
shared pool PLS non-lib hp                   2096
shared pool event statistics per sess      189120
shared pool db_block_hash_buckets         3082776
shared pool file # translation table        65572
large pool  free memory                  15659988
large pool  PX msg pool                    122892

DB_BLOCK_SIZE

The DB_BLOCK_SIZE determines the database block size, the most granular unit of physical storage in an Oracle database. Thus data is read from the disk in blocks. Therefore the bigger the blocks are then the more data is read for each disk I/O. Oracle8i can be set as high as 32K. Larger block sizes are generally more appropriate for multimedia containing databases or batch processing databases withg large queries. OLTP databases accessing small amounts of data concurrently would be better with a 2K block size since more precise and smaller amounts of data are required to be accessed at once.

DB_BLOCK_BUFFERS

The DB_BLOCK_BUFFERS determines the size of the area in memory inside the SGA reserved for the database block buffer cache. The buffer cache temporarily stores the most recently read database blocks in memory such that subsequent access to those same blocks can be read directly from memory without any disk I/O required. Thus the larger the DB_BLOCK_BUFFERS parameter then the more blocks can be retained in memory at once. Setting this parameter too low causes frequently used data to be flushed from the buffer too soon thus requiring disk I/O on subsequent reads. Setting this parameter too high can limit memory space for other requirements.

The DB_BLOCK_BUFFERS setting efficiency can be analysed by measurement of the buffer hit ratio. This ratio is the ratio of accesses from the database block buffer cache to that of disk I/O activity since the required blocks were flushed from memory due to lack of use. A hit ratio of less than 95% is not desirable. Set the database buffer cache size to retain 25% of the SGA memory area depending of user numbers.

The database buffer cache can be avoided. Sorting, parallel reads and full table scans do not utilise the database buffer cache. When increasing the DB_BLOCK_BUFFERS parameter ensure that the operations being tuned for are actually using the database buffer cache. Also ensure that memory available to Oracle is increased aswell if so required otherwise excessive paging and swapping can result. The script CATPARR.SQL will create a view called V$BH. The V$BH view will file and block numbers currently occupying the shared global area.

select v$bh.file# "File", count(v$bh.block#) "Blocks", count(distinct v$bh.file#||v$bh.block#) "Blocks per File" from v$bh group by v$bh.file#;

     File    Blocks Blocks per File
--------- --------- ---------------
        1      1014            1014
        2         2               2
        3         8               8

SHARED_POOL_SIZE (The Shared Pool)

The SHARED_POOL_SIZE parameter allocates memory to caching of the library and data dictionary. Measure the memory to disk I/O hit ratio to determine efficiency. 95% is a desired ratio. If the shared pool is not large enough resources are consumed managing limited space.

Large memory requests can sometimes be unable to find unfragmented sections of memory. The SHARED_POOL_RESERVED_SIZE reserves space in the shared pool for large memory requests. Set SHARED_POOL_RESERVED_SIZE to 10% of SHARED_POOL_SIZE. The higher the value of SHARED_POOL_RESERVED_SIZE will cause the database to request more memory from the shared pool list and less from the reserved list. It is best to set SHARED_POOL_RESERVED_SIZE such memory requests are catered for by the reserved list without causing shared pool flushing. Flushing implies removal of items previously loaded into memory. Flushing can lead to defragmentation of the shared pool and removal from memory of commonly used items. The best tuned system will have no misses and no failures.

SQL> select request_misses,request_failures,free_space from v$shared_pool_reserved;

REQUEST_MISSES REQUEST_FAILURES FREE_SPACE
-------------- ---------------- ----------
             0                0     512000

When REQUEST_FAILURES is greater than zero and increasing then increase the value of SHARED_POOL_RESERVED_SIZE. Conversely if REQUEST_MISSES is zero and constant or FREE_MEMORY exceeds SHARED_POOL_RESERVED_SIZE by 50% then the SHARED_POOL_RESERVED_SIZE parameter is not large enough.

Library Cache

The library cache will contain parsed SQL statements and PL/SQL objects, ie. procedures, packages and triggers. The hit ratio calculation is shown below. Also check the reload column in the V$LIBRARYCACHE. A reloaded statement is a statement which was in memory but had to be reloaded again after the server had already purged it from memory. Increase the SHARED_POOL_SIZE if the value of the reload column is greater than zero or the hit ratio as shown below is less than 95%.

select (1 - (sum(decode(name, 'physical reads', value, 0)) / 
	    (sum(decode(name, 'db block gets', value, 0)) + 
	    sum(decode(name, 'consistent gets', value, 0))))) * 100 "Read Hit Ratio"
from v$sysstat;

Read Hit Ratio
--------------
     88.586128

The query below shows a listing of the contents of the v$librarycache performance view.

select namespace,gets,gethits,gethitratio,pins,pinhits,pinhitratio,reloads from v$librarycache;

NAMESPACE            GETS   GETHITS GETHITRATIO      PINS   PINHITS PINHITRATIO   RELOADS
--------------- --------- --------- ----------- --------- --------- ----------- ---------
SQL AREA              743       667   .89771198      2129      1965   .92296853        10
TABLE/PROCEDURE       402       318   .79104478       543       402   .74033149         0
BODY                    2         0           0         2         0           0         0
TRIGGER                 0         0           1         0         0           1         0
INDEX                  28         0           0        28         0           0         0
CLUSTER               110       105   .95454545       147       142   .96598639         0
OBJECT                  0         0           1         0         0           1         0
PIPE                    0         0           1         0         0           1         0

The ratio of PIN retrievals to that of PIN reloads should be close to 100% as in the first query below which shows the ratio of hits in the library cache. The second query shows the percentage hits in row cache. When free memory is close to zero and either or both ratios are less than 95% then increase shared pool size.

select to_char((sum(pins-reloads))/sum(pins)*100,990.00)||'%' "Non-reparses" from v$librarycache;
select to_char((sum(gets-getmisses-usage-fixed))/sum(gets)*100,990.00)||'%' "Rows in Buffer" from v$rowcache;

Utilise available memory for shared SQL areas by increasing the number of open cursors for each session by increasing the value of the OPEN_CURSORS parameter. Note that allocating too much memory to library cache could lead to paging and swapping if enough virtual memory is not available.

Library cache misses can be generally reduced by ensuring the use of shared SQL areas for SQL statements and PL/SQL blocks. This can be accomplished by making sure that SQL statements and PL/SQL blocks are not reparsed. SQL is matched in the library cache as a text string match. Always use bind variables since differing literal values will not match. Also qualify schema names if identical objects reside in different schemas. Make sure that individual users cannot change optimisation at the session level.

The CURSOR_SPACE_FOR_TIME parameter can be set to TRUE. This setting will ensure that shared SQL areas are only deallocated from when all cursors associated with a SQL statement or PL/SQL vlock are closed. This ensures a library cache hit. However, if the shared pool is not large enough other requests could result in errors due to Oracle being unable to parse new SQL statements or PL/SQL blocks since the shared pool has run out of space. In the same guise, cursors can be cached at session level by setting a value into the SESSION_CACHED_CURSORS parameter. However, the SESSION_CACHED_CURSORS uses an LRU list rather than freezing cursors into the library cache for the duration of a transaction.

select v$sysstat.name,sum(v$sesstat.value) "Hits" from v$sysstat,v$sesstat
where v$sysstat.name='session cursor cache hits'
and v$sysstat.statistic#=v$sesstat.statistic#
group by v$sysstat.name;

NAME                                                                  Hits
---------------------------------------------------------------- ---------
session cursor cache hits                                                0
Private SQL/PLSQL Areas

Lower amounts of reparsing by cache'ing and pinning objects and SQL statements into memory uses more memory but causes higher numbers of disk hits. Unneccessary parse calls can be found and eliminated by using the SQL Trace facility, the V$SQLAREA view and the vSTATNAME view as shown below.

select sql_text,parse_calls,executions from v$sqlarea order by parse_calls;

PARSE_CALLS EXECUTIONS
----------- ----------
         12         12
         12         12
         12         12
         12         12
         12         12
         10         29
         12         57
         19         19

select * from v$statname where name in ('parse count (hard)','parse count (total)','execute count');
Pinning Objects and Cache'ing Data in Memory

Tables, packages and PL/SQL objects can be pinned into memory thus preventing them from being flushed from memory. Pinning objects into memory implies that these objects are placed into an MRU (most recently used) queue rather than an LRU (least recently used) queue. Thus it is important to note that objects are never be pinned or cached into memory permanently but are more likely to be accessed from memory rather than re-read from disk. Note that an object must be read from disk atleast once, ie. the first time it is accessed.

Cache'ing Tables into Memory

Small, static tables which are in constant use are good candidates for cache'ing. Use the CREATE/ALTER TABLE tablename CACHE command. Note that the CACHE option ensures that ONLY full table-scan results of these cached tables are placed into the MRU list in memory.

Cache'ing Individual SQL Queries into Memory

The CACHE hint in a query can also be used to cache a table into memory on first use of the query.

SELECT /*+ CACHE(customer) */ * FROM customer;

Make sure that enough free memory is available before caheing tables into memory. Also ensure that only neccessary tables are cached, ie. small tables with static data such as lookup tables. To find out how much memory is available at any given time query the X$BH table as show below (log in as SYS).

select decode (state,
	0, 'FREE',
	1, decode(lrba_seq,0,'AVAILABLE','IN USE'),
	3, 'IN USE', state
) "Block Status", count(*)
from x$bh
group by decode (state,
		0, 'FREE',
		1, decode(lrba_seq,0,'AVAILABLE','IN USE'),
		3, 'IN USE', state
	);

Block Status                              COUNT(*)
---------------------------------------- ---------
AVAILABLE                                      196
IN USE                                           4

Execute the above query repetitively if neccessary. If no free buffers are available within a short period of time increase the DB_BLOCK_BUFFERS parameter value. In my case I am running on Personal Oracle. I increased the DB_BLOCK_BUFFERS from 200 to 550 with a 128Mb RAM PC and got the result as shown below.

Block Status                              COUNT(*)
---------------------------------------- ---------
AVAILABLE                                      400
FREE                                           150
Pinning Objects and Packages into Memory

It may not be possible to maintain the shared pool (SHARED_POOL_SIZE) at a large enough value in order to most efficiently cater for retaining objects and packages in memory. Thus pinning of packages into memory is another option. Pin PL/SQL objects into memory using the DBMS_SHARED_POOL.KEEP procedure. In general, some or all packages, including Oracle built-in packages, can be pinned into memory. Pinning packages and procedures into memory prevents reparsing of these objects when they are once again read into memory for execution for reparsing. The SQL command listed below will pin all packages into memory.

DECLARE
	owner VARCHAR2(32);
	name VARCHAR2(32);
	CURSOR packages IS SELECT owner, name, FROM dba_objects WHERE object_type = 'PACKAGE';
BEGIN
	OPEN packages;
	LOOP
		FETCH packages INTO owner, name
		EXIT WHEN packages%NOTFOUND;
		DBMS_SHARED_POOL.KEEP (owner||'.'||name, 'P');
	END LOOP;
END;

The value of pinning of code objects into memory is that Oracle knows when two statements issued are identical. The matching of currently submitted SQL statements or blocks is tested against those already in memory using previosly calculated hash values. For heavily active OLTP systems pinning of coded objects into memory can make a big difference in performance. Even previously executed SQL statements will not require re-execution.

The DBMS_SHARED_POOL Package

Note that when pinning objects into memory it is best to do so immediately after instance startup. This will prevent fragmentation in memory with other objects already loaded. Since the pinned objects will be permanently in memory during the life of the instance they are best loaded into memory as a contiguos section.

DBMS_SHARED_POOL.[UN]KEEP (object, type)

type is P, C, R or Q for package, cursor, trigger or sequence.

The following code executed in SQL*Plus will show all objects in the shared pool which are larger than a specified size.

DBMS_SHARED_POOL.SIZES(size)
Multiple Buffer Pools

Cache behavior of different schema objects are used differently. KEEP small, frequently used objects and place seldom used large segments into the RECYCLE buffer pool. All other segments will be placed into the DEFAULT buffer cache. It is possible to create buffer pools specifically for each instance separately. DB_BLOCK_BUFFERS defines the number of buffers per instance. Each buffer pool is created from the DB_BLOCK_BUFFERS value, the remainder number of buffer pools defined in DB_BLOCK_BUFFERS comprises the DEFAULT buffer pool. DB_BLOCK_LRU_LATCHES is also allocated to the different buffer pools in the same way as DB_BLOCK_BUFFERS. The BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE parameters can be set for example as shown below. Note that the buffers parameter assigns a number of buffers to the KEEP or RECYCLE cache and the lru_latches parameter assigns the specified number of latches to the KEEP or RECYCLE cache.

BUFFER_POOL_KEEP = (buffers:100, lru_latches:2)
BUFFER_POOL_RECYCLE = (buffers:200, lru_latches:1)

Find the BUFFER_POOL_KEEP hit ratio using the query below.

select 1-(physical_reads/(block_gets+consistent_gets)) "Hit Ratio", physical_reads,block_gets,consistent_gets from v$buffer_pool_statistics where name='KEEP';

Beware of objects put into the KEEP buffer cache where these cached objects grow. In general only store static data into the KEEP buffer cache. However, static data is always relative to application functionality.

The V$SESSION_WAIT view can be used to analyse a statistical distribution of I/O's per segment in order to assess whether a particular segment should be placed into the KEEP or RECYCLE buffer pools.

LRU latches control the LRU (least recently used) buffer lists, ie. when a block or row is selected for use it is locked such that another process cannot select that same block or row at the same time. Thus processes can wait (spin) when accessing a row or block which already has obtained a latch. Latch contention is where one process is trying to lock something which another process already has. It appears that Oracle seems to be locking based on blocks. Since rows can span blocks then therefore there is no such thing as row locking in Oracle. What a suprise !!!

Use the queries below to detect latch contention.

select gets,misses,sleeps,spin_gets from v$latch;
select v$latch_children.child#,sleeps/gets "Miss Ratio" from v$latch_children where name='cache buffers lru chain';

Dictionary Cache

One cannot place the complete dictionary into memory since the dictionary is too large. However, every SQL statement will access the data dictionary multiple times when executing. Use the query below to show the data dictionary hit ratio. Increase the SHARED_POOL_SIZE if the hit ratio as shown below is less than 95%.

select (1-(sum(getmisses)/sum(gets)))*100 "Hit Ratio" from v$rowcache;

Hit Ratio
---------
81.499395

The Large Pool

The large pool contains the UGA when sessions are connected to the database using MTS. The large pool was added in Oracle8 as a result of MTS to allow a single process to manage several user connections. The two main purposes of the large pool are as follows. The large pool is sized using the LARGE_POOL_SIZE and the LARGE_POOL_MIN_ALLOC parameters. Note that sizing of the large pool allocates additional memory to the SGA and thus increasing the possibility of disk page swapping. With MTS Oracle allocates space in the SGA storing connections containing user, dispatcher and server processes plus users' private SQL area session level details.

When sessions are connected through dispatchers to shared server processes then memory is utilised in the shared pool (the large pool). When sessions are connected to dedicated server processes (non-MTS) then memory is included with each user process' memory allocation. Therefore MTS obviously reduces memory usage substantially for large numbers of concurrent connections.

Sums the current total amount of memory allocated to all sessions and the value of memory maximum usage sizes allocated to each session.

select sum(value) || ' Bytes' "Total sessions memory"
from v$sesstat,v$statname
where name in ('SESSION UGA MEMORY','SESSION UGA MEMORY MAX')
and v$sesstat.statistic# = v$statname.statistic#;

SORT_AREA_SIZE

This parameter allocates memory for sorting in general and for each user. If in-memory sorting cannot be performed for a particular SQL statement then disk temporary segments are allocated to the sort operation. This obviously increases I/O. Set the SORT_AREA_SIZE parameter to a point which limits generation of temporary disk segments. However, do not set SORT_AREA_SIZE so high that not enough memory is available to for other requirements. Try to get in-memory sorts with a memory to disk hit ratio of over 90%. If this is not achieved it may be neccessary to increase the SORT_AREA_SIZE value.

select (1 - (sum(decode(name, 'physical reads', value, 0)) / 
	    (sum(decode(name, 'db block gets', value, 0)) + 
	    sum(decode(name, 'consistent gets', value, 0))))) * 100 "Read Hit Ratio"
from v$sysstat;

Read Hit Ratio
--------------
     88.596717

Obviously the larger a portion of a sort which is executed in memory and not within a temporary sort segment on disk then the faster the sort will be. The SORT_AREA_RETAINED_SIZE parameter will retain memory for future sorting. Note that the SORT_AREA_RETAINED_SIZE will be retained for the duration of the connection (has implications with MTS ans connection pooling). However, if many large sorts are executed by many concurrent users then all these users will be retaining sort space in memory for the duration of connections. A large part of the time involved in the duration of a connection is idle. Thus large sorts and many concurrent users can use up too much memory for sorting. In fact in a system with large sorts executed by many concurrent users it may be best to force sorting to disk by setting the SORT_AREA_RETAINED_SIZE parameter to zero. Note that each connection will retain an area of memory equal to the SORT_AREA_RETAINED_SIZE parameter. Only ever set SORT_AREA_RETAINED_SIZE equal to SORT_AREA_SIZE for small transactional, high connection level OLTP databases with high exact match row hit rates and little sorting. In fact it may be best to always set SORT_AREA_RETAINED_SIZE to a small percentage of the SORT_AREA_SIZE value on any system other than a batch system.

Multi-Threaded Server or MTS Connection Pooling

In high concurrent usage systems database connections affect memory usage most distinctly. MTS can help to alleviate this by use of connection pooling and connection multiplexing, ie. connection sharing. Every connection to the database reserves a section of memory for use by the session utilising the connection. Obviously sharing of those connections can reduce the overall amount of memory required.

Redo Log Buffers

The log buffer is a memory area reserved for storage of log entries. Periodically the log buffer is flushed to the log files. Database activity can fill the log buffer faster than the log writer (LGWR) process can write the buffer to disk in the log files. If this happens the buffer will be written to disk first and all other operations will wait. Increasing the redo log buffer decreases the chance of new log entries contending with a part of the buffer being written to disk. However, increasing the log buffer by too much could use up too much memory, typically the log buffer is set to 32K or 64K but larger sizes are acceptable.

select name,value
from v$sysstat
where name in ('redo log space requests','redo entries')
order by name desc;

NAME                           VALUE
-------------------------- ---------
redo log space requests            0
redo entries                      45

The redo log space requests to redo entries ratio should be maintained at less than 1/ 5000. If this ratio is exceeded increase the redo log buffer size. It is tempting to make the redo log buffer cache the same size as the redo log files. Some very large databases are most efficient with very large redo log files, sometimes even as high as 250M for each redo log file. Thus it is tempting to set the redo log buffer cache to 250M. This would cause serious contention and impact performance greatly. This is because the redo log buffer cache is check and written in it's entirety under three separate circumstances. These are when the redo buffer cache is 1/3 full, whenever a commit is performed and at redo log checkpointing. Since the redo log buffer cache is checked from start to finish when being written to redo log files then a 250M redo log buffer will seriously impact database performance by having 1/3 of 250M written to the disk every time a commit is made. NEVER set the redo log buffer cache to anything over 256K. The redo log buffer cache is most efficient when being a mutliple of operating system block size, ie. 8K, 16K, 32K, 64K, 128K, 256K, etc... The highest redo log buffer cache I have ever set is 128K. Sometimes increasing log buffer cache upto 128K can significantly increase performance. Anything over 256K will have dire consequences for performance.

The Operating System

Operating system level paging and swapping can occur when large chunks of information are processed at once. Total hardware and virtual memory must be large enough to accomodate required memory space. A large Oracle SGA will minimise excessive paging and swapping as a result of Oracle operations. It is possible to load the entire SGA into memory on instance startup by the PRE_PAGE_SGA parameter to YES. It is important to note no matter how much the SGA is altered poor application implementation can still cause programs, for instance, continual logging in and off is very resource intensive, continual database reconnections. Execute the SHOW SGA command in sql* to see the sizes of the different parts of the SGA.

show sga

Total System Global Area   13929908 bytes
Fixed Size                    64948 bytes
Variable Size              11694080 bytes
Database Buffers            2097152 bytes
Redo Buffers                  73728 bytes