init.ora Oracle 8i

Oracle 8i - Instance Memory setup

This page covers the options for configuring available memory for an Oracle 8i instance - a full list of init.ora parameter settings for Oracle 9/10 can be found here.

This is not a tuning guide. Full database tuning requires an analysis of application design, SQL tuning and transaction load analysis (typically with utlstat or statspack) - see the first chapter of Thomas Kyte's book for more detail.

The advice on this page will provide a "first stab" at choosing initialisation file (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.

Although commonly referred to as an init.ora file - the full filename is initABC.ora where ABC is the ORACLE_SID
The default location of this file is ORACLE_HOME/dbs on unix or %ORACLE_HOME%\database on Windows.

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 (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.

The buffer hit-ratio gives some idea of the effectiveness of the DB buffer cache. General tuning wisdom indicates that 'typical SQL' on a properly tuned database should result in a buffer hit-ratio of between 94% and 97% However 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)

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.

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

In Oracle 9 DB_BLOCK_BUFFERS are set directly in Bytes, in earlier versions this is set in Blocks (so to calculate the size in bytes multiply by DB_BLOCK_SIZE.)

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)

Typical values are 80 Mb (Oracle 7) or 200 Mb (Oracle 9)
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.
Check the release notes for your Oracle version and platform to see the default size of this.
On some versions you can get away with 32K (if you arent planning to use java).
Version 9 requires a minimum of 150M (won't install with less)

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 to 4 Mb of server memory, the exact figures are 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 to install, but after installation you may be able to get away with drastically reducing the shared_pool and java_pool_size (try it and see).

Init.ora changes

It's worth noting that many parameters in the init.ora file can only be modified within a limited range, to be sure that a change you make has actually been applied to the instance - check it's value in v$parameter.
e.g.
select value from v$parameter where name ='db_file_multiblock_read_count';

Oracle 9 Spfile

From 9i onwards, the default configuration is to use an spfile rather than an init.ora
An spfile is not directly editable, so any illegal value that gets in there (which it doesn't trap for, and which could prevent the instance from starting) can only be removed by exporting the spfile to an init.ora ('create pfile from spfile'). Because the pfile is used in preference to init.ora you also need to delete the spfile (SHOW PARAMETERS SPFILE) then ('rm <location of spfile>')

Summary
With all the parameters described on this page there is no 'right' value or 'ideal ratio' - it depends very much how the application has been designed and how it is used by the end users - how big is a row of data? how often is it read or updated?
If there was a 'one size fits all' option, or even a simple algorithm that worked reliably then Oracle would automate the whole thing and remove these controls altogether.

Related Packages
DBMS_SHARED_POOL
DBMS_LIBCACHE

Links
Oracle Memory Architecture (Powerpoint)
Automated Oracle Tuning Initial Procedure - DBAZine.com
What are shared_pool and java_pool? - Oracle Technet

Windows 2000 tuning tips:
Q291988 - Windows 4 GB Tuning
Tuning the Windows Desktop Heap
Architecture, Scalability, and Tuning - Oracle 8i under Windows

Simon Sheppard
SS64.com