Rules-of-thumb for setting up an Oracle Instance

This is not a tuning guide - on a production database you will get better results by analysing actual resource usage with utlstat or statspack. This document is intended to provide you with 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 a suitable starting value.

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 in server manager

 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 cache in bytes is DB_BLOCK_SIZE * DB_BLOCK_BUFFERS. This is the size of the data block buffer cache. 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 determine the effectiveness of the data block buffer cache by measuring the buffer hit-ratio of the database.

The temptation may be to throw all your memory at this cache, but in reality the 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 normally small enough to fit in the shared_pool and this is implicitly queried as part of every DML statement. Therefore when allocating memory the buffer cache should take second place after sizing the shared pool.

Initially size the DB buffer cache to be 1/10 the size of the shared_pool (after tuning the DB buffer cache often ends up somewhere between 1/10 and 1/4 the size of the shared_pool ) General tuning wisdom indicates aiming for a buffer hit-ratio of between 94% and 97%

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_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)

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';

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: 1048576 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 ) this should cope with about 10 users.

Related commands

Oracle Memory Architecture (Powerpoint)
What are shared_pool and java_pool? (Technet)
Why these figures still won't add up when running Oracle under Windows NT