Memory Tuning: Make sure the SGA is tuned correctly

The System Global Area(SGA) is a shared memory region that contains data and control information for one Oracle instance. An SGA and the Oracle background processes constitute an Oracle instance. Oracle allocates the system global area when an instance starts and deallocates it when the instance shuts down. Each instance has its own system global area. Users currently connect to an Oracle instance share the data in the system global area. For optimal performance,the entire system global area should be as large as possible( while still fitting in real memory avoid to causes paging the the swap file) to store as much data in memory as possible and minimize disk I/O. The information stored within the system global area is divided into several types of memory structures:
the database buffers,
redo log buffer,
the shared pool. These areas have fixed sizes and are created during instance up.Database Buffer Cache Database buffers of the system global area store the most recently used blocks of database data; the set of database buffers in an instance is the database buffer cache. The buffer cache contains modified as well as unmodified blocks.Because the most recently used data is kept in memory,less disk I/O is necessary and performance is improved.Redo Log Buffer The redo log buffer of system global area stores redo entries -- a log of changes made to the database.The redo entries stored in the redo log buffer are written to the online redo log files by the LGWR process,which is used if database recovery is necessary.Shared pool Comprised of the Library Cache (also known as the Shared SQL Area) and the Row Cache (also known as the Dictionary Cache). Library Cache: Contains the text, parsed form, and execution plan for SQL and PL/SQL statements. A single shared SQL area is used by multiple applications that issue the same statement,leaving more shared memory for other uses. Row Cache: Contains Oracle data dictionary tables, including the system catalog and privileges of all users.

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 CacheOracle 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. Tuning is not a one time job but rather an iterative process which you do over and over, refining each time around. OSM help you to monitor the hit/miss ratio for library cache,dictionary cache and buffer cache periodically,and save this useful information to file for later use.