ORACLE Internal Architecture

ORACLE Internal Architecture

Program Global Area – PGA
The Program Global Area, or PGA, is a variable sized buffer of non-shared memory which is limited in growth only by the physical hardware. Basically, a separate PGA is allocated by the Oracle server when a user connects to the database and a session is created. Separate PGA’s are also allocated for each Oracle background process. The PGA is exclusive to that server process and is read and written only by Oracle code acting on behalf of that process.
The PGA consists of three components: a Stack Area, a Data Area and a Sort Area. The Stack Area is always present in each PGA and holds information such as a session’s variables and arrays. The Data Area, as its name suggests, is used to store data at a session level. The Sort Area is memory allocated to store data being sorted.

The initial size of the PGA is fixed depending on the operating system and the database initialization parameter settings. However the size of the PGA is variable once users start connecting. If sufficient PGA memory is not available when a user attempts to connect to an Oracle database their connection will be rejected with an Oracle error message. However, if a user connection is successful, they can never run out of PGA space. The overall size of the PGA can be affected by using Multi Threaded Server, as well as by Oracle initialization parameters such as open_links, db_files and sort_area_size. As the use of Multi Threaded Server (MTS) has the biggest impact on the sizing of the PGA, please refer to that section for more detailed information regarding PGA sizing including information on the initialization parameters as well as MTS is covered in the relevant sections of this paper.

System Global Area – SGA
Also referred to as the Shared Global Area, the System Global Area, or SGA is a dynamically allocated segment of memory which is allocated when a database starts up. Conversely, it is de allocated when a database instance is shut down. Unlike the PGA there is only one SGA per database instance and it is static in size.
The SGA consists of several memory structures each of which can independently have a major impact on overall system performance. It is because of this that a majority of tuning information covered in this paper concentrates on the components of the SGA. Some basic rules of thumb to consider when configuring the SGA for performance are:
· It is usually best to keep the entire SGA in real memory (or non-virtual memory).
· On platforms which support it, you should lock the SGA into real memory using the LOCK_SGA parameter.
· The size of the SGA should typically be sized so as to occupy between 30-60% of the total available memory.

To get a clearer understanding of the SGA and how the memory is divided, a diagrammatic representation is required. The following diagram illustrates the breakdown of SGA:
SHARED POOL = LIBRARY CACHE + DATA DICTIONARY CACHE
SGA = SHARED POOL + DATABASE BUFFER CACHE + JAVA POOL + REDO LOG BUFFER

USING MULTI THREADED SERVER
An Oracle database can be configured in either a dedicated server mode or in a Multi Threaded Server (or MTS) mode. Using Multi Threaded Server gives an Oracle database the ability to support tens of thousands of concurrent users. Oracle is able to accomplish this by moving user processes and memory components from the individual user processes into the SGA. This added overhead does require an increase in total SGA sizing but this is minimal compared to the overall memory reductions from the would-be dedicated user processes. Also, growth of the database memory is in a linear fashion and not exponential as would be the case in a dedicated server mode. In an MTS configuration the SGA is split into an additional segment of memory called the User Global Area (or UGA). Instead of having a dedicated process for each client request there is only one process per shared server. Also, the number of shared servers can be controlled and the number of processes is much less than with a dedicated server configuration. The process space previously used by each dedicated server process is no longer required as there are now fewer processes. As a result, there is substantial resource saving.
Parameters to setup:
· mts_dispatchers
· mts_servers
· mts_max_dispatchers
· mts_max_servers

SHARED POOL
The Shared Pool is the area in memory allocated to Oracle for holding shared memory constructs including the data dictionary cache, library cache and the execution plans for the corresponding SQL statements. Shared SQL areas are required to process every unique SQL statement submitted to a database, however a single shared SQL area can be "shared" by multiple sessions if the same identical statement is issued. The total size of the shared pool is specified using the shared_pool_size parameter in the parameter (or init.ora) file. It is specified in bytes and should not be set too low. If this parameter is set too low, you will not be able to take advantage of the memory allocated to the Database Buffer Cache, even if the database buffer cache has been set to a reasonably large size. Conversely, the size of the shared pool should not be set too high either, as too high a value can result in a waste of memory with possible adverse performance effects.
Querying v$sgastat will show available free memory. It will also show how much memory is being wasted. For example:

select name, value from v$parameter
where name = ’shared_pool_size’
NAME            VALUE ----------------------- -------------------------- shared_pool_size    90000000
select name, bytes "Size"
from v$sgastat
where name = ’free memory’
NAME            Size -------------------- --------- free memory        61779040
In this example the first statement displays how large the shared pool is: 90MB. The second statement then displays how much of the memory in the shared pool is free: 61MB. This indicates that the shared pool is being under utilized. Almost two-thirds is not being utilized. This memory could be allocated elsewhere. The shared_pool_size can be monitored through the data dictionary cache and the library cache. Both should be continuously monitored for an appropriate hit ratio.
NOTE: The size of the shared pool (or shared_pool_size) can be monitored through the data dictionary cache and the library cache. Both should be continuously monitored for an appropriate hit ratio.

DATA DICTIONARY CACHE
The Data Dictionary Cache is where reference information from the data dictionary for shared access is stored. This includes information about the database, it’s structures and it’s users. When a user processes a SQL statement, the data dictionary is referenced by Oracle several times. Reducing physical disk I/O is very important, hence the more information that is stored in memory, the less needs to be read from disk. The data dictionary cache is very important in this respect because this is where the data dictionary components are buffered. The only means of tuning the data dictionary is by increasing the shared pool (or shared_pool_size parameter). The data dictionary component can be monitored via V$ROWCACHE using the following select statement:

select sum(gets) "Gets",
sum(getmisses) "Get Misses",
(1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
from v$rowcache

Gets Get Misses Hit Ratio --------- ---------- --------- 509234    272        96.946586
This value should generally be over 90%, however whenever the database instance is first started, this value will be somewhere around 85%.

LIBRARY CACHE
The library cache consists of shared SQL and PL/SQL areas. When SQL is executed, the statement has to be parsed. The library cache reduces the overhead of this by maintaining parsed SQL and PL/SQL in the library cache. Whenever a statement is re-executed, there is no need to re-parse the statement if the statement exists in the Library Cache. In essence, this can reduce the work of the database and improve performance, especially in an OLTP environment where the same SQL statements are typically reissued. Also, the use of bind variables in the SQL statements can also help this. More information on Bind Variables is covered in a section on its own. Statistics reflecting library cache activity is stored in the dynamic performance view V$LIBRARYCACHE. These statistics reflect all library cache activity since the most recent instance startup. Like the Data Dictionary, the only means of tuning the library cache is by increasing the shared pool (or shared_pool_size parameter).

DATABASE BUFFER CACHE
Before explaining the purpose and operation of the database buffer cache it is important to clarify the concept of database blocks. All data stored in an Oracle database is stored on disk as blocks. A block is a fixed number of bytes in the range of 2KB to 32KB which is defined during database creation. The default and maximum size for an Oracle block is platform dependent. Almost all operations performed against data in an Oracle database is retrieved and written via multiples of blocks. Determining a suitable block size can have a major impact on database performance and overall disk and memory usage. Typically a data warehousing database should have a relatively large block size (around 16K) where as an OLTP intensive database will perform better with a smaller block size (around 8K).
The database buffer cache, or buffer cache, is a statically sized memory cache allocated during database instance startup. It is used as a temporary memory store for blocks of data as they are read from and written to disk. Each buffer contains a single Oracle block, which in turn could contain several rows of data. The buffer cache contains three main list structures: The Least Recently Used (or LRU) List, the Dirty Buffer (or LRUW) and the Hashed Chain List. The Dirty Buffer holds blocks that have been modified or updated and need to be written to disk. The LRU list holds free and pinned buffers which are either empty and therefore free for reuse, or blocks of data that are being accessed but not modified. The Hashed Chain List holds the same buffers as the other two lists but its buffers are arranged depending on their data block addresses. When a buffer and therefore a block are held in the Hashed Chain List it can only be in one of the two other lists at any given time.Both the Dirty Buffer and the LRU are LRU-List memory structures, meaning that blocks are always inserted on one end of the list (the most recently used end - MRU) and gradually moved to the LRU end as new blocks are inserted behind them. If a data block is never required again it will eventually be aged out of the list.

When a user reads information from a table, the Oracle database first queries the Hashed Chain List to see if the required rows are already loaded into memory, if so the Hashed Chain List will determine which buffer list the block has been loaded into. This enables Oracle to return these rows without ever requiring a disk I/O operation. Otherwise if the rows are not already present in the Hashed Chain List, the blocks and their associated rows will be retrieved from the relevant database object from disk and inserted into both the Hash Chained list as well as the LRU list within the buffer cache prior to any user seeing them. Once a block is loaded into the buffer cache it is available for use by all database users.

The size of the database buffer cache is controlled by the instance startup parameter: db_block_buffers and can only be modified via the parameter (or init.ora) file. To determine the total size of the database buffer cache simply multiply this number by the database block size –as each represents a single database block. For example:
db_block_size * db_block_buffers = data buffer cache size

In many cases simply increasing this parameter can improve system performance as use of the buffer cache can eliminate a large volume of disk i/o as many data operations can be satisfied by pure memory operations thus improving performance in orders of magnitude. However sizing the database buffer cache too large can have two drawbacks; firstly, the larger the buffer cache the larger the LRU is to be scanned to determine if a block is in memory or not and secondly, if the buffer cache is sized too large it could possibly induce unnecessary memory swapping. Of course the latter is more important. This must be considered when sizing the buffer cache and must not be disregarded. On many production databases it is not uncommon for the database buffer cache to occupy over two-thirds of the total SGA space.
NOTE: The Oracle initialization parameter - USE_INDIRECT_DATA_BUFFERS enables an extended buffer cache mechanism for 32-bit platforms that can support more than 4 GB of physical memory. (Not supported in Windows NT)

JAVA POOL
The Java Pool is a fixed piece of memory allocated to the Java Virtual Machine (or JVM). It is used to store the shared part of each Java class actually used per session. These are basically the read-only parts (vectors, methods, etc) and are typically about 4KB to 8KB per class. The size of the Java Pool is specified in bytes using the java_pool_size parameter in the parameter (or init.ora) file. The default size of the Java Pool is 20MB. In early releases of Oracle8i the size of the Java Pool was limited to a minimum of 20MB, even when it appeared to be smaller, it is important to be aware of this when working on a system with limited available memory running an early release of Oracle8i.

On a dedicated server, only using Java stored procedures, it is possible to size the Java Pool as low as 10m as none of the per session Java states are stored in the Java Pool, for dedicated servers, it is stored in the User Global Area within the PGA. However in an Multi-Threaded Server (or MTS) environment, which is required for CORBA and EJB’s, the Java Pool could be very large. CORBA and EJB’s require more memory, and a large Java-intensive application could quite possibly require up to 1 gigabyte of Java Pool memory.
On MTS servers, some of the User Global Area (or UGA) used for per session Java states are stored in the Java Pool. Also, because the size of the Java Pool is fixed, the total requirement for your application must be estimated and then multiplied by the number of concurrent sessions created. All UGA’s must be able to fit in the Java pool. As a general guideline, the Java Pool should be sized to 50MB or higher for large applications. While the default of 20MB is adequate for most typical Java stored procedure usage. The following query can determine how much Java pool memory is being used:
SELECT *
FROM V$SGASTAT
WHERE pool = ’java pool’;

REDO LOG BUFFER
The Redo Log Buffer is an area of allocated memory within the SGA for buffering redo information prior to being written to the redo log files. The redo buffers are a crucial component of the Oracle recoverability process and are accessed for almost every database process and transaction, including both DML and DDL. Even uncommitted transactions access the Redo Log Buffer.

The Redo Log Buffer helps to absorb processing spikes caused by the memory to memory transfer of data (SGA to Redo Buffer) verses the memory to disk transfer of data (Redo Buffer to Redo Log). As the buffer fills up, the output process (LGWR) is awakened to empty the buffer to the redo log files. The LGWR process requires some lead time, since it is possible that a large transaction could generate redo faster than the LGWR process can write to disk. To help alleviate this possible bottleneck, once the redo buffer becomes one third full, the LGWR process will be awakened into action. Otherwise the LGWR process will awaken every 3 seconds or during a checkpoint process.

The size of the buffer is specified in bytes using the log_buffer parameter in the parameter (or init.ora) file. In general, a larger redo buffer size reduces redo log file I/O, particularly if transactions are long or numerous. In a busy system, the value 65536 or higher is not unreasonable, however values above 1Mb are unlikely to yield any significant benefit. The default size of the redo log buffer is dependant on the hardware and operating system platform. The size of the redo log buffer can be viewed with the following query:

select * from v$sgastat where name = ’log_buffer’;
POOL        NAME        BYTES ----------- ------------------- ---------         log_buffer        31457280 shared pool log_buffer        491520
NOTE: In Oracle8 onwards the output of "v$sgastat" indicates the smaller "log_buffer" value is contained within the shared pool.
When an Oracle database instance is started up, the size of ’Redo Buffers’ shown can differ from the value of the log_buffer size specified in the parameter file. This is due to memory set aside for what is known as “guard” pages which help to protect the redo buffer.
NOTE: If you have small transactions each transaction COMMIT causes redo to be flushed to disk before the COMMIT returns control to the user.

Tips for Tuning Redo
· Locate Redo Log files on a separate disk to data if at all possible
· Use larger Redo Log files if necessary
· Increase the number of Redo Log Groups (& files)
· Use NOLOGGING where possible
· It is more effective to size Redo Logs larger and set LOG_CHECKPOINT_INTERVAL to a higher number (ie. 9999999 – 7x9).
· Init Parameters:
· LOG_BUFFER - (Increase - Evaluate)
· CHECKPOINT_INTERVAL - (Increase)
· DB_BLOCK_MAX_DIRTY_TARGET - (Increase)

LARGE POOL
First introduced in Oracle 8.0, the large pool is an optional area of the SGA similar to the shared pool, but with restrictions on its usage. Only certain types and sizes of memory can be allocated in this pool. The memory for the large pool does not come from the shared pool but instead directly out of the SGA thus adding to the amount of shared memory Oracle needs at startup.
The two main uses of the large pool are:
1. For the User Global Area (UGA) of sessions connected using multi-threaded server (MTS)
2. Buffering for sequential file IO (e.g. used by the recovery process when multiple IO slaves are configured)
The large pool is protected by the ’shared pool’ latch for memory allocation and management. However, unlike the shared pool, there is no LRU mechanism in the Large Pool. So chunks of memory are never aged out of the large pool – memory must be explicitly allocated and freed by each session. If there is no free memory left when a request is made for large pool then an ORA-4031 will be signaled.

The size of the large pool is specified by the large_pool_size parameter and the minimum size chunk of memory which can be allocated is determined by the large_pool_min_alloc parameter. By default the large pool is not allocated, and needs to be explicitly specified to exist. When specifying a value for the large pool it can be specified in either megabytes or kilobytes in order to accomplish this the large_pool_size parameter can be assigned a numerical value or a number followed by the suffix "K" (for Kilobytes) or "M" (for Megabytes).

If large_pool_size is left unset and the pool is required by either parallel query or backup I/O slaves, then Oracle will compute a value automatically. This occurs when parallel_automatic_tuning is TRUE. The computation will add 250k per session for the MTS server if mts_dispatchers is configured.
NOTE: the default computation can yield a size that is either too large to allocate or causes performance problems. In such a case, the large_pool_size should be explicitly set to a value sufficiently small enough for the database to start.

Large Pool using MTS
If there is a large pool configured, MTS will ONLY try to use this pool for a sessions UGA. When a new session is started a small amount of memory (known as the fixed UGA) is allocated in the shared pool and the rest of the session memory (UGA) is taken from the large pool. If there is insufficient space left in the large pool and ORA-4031 error will be returned. Memory is allocated from the large pool in chunks of at least large_pool_min_alloc bytes in size to help avoid memory fragmentation. This can impact the amount of memory used by each MTS session when compared to memory usage with no large pool configured. If no large pool is configured MTS will use the shared pool for the entire UGA as was the case in Oracle version 7x.

MULTIPLE DATABASE BUFFER POOLS
Multiple Database Buffer Pools are, as their name suggests, separately configured pools within the database buffer cache. With multiple buffer pools we are able to segregate memory operations based on the types of objects being accessed. For example one table could contain information that is rarely accessed so storing it’s blocks in memory over an extended period of time could be seen as a waste of resources. On the other hand another table could be frequently accessed and the I/O requirements to constantly reload its blocks into memory could be a time consuming process. Hence with Multiple buffer pools we are able to partition our buffer pool memory into separate areas that can be used to store different types of data objects to help overcome this issue. Oracle provides us with three separate types of buffer pools that can be configured. They are Keep, Recycle and Default.

Keep
· Intended for frequently accessed objects
· Lookup Tables and dimensions are good candidates for the Keep pool
· When sizing the Keep pool it should be sized so it can contain the sum of all objects intended to be stored, as objects can still be aged out of this pool if there is insufficient space

Recycle
· Intended for rarely accessed or large objects which could be considered a waste of space if cached
· Randomly accessed large tables are good candidates for the Recycle pool
· The Size of Recycle pool can be relatively small as the intention of this pool is for the buffers to be frequently overwritten

Default
· Contains the data blocks that are not explicitly assigned to any buffer pool
· Contains objects that are explicitly assigned to the DEFAULT pool
· There is no need to define the size and number of LRU latches for the Default buffer pool as it assumes what is not explicitly assigned to the Keep or Recycle pools

The are two new initialization parameters added for configuring Multiple Buffer Pools they are buffer_pool_keep and buffer_pool_recycle. When setting these parameters the number of buffers/blocks are specified along with the number of LRU latches. In addition the db_block_lru_latches parameter must be specified.

NOTE: Multiple Buffer Pools each contain their own Dirty Buffer Cache and Hash Chain Lists in conjunction with the addition LRUs specified during their creation.

When configuring these parameters it is also important to realize that the number of latches are subtracted from the total number allocated to the instance, just like the buffers. There must be a minimum of 50 buffers per LRU latch allocated to each buffer pool.

An example of how a parameter file may look follows:
DB_BLOCK_SIZE = 8192
DB_BLOCK_BUFFERS = 20000
BUFFER_POOL_KEEP = (buffers:8000, lru_latches:4)
BUFFER_POOL_RECYCLE = (buffers:1600, lru_latches:2)
DB_BLOCK_LRU_LATCHES = 12

In this scenario our block size is 8KB and our total memory allocation is 20,000 buffers each at 8KB giving us a total buffer cache of approximately 163MB. This buffer cache is then broken down into three separate pools: Keep Pool at approximately 65MB, Recycle Pool at approximately 12MB and the remainder of 86MB and 12 latches going to the default pool. Of course in order for a database object to take advantage of this it is necessary for the object to be either created or modified with the buffer parameters specified within the storage clause. The following example demonstrates this:

CREATE TABLE Foo_Keep
(nfoo number (1),
vfoo varchar2(5) )
STORAGE (buffer_pool KEEP)
/
Table created.

CREATE TABLE Foo_Default
(nfoo number (1),
vfoo varchar2(5) )
STORAGE (buffer_pool DEFAULT)
/
Table created.

ALTER TABLE Foo_Recycle STORAGE (buffer_pool RECYCLE);
Table altered.

SELECT table_name, buffer_pool
FROM user_tables
WHERE table_name LIKE ’FOO%’
/

TABLE_NAME        BUFFER_POOL ------------------ ----------- FOO_DEFAULT        DEFAULT FOO_KEEP        KEEP FOO_RECYCLE        RECYCLE
NOTE: When using Multiple Buffer Pools, it is important to remember that the placement of objects within a specific buffer pool is explicit. Remember that objects are not explicitly assigned a buffer pool will end up in the Default Pool. This means that the adoption of a multiple buffer pool strategy is an all or nothing approach. The benefits of using multiple pools are enormous but can be easily outweighed if configured incorrectly. Finally, the Oracle8i Tuning Guide has an excellent section that cover the steps taken to determine whether or not multiple buffer pools will be of benefit to your application.

CACHING ORACLE OBJECTS
In an effort to help a table or cluster remain in memory longer, Oracle provides the CACHE command as part of a table or cluster’s storage attributes. If a table or cluster has the Cache attribute set, the blocks of that object will be inserted into the MRU end of the LRU list in the Database Buffer Cache whenever they are accessed. This does not guarantee the blocks to remain permanently in memory, but instead that they will remain as long as feasible. Updating an extremely large table (perhaps larger then the buffer cache) can age out any previously cached blocks. An example of using the cache clause follows:
create table foo_x
( nfoo_x number(1),
vfoo_x varchar2(3) );
Table created.

create table foo_y
( nfoo_y number(1),
vfoo_y varchar2(3) )
CACHE ;
Table created.

select table_name, cache
from user_tables
where table_name like ’FOO%’
TABLE_NAME    CACHE ------------- ----- FOO_X        N FOO_Y        YThe default Cache attribute for a Table or Cluster is NOCACHE. Explicitly setting a table to be NOCACHE does have benefits as well – much as the recycle buffer pool is used to store rarely accessed tables, these too can be specified using NOCACHE.

NOTE: The CACHE and NOCACHE attribute is also a part of the LOB storage clause. Using NOCACHE for a LOB column is extremely beneficial. A LOB that is several gigabytes in size could easily cause the entire contents of the database buffer pool to be aged out completely.

Oracle also provides the following two initialization parameters to assist with caching of other objects

Cursors_Space_For_Time
· Default value is FALSE
· If set to TRUE, a shared SQL area cannot be aged out of the shared pool until all application cursors associated with its statement are closed. This can improve performance as each referenced cursor remains in memory. When set to TRUE this can possibly lead to more memory consumption

Session_Cached_Cursors
· Default value is 0 - can be set dynamically
· specifies the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and need not reopen the cursor.
· An LRU list is used to maintain entries in the session cursor cache.

USING DBMS_SHARED_POOL - PACKAGED PROCEDURE
In addition to being able to explicitly cache tables, clusters, LOBs and SQL cursors, Oracle also provides the ability to cache stored procedures as well. This functionality is provided with the dbms_shared_pool packaged procedure.
In order to use this package and the procedures it contains you must first run the dbmspool.sql script to install it as it is not installed with via the catproc.sql script. Once installed the following procedures are available. Once the package has been installed in order to see any results from the procedures the SET SERVEROUTPUT ON statement must be executed if running from a SQL*Plus session.
Sizes
1. This procedure is helpful to determine all of the objects currently loaded in the shared pool which exceed a specified size. The size is specified in kilobytes as a parameter when calling the procedure.
2. execute dbms_shared_pool.sizes(10) ;

Keep
1. This procedure is used to Keep or pin an object in the shared pool. Once an object has been kept in the shared pool, it is not subject to aging out of the pool. This may be useful for certain semi-frequently used large objects, because when large objects are brought into the shared pool, a larger number of other objects may need to be aged out in order to create a contiguous area large enough.
2. execute dbms_shared_pool.keep(’SCOTT.FOO_AUDIT’) ;

Unkeep
1. This procedure has the reverse effect of the dbms_shared_pool.keep procedure. It unkeeps an object from the shared pool.
2. execute dbms_shared_pool.unkeep(‘SCOTT.FOO_AUDIT’) ;

NOTE: The use of both the Keep and the Unkeep procedures may be unsupported in a future release of Oracle.

64 BIT OPERATING SYSTEM CONSIDERATIONS
64 Bit systems involve changing pointers and addresses from 32 Bit to 64 Bit quantities. Thus boosting addressibility in the form of memory- and filesize- addressibility. In using a 64 Bit system the size available for the SGA and datafiles can be increased from the limit imposed by 32Bit systems of 4 Gigabytes up to 32 Gigabytes. However the maximum size is dependant on the hardware manufacturer and may vary. Other restrictions lifted in 64 Bit implementations all relate to sizing; for exampl the database block size can be increased to 32 Kilobytes and export and log files can be larger than 2 Gigabytes.
However, a common fallacy is if identical databases were installed on a 64 Bit system, as well as a 32 Bit system, the 64 Bit system would perform better. This is not true. The performance of a 64 Bit system is the same if not slower then a 32 Bit system because every pointer and address structure will be increased in size. Many applications embed addresses and pointers in code and data, which means that when everything doubles in size the application itself will grow too, consuming more memory. However the obvious benefits of moving to a 64 Bit operating system should not be disregarded, just as there are different database block sizes suited to different types of applications, there are many cases where the use of a 64 Bit system would be beneficial. A good fit for a 64 Bit system would be a data warehouse where the data is typically larger then an online transaction processing system and would therefor require a larger memory for loading and caching. In a nutshell, consider moving to a 64 Bit system for sizing considerations and not as a means to magically improve processor performance.

NON ORACLE FACTORS AFFECTING MEMORY
Whether you are considering the configuration of a system in which an Oracle database will be installed or you are tuning an existing system in which Oracle is running, the following list of non Oracle components will need to be considered. These have been provided in the form of checklist as many of the components are specific to your hardware and operating system platform. These points are provided to help steer you in the right direction and to help remind you of any obvious factors you may have inadvertently overlooked, much like a pilots preflight checklist.
· How much memory is available - OS and Hardware dependent
· Don’t forget to allow for other processes that may be running on the machine
· Is the system an OLTP based system or a Datawarehouse or a Hybrid? These decisions will effect your decision on multiple buffer pools as well as caching.
· How many users will be connecting to this database? Is this a fixed number or is likely to grow in the future?
· O/S Buffer Cache -- Non Raw disks, how big is it? Will it grow with your requirements?
· Oracle will not share memory assigned to it with other applications. Once memoryhas been assigned to an Oracle database it will be used by Oracle.
· Virtual Memory. Is there sufficient memory? - Beware of swapping?
· What tools are available for monitoring your systems memory consumption? For example: vmstat (UNIX) or Task Manager (Windows NT)
· Is it possible to make the OS Page file 100-300% of the physical memory?
· Is there sufficient free system memory to assign two-thirds of what is available to Oracle’s SGA?

LATCH CONTENTION
A latch is a low level serialization mechanism which is used to protect a region of code from concurrent execution. Latches are often used when a program needs to access shared data in the SGA but is performing an operation which needs to be done atomically, the latch provides the mechanism to pause the program until the required resource becomes available. Although the purpose of latches is essential the cost of a poorly tuned and configured system can make latches a performance bottleneck.

Sometimes it may be observed that a CPU is fully utilized and yet not much appears to be happening in terms of data processing. One of the main causes for this could be because of Latch contention in the Oracle Database.

The main cause for Latch Contention, and therefore poor performance, is because of poorly written SQL statements, frequent context switches etc. However Oracle provides several initialization parameters which can help reduce Latch contention to a large degree, although the best solution is still to resolve poorly written SQL code where possible. Four areas which can help in addressing Latch contention and thus improve overall system performance are:
· Parsed SQL statements may be aged out of the SGA if insufficient memory is allocated to shared pool. In this case the shared_pool_size parameter should be
increased.
· Use of identical SQL statements, this helps can help in sharing of SQL statements which can both overcome shared pool usage as well as parsing efficiency.
· Using BIND variables wherever possible will permit sharing of SQL statements which can reduce shared pool usage while reducing parsing.
· The use of the Cursor_Sharing parameter introduced in Oracle8iR2.

CURSOR SHARING
· Introduced in Oracle8iR2 (or Oracle8.1.6)
· Configured using the Cursor_Sharing initialization parameter
· Can be set to: FORCE or EXACT
· EXACT is the default setting.
· When set to Force: similar statements are “forced” to share SQL by replacing literals with system generated bind variables improving cursor sharing with reduced memory usage, faster parses, and reduced latch contention.
· Two views added to support this:
· V$SQL_BIND_METADATA
· V$SQL_BIND_DATA

NOTE: This parameter is not provided as a replacement for a poorly written application instead it is provided for overcoming situations where an applications source code is inaccessible to the customer such as in a legacy envuronment.

Some drawbacks to using the Cursor_Sharing parameter:
· As the application still issues a parse, there will be a soft parse in the kernel, this does have library cache latch overhead when compared to a plain (re-)execute. Hence, an application will suffer in scalability.
· The parameter effects SQL statements only; this excludes PL/SQL.
· The parameter may have unwanted effects on data warehousing applications. Using bind-variables implies the optimizer has less information to do its work, and it can e.g. not use histograms as the actual query value is not available.

APPENDIX A - INITIALIZATION PARAMETERS AFFECTING MEMORY USAGE:

DB_BLOCK_BUFFERS
Refer to the section on the Database Buffer Cache in this document for more information on this parameter.
Specified as an integer value representing the number of buffers available in the database buffer cache. To determine the true size of the database buffer cache, multiply this parameter by the db_block_size. The total size of the database buffer cache should typically be sized to represent two-thirds of the SGA.
NOTE: The db_block_buffer parameter is the single most significant parameter affecting performance on an Oracle database.

SHARED_POOL_SIZE
Refer to the section on the Shared Pool in this document for more information on this parameter.
The Shared_Pool_Size is specified in bytes. The default size for this setting parameter is never adequate and can usually be multiplied by a factor of 10 on most platforms. However before haphazardly increasing the value for this parameter the steps outlined in the Shared Pool section of paper should be read und understood. This does not mean for us and the to all versions of Oracle and is measured in bytes. This is the memory that is allocated to Oracle for areas like the data dictionary, stored procedures, and statements. Comprising a large part of the SGA, the Shared_Pool_Size is comprised of the dictionary cache and library cache, and just like DB_Block_Buffers above, should not be set too low. If this parameter is set too low, you will not be able to take advantage of the memory that has been allocated by the DB_Block_Buffers, even if the DB_Block_Buffers has been set a reasonable value.

SORT_AREA_SIZE
Specified in bytes. Oracle allocates a Sort Area sized to the value of Sort_Area_Size to every user process for performing data sorts. The Sort Area is memory that is allocated outside of the SGA. In a dedicated server environment it is easy to monitor the effects of modifying this parameter by monitoring the size of the user processes via tools such as ps -ef. If the Sort_Area_Aize is set too small, a process will be forced to sort on disk in the user’s temporary tablespace.

SORT_AREA_RETAINED_SIZE
Specified in bytes. The Sort_Area_Retained_Size parameter is used in conjunction with the Sort_Area_Size parameter. It controls the size that the Sort_Area_Size will be reduced to once a user sort is complete. This parameter should be set less than or equal to the Sort_Area_Size parameter to be truly effective.

SHARED_POOL_RESERVE_SIZE
Specified in bytes. The Shared_Pool_Reserve_Size parameter controls the allocation of a portion of the shared pool for storing large objects. The default setting of this parameter is 5% of the shared_pool_size setting. Setting this to a relatively low value ensures that the majority of the area within the shared pool is not being used for large objects, but rather in a "separate area".

SHARED_MEMORY_ADDRESS
It is recommended you refer to the Oracle Installation Guide which is shipped with your Oracle software if you are considering using this parameter as it is very platform specific

HI_SHARED_MEMORY_ADDRESS
It is recommended you refer to the Oracle Installation Guide which is shipped with your Oracle software if you are considering using this parameter as it is very platform specific

LOG_BUFFER
Refer to the section on the Redo Log Buffer in this document for more information on this parameter.

JAVA_POOL_SIZE
Refer to the section on the java Pool in this document for more information on this parameter.

LARGE_POOL_SIZE
Refer to the section on the Large Pool in this document for more information on this parameter.

BUFFER_POOL_KEEP
Refer to the section on Multiple Buffer Pools in this document for more information on this parameter.

BUFFER_POOL_RECYCLE
Refer to the section on Multiple Buffer Pools in this document for more information on this parameter.

DB_BLOCK_LRU_LATCHES
Specified as an integer, DB_Block_LRU_Latches indicates the maximum number of LRU latch sets. The buffers of a buffer pool are equally divided among the working LRU latch sets of the buffer pool so that each buffer is protected by one LRU latch. Normally, the more latches you specify, the less contention exists for those latches. However, too many latches may result in small LRU lists, potentially reducing the cache life of a database block. Typically you should set this parameter to the number of CPUs or a multiple of that number. The default setting for this parameter is equal to CPU_COUNT/2 , which is usually adequate. Refer to the section on Multiple Buffer Pools in this document for more information on this parameter.

APPENDIX B - TUNING MEMORY ON NT
· Oracle8i supports Intel ESMA
· Over 4GB of RAM is accessible
· Only on Intel Pentium II and Pentium III Xeon 32-bit processors
· init.ora parameter: use_indirect_data_buffers
· NT registry entry: vlm_buffer_memory
· See:
http://developer.intel.com/procs/servers/isv/oracle/esma.htm

To take advantage of this support, you must do the following:
1. More than 4GB of RAM must be present in the server running Oracle8i.
2. Windows NT v4.0 Enterprise Edition, Service Pack 3 or later must be installed.
3. The Intel PSE36 driver must be installed and operational. See http://developer.intel.com/vtune/pse36/index.htm for further PSE36 system
requirements and for download instructions.
4. USE_INDIRECT_DATA_BUFFERS=TRUE must be present in the INIT.ORA for the database instance that will use the PSE36 driver. If this parameter is not set, then Oracle8i behaves in exactly the same way as previous releases.
5. Set DB_BLOCK_BUFFERS and DB_BLOCK_SIZE as desired for the database. Note that the total number of bytes of database buffers (that is, DB_BLOCK_BUFFERS multiplied by DB_BLOCK_SIZE) is no longer limited to 3GB as was the case in previous releases.
6. The VLM_BUFFER_MEMORY registry parameter must be created and set in the appropriate key for your Oracle home in the Windows NT Registry. This parameter is specified in bytes and has a default of 1GB. When set, this parameter tells Oracle8i how much non-PSE36 memory to use for database buffers. This memory comes from Oracle8i’s virtual address space, as was the case in previous releases. Setting this parameter to a large value has the effect of using more of Oracle8i’s address space for buffers and using less PSE36 memory for buffers. However, since accessing PSE36 buffers is somewhat slower than accessing virtual address space buffers, tune this parameter to be as large as possible without adversely limiting database operations.

Memory Tuning: Make sure the SGA is tuned correctly

Tuning the Shared Pool
For most applications,shared pool size is critical to Oracle performance(Shared pool size is less important only for applications that issue a very limited number of discrete SQL statements.) The shared pool holds both the data dictionary cache and the fully parsed or compiled representation of PL/SQL blocks and SQL statements. If the shared pool is too small,then the server must dedicate resources to managing the limited space available.This comsumes CPU resources and causes contention,because restrictions must be imposed on the parallel management of various caches. The more you use triggers and stored procedures,the larger the shared pool must be.It may even reach a size measured in hundreds of megabytes.
Monitor the statistics in the V$LIBRARYCACHE over a period of time with this query:
select sum(reloads)/sum(pins) "Lib Cache miss ratio" from v$librarycache;

Total misses should be near 0. If the ratio of misses to executions is more than1%, try to reduce the library cache misses through the means of:

Allocating additional memory for the library cache by increasing the value of the initialization parameter SHARED_POOL_SIZE.
To take advantage of additional memory available for share SQL areas,you may also need to increase the number of cursors permitted for a session.You can do this by increasing the value of the initialization parameter OPEN_CURSORS. Be careful not to intruduce paging and swapping by allocating too much memory for the library cache.The benefits of a library cache large enough to avoid cache misses can be partially offset by reading share SQL area into memory from disk whenever you need to access them. Writing identical SQL statements whenever possible
Monitor the statistics in the v$ROWCACHE over a period of time while your application is runing with this query:
select sum(getmisses)/sum(gets) "Row Cache miss ratio" from v$rowcache;

For frequently accessed dictionary caches,the ratio of total GETMISSES to total GETS should be less than 10% or 15%.If the ratio continues to increasing above this threshold while your application is running,you should consider increasing the amount of memory availabe to the data dictionary cache. To increase the memory available to the cache,increase the value of initializtion parameter SHARED_POOL_SIZE.
The amount of free memory in the shared pool is reported in V$SGASTAT.The instantaneous value can be reported using the query
select sum(bytes) bytes from v$sgastat where name='free memory';

If there is always free memory available within the shared pool,then increasing the size of the pool will have little or no beneficial effect. However,just because the shared pool is full does not necessarily mean that there is a problem. If the ratios discussed above are close to 0,there is no need to increase the pool size.

Tuning the Buffer Cache
Oracle collects statistics that reflect data access and stores them in the dynamic preformance table V$SYSSTAT. These statistics are useful for tuning the buffer cache:
db block get,consistent gets:
The sum of the values of these statistics is the total number of access to buffers in memory.
physical reads:
The value of this statistic is the total number of requests for data resulting in access to datafiles on disk.

Monitor these statisitcs as follows over a period of time while your application si running:
select name,value from v$sysstat where name in ('db block gets','consistent gets','physical reads');

Calculate the hit ratio for the buffer cache with this formula:
Hit Ratio = 1-(physical reads/(db block gets + consistent gets))

The buffer cache hit ratio should be 90% or higher.
Since the size of the buffer cache is determined by:
DB_BLOCK_SIZE * DB_BLOCK_BUFFERS, and DB_BLOCK_SIZE cannot be changed without recreating the database,you can increase DB_BLOCK_BUFFERS to improve Hit Ratio.
The relationship between cache hit ratio and number of buffers is far from a smooth distribution.When tuning the buffer pool,avoid the use of additional buffers that contribute little or nothing to the cache hit ratio.