Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 21 October 2012

SGA Sizing in Oracle9i

SGA (System Global Area)
The SGA is a chunk of memory that is allocated by an Oracle Instance (during the nomount stage) and is shared among Oracle processes, hence the name. It contains all sorts of information about the instance and the database that is needed to operate.
Components of the SGA
The SGA consists of the following four (five if MTS) parts:
        Fixed Portion
        Variable Portion
        Shared pool
        Java pool
Fixed/Variable portion of SGA
The size of the fixed portion is constant for a release and a platform of Oracle, that is, it cannot be changed through any means where as the variable portion can be changed. The variable portion consists of:
Large pool (optional): This area is only used if shared server architecture, also called multi-threaded server (MTS), is used, or if parallel query is utilized. It also used for IO server process and oracle RMAN backup and recovery purpose (although rman will also work without large pool).
Note: If large pool is not enough then these operation load will go to the shared pool.
Shared pool: The shared pool is used for objects that are sharing among all users. For Example Table definition, PL/SQL definitions, Cursor and so on. The shared pool can further be divided into: Control structure, Character set, Dictionary cache, Library cache
Java pool: The java pool holds the java execution code in a similar manner of PL/SQL cache in shared pool . The java pool is used by many internal routines, such as import and export, and should be sized at approximately 60 megabytes if no other java will be utilized in the user applications. The size of variable portion can be determined through this query:
Select  sum(bytes) from   v$sgastat
where  pool in ('shared pool', 'java pool', 'large pool');
SELECT name, value,isdefault FROM v$parameter
WHERE name like '%size';
Oracle9i allows the size of the SGA (or at least some components of the SGA) to be dynamically changed. The database buffer cache is one component that can be dynamically changed in Oracle9i. As we know the dynamic SGA infrastructure allows the limits to be set at runtime as to how much physical memory will be used for the SGA. The instance starts under-configured and it will use as much memory as the operating system allows it to use.
To facilitate the dynamic sizing of the SGA, Oracle9i introduces the concept of a granule (a unit of contiguous virtual memory allocation). The granule size is determined by the estimated total SGA size: (Query V$sga)
        If SGA size is less than 128MB, the granule size is 4MB.
        If SGA size is greater than or equal to 128MB, the granule size is 16MB.
Buffer cache, shared pool, and large pool are allowed to grow and shrink in size in units of granules. The SGA variable portion can be dynamically changed by using the ALTER SYSTEM command as shown below. The following formula can be used to determine the approximate SGA size in Oracle9i:
SGA size =
+ 1MB
In the above formula, up to four DB_nK_CACHE_SIZE parameters can be defined (for 2KB, 4KB, 8KB, 16KB, and 32KB).
Redo log Buffer: Redo log buffer roughly equal to the parameter log buffer.
DB Buffer Cache: Its size is equal to db_block_size * db_block_buffer.
Note: db_block_buffer is depreciated as on oracle 9i, so if in init parameter db_cache_size is set, the buffer cache’s size will be set according to this value.
How to Increase SGA_MAX_SIZE:
You can increase the sga_max_size either changing in parameter file or directly altering from SQL prompt. Some of the Dynamic SGA parameter setting examples are listed below. The parameter setting is calculated based on the new default database and output is pasted from toad.
SQL> show sga;
  1. Creat pfile from spfile.
  2. Increase the “sga_max_size” parameter size into the pfile
  3. re-start the database with the modified pfile.
  4. Create spfile with the pfile again.
SQL>Select name, value from v$parameter where name='sga_max_size';
NAME                 VALUE
---------------      ------------------------
sga_max_size         135266304

SQL>Alter system set sga_max_size  = 200M scope = spfile;
NAME                 VALUE
---------------      ------------------------
sga_max_size         210836200
Increase DB_cache_size:
Deciding the size of block buffer is critical to overall system performance. A large block buffer will provide more memory space for caching data block in the SGA. In such case, most SQL statement will find data blocks in memory and less time consuming I/O will be performed by the database. If we allocate a very high block buffer the server may swap/page the SGA into a swap space on the disk. In such case data will no longer will be accessed from the memory, and CPU will have work harder to manage the virtual memory of the server, thus degrading the overall system performance. Hence understanding the amount of memory available for block buffer is key to tune the block buffer.
SQL>Select name, value from v$parameter where name='db_cache_size';
NAME                 VALUE
---------------      ------------------------
db_cache_size        25165824

SQL>alter system set db_cache_size=48M scope = spfile;
SQL>Select name, value from v$parameter where name='db_cache_size';
NAME                 VALUE
---------------      ------------------------
db_cache_size        50331648
Once you have allocated the available memory to block buffers, the next step is to identify the efficiency of block buffer usage. Oracle database maintain dynamic performance v$BUFFER_POOL_STATISTICS view with overall buffer usage statistics. This view counts every time a data block is accessed either from the block buffer or from the disks.
Before measuring the database buffer hit ratio, it is very important to check that the database is running in steady state with normal workload and no unusual activity has been taken place. For example when you run SQL statement just after the database startup, no data block have been cached in the block buffers. At this point, oracle reads the data block from the disk and will cached the block in memory. If you run the same SQL again, the most probably the data block will still present in the cache, and oracle will not have to perform disk I/O. If you run the same SQL multiple time you will get a higher buffer ratio.On the other hand if you run the distinct large query, the data block will not be in buffer cache and oracle will have to perform disk I/O, there by lowering the buffer hit ratio.
 A hit ratio 95% or greater is considered to be the good hit ratio for OLTP system. A lower hit ratio means oracle is performing more disk IO on the server. In such situation you can increase the size of database block buffer to increase the database performance. You may have to increase the physical memory on the server if the server start swapping after increasing the block buffer size.

SQL>SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
FROM v$sysstat P1, v$sysstat P2, v$sysstat P3
WHERE = 'db block gets' AND = 'consistent gets' AND = 'physical reads';
SQL>Select 100*(1 - (v3.value / (v1.value + v2.value))) "Cache Hit Ratio [%]"
from v$sysstat v1, v$sysstat v2, v$sysstat v3
where = 'db block gets' and = 'consistent gets' and = 'physical reads';
SQL>SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE = 'db block gets' AND = 'consistent gets' AND = 'physical reads';

A DBA can create multiple buffer pool in SGA to store data block efficiently. for example we can use DB_RECYCLE_CACHE_SIZE to cache data blocks that are rarely used in the application. Similarly we can use DB_KEEP_CACHE_SIZE to cache data blocks that are frequently used by the application. By storing data blocks in KEEP and RECYCLE pools you can store frequently used data blocks separately from the rarely used data blocks, and control which data blocks are flushed from the buffer cache. Using RECYCLE pool, we can also prevent a large table scan from flushing frequently used data blocks.
SQL>Select name, value from v$parameter where name='db_keep_cache_size';
NAME                 VALUE
---------------      ------------------------
db_keep_cache_size        0
SQL>alter system set db_keep_cache_size=8M scope = spfile;
SQL>Select name, value from v$parameter where name='db_recycle_cache_size';
NAME                 VALUE
---------------      ------------------------
db_recycle_cache_size        0
SQL>alter system set db_recycle_cache_size=8M scope = spfile;
When you use the above parameters, the total memory allocated to the block buffers is the sum of DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, and DB_CACHE_SIZE.
Increase log_buffer size:
SQL>Select name, value from v$parameter where name='log_buffer';
NAME                 VALUE
---------------      ------------------------
log_buffer           524288
SQL> Alter system set log_buffer = 65534 scope = spfile;
NAME                 VALUE
---------------      ------------------------
log_buffer           65534
Check the wait event (v$system_event) if you see lots of log_buffer_space_wait then increase the size o log_buffer. While increasing the log_buffer size consider this point in mind:
        The transactions rate
        The size of the transactions
        The amount of RAM on the system.
        The number of users
        The configuration (dedicated or shared)
        The block buffer size
Increase Shared pool size:
show parameter shared_pool_size;
SQL>select sum(bytes) from v$sgastat where pool='shared pool';
SQL>select pool,name,bytes from v$sgastat where name='free memory' and pool='shared pool';
POOL              NAME              BYTES      
-------------     ---------------   -------------
shared pool       free memory       46456020
SQL>select * from v$sgastat where NAME = 'free memory';
POOL              NAME              BYTES
shared pool       free memory       46456020
large pool        free memory       8388608
java pool         free memory       33554432
SQL> alter system set shared_pool_size=128m;
Off course, it must be less than sga_max_size.
SQL>Alter system set shared_pool_size = 'your size' SCOPE =both;
SQL>Alter system set java_pool_size = 'your size' SCOPE =both;
Some Other Parameter Setting:
ALTER SYSTEM SET DB_CREATE_FILE_DEST='C:\oracle\oradata’ scope=spfile;


  1. DreamHost is ultimately one of the best web-hosting company for any hosting plans you might need.