init.ora

Oracle Instance - Memory setup

This page covers the options for configuring available memory for an Oracle instance - it is not a tuning guide. Full database tuninig requires an analysis of application design, SQL tuning and transaction load analysis (typically with utlstat or statspack) - see the first chapter of Thomas Kytes book for more detail.

The advice on this page will provide a "first stab" at choosing init.ora settings for a database that will be an improvement on leaving everything at the default. There is often confusion about how to size the basic components of the Oracle System Global Area (SGA) in particular DB_BLOCK_BUFFERS and the SHARED _POOL so I describe what each item is for, and suggest some suitable starting values.

The current init.ora settings can be seen in server manager with the command SHOW PARAMETERS or in SQL*Plus with SELECT * FROM v$parameter Changes to the init.ora file are only read at instance startup - it is a good idea to change only ONE parameter at a time then bounce the database and monitor the effect.

System Global Areas - SGA
A summary of SGA settings can be seen at instance startup

 Total System Global Area 4942984 bytes
 Fixed Size                 38984 bytes
 Variable Size            4383808 bytes
 Database Buffers          512000 bytes
 Redo Buffers               65536 bytes

You can also view this summary at any time with the command SHOW SGA or SELECT * FROM v$sga;

Fixed Size
This is fixed for a given release on a given platform.

Variable Size
This size of this is dictated by various init.ora parameters, predominantly the SHARED_POOL.

Database Buffers
The DB buffer (data block buffer) cache in bytes is DB_BLOCK_SIZE * DB_BLOCK_BUFFERS.
The larger this cache is, the greater the chance that a user's request for a data block will already be in memory - reducing the need for physical reads.

You can to some extent determine the effectiveness of the DB buffer cache by measuring the buffer hit-ratio of the database. This ratio is dependent on the exact SQL statements being processed (it's even possible to deliberately write SQL that when run will produce a given hit ratio) General tuning wisdom indicates that 'typical SQL' on a properly tuned database should result in a buffer hit-ratio of between 94% and 97%

The temptation may be to throw all your memory at this cache, but datafiles are likely to be an order of magnitude larger than available memory so Oracle can only ever cache a small proportion of the database. By way of contrast the Data Dictionary is implicitly queried as part of every DML statement and is small enough to fit in the shared_pool. Therefore when allocating memory the buffer cache should take second place after correctly sizing the shared pool.

Don't assume from the above that the DB buffer cache must be smaller than the Shared_Pool - in fact it's usually larger, but it's important that the shared_pool is large enough - if the shared_pool is too small it will have a more detrimental impact on performance than a DB buffer cache that is too small.

For a small database (>10 Gb) you can probably set a DB buffer cache large enough to store everything in memory. If you have the RAM available then the DB buffer cache may be increased up to 100% of the physical datafiles (although there is little point in providing cache for 100% of a datafile unless it is 100% full of actual data.)

Shared_Pool_Size
The shared pool includes the Dictionary cache, SQL cache, Cursor cache plus latches & locks.

When I started learning Oracle I was surprised to learn that table definitions and other dictionary data is not simply 'available' in memory but stored in X$ Tables; just like any other table these are only read into memory when required. (exceptions are V$ views and associated X$ tables which are literally created in memory each time the instance is started; also see PRE_PAGE_SGA)

Just about every SQL statement utilises dictionary information such as table and column names.

Increasing the SHARED_POOL_SIZE means that more objects will be held in the cache. If the pool is too small the cache will become fragmented - if it's too large there may be negative side effects (searching through old objects)

Here is a nice script that will suggest a shared pool size - run it against a live instance and plug in the number of concurrent users.

To view the current shared pool size

 SELECT name, value
 FROM   v$parameter
 WHERE  name = 'shared_pool_size';

Compare this with free memory to see if it's too large

 SELECT name, bytes "Size"
 FROM   v$sgastat
 WHERE  name = 'free memory';

Redo Log Buffers
The LOG_BUFFER is a cache for the redo-log buffer - this will affect the time that user processes wait for a commit. For an OLTP application in which many users perform transactions, the LOG_BUFFER parameter needs to be increased beyond the default value.

If the 'redo log space requests' statistic in V$SYSSTAT is non-zero, you should increase LOG BUFFER.

SELECT name, value
FROM V$SYSSTAT where name ='redo log space requests';

Oversizing the redo-log-buffer will increase performance but deploying that extra memory elsewhere is likely to have a greater impact on overall performance.

A good starting point for the log_buffer is 64K, (after tuning the log_buffer often ends up somewhere between 65536 and 163840) there is generally no advantage in making the log_buffer larger than 1 Mb.

Shared_Pool_Reserved_Size
Sets a percentage of the shared pool to be reserved for SQL statements requiring large memory allocations - including packages.

Default in Oracle8 is 5% of shared pool (in Oracle 7 this was 0) I would start with the default but consider tuning this to ensure that sufficient memory is always available to load packages.

BITMAP_MERGE_AREA_SIZE
Merges bitmapped indexes. Typically, a large installation that has many bitmapped indexes will increase this parameter to 10 megabytes. Default: 1,048,576 bytes

JAVA_POOL_SIZE
The size in bytes of the Java pool. Default: 10MB If you aren't using Java reduce this to 65536

LARGE_POOL_SIZE
If you are using this - set it to around 10% of the shared_pool.

User and Program Global areas - UGA and PGA

Don't make the mistake of assigning all system memory to SGA structures like the shared pool - to prevent excessive paging to disk you should leave sufficient (probably a majority) of memory free for UGA/PGA and other server side programs. The exact figure will depend on the processes you have running on the server - but for a typical Oracle Forms application you should limit the memory allocated to the Oracle shared_pool + log_buffer to around 30 % of available physical memory.

The PGA is a region of memory that contains data and control information for a single connection. This memory must be available at connect time for a particular user, therefore the amount of free server memory is a limitation to the number of concurrent connections. Each users PGA may require anything from 50 Kb - 4 Mb of server memory, mostly dependent on application design.

PGA size is affected by: OPEN_LINKS, DB_FILES, LOG_FILES, HASH_AREA_SIZE.
SORT_AREA_SIZE allocates space to the PGA in a dedicated server connection or the UGA in a MTS environment.

To see the memory used by UGA/PGA processes try this query:

SELECT 
   se.SID, 
   value, 
   se.username,
   se.osuser,
   n.name
FROM
   v$session se,
   v$sesstat s, 
   v$statname n
WHERE 
   s.statistic# = n.statistic# and
   se.sid = s.sid and
   (n.name = 'session uga memory max' or n.name = 'session pga memory max' )
ORDER BY 
   n.name, value;

Setting up a small 'Test' Instance.
There are certain mimimum memory requirements for Oracle 8 to run at all - a good starting point is 20 Mb SGA plus 16 Mb UGA (or if using Java a minimum of 50Mb )

Related Packages
DBMS_SHARED_POOL
DBMS_LIBCACHE

Links
Why these figures still won't add up when running Oracle under Windows NT
Oracle Memory Architecture (Powerpoint)
Automated Oracle Tuning Initial Procedure - DBAZine.com
What are shared_pool and java_pool? - Oracle Technet

Simon Sheppard
SS64.com