Size Of SGA

Size Of SGA =
             (DB_BLOCK_SIZE × BD_BLOCK_BUFFERS) +
             LOG_BUFFER + SHARED_POLL_SIZE + JAVA_POLL_SIZE

To approximate size of the SGA (Shared Global Area), use following formula:

DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE +
SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS + 1MB

NOTE: ADD IN EACH DB_nk_CACHE_SIZE. THERE CAN BE UP TO 4
DB_nk_CACHE_SIZE (2, 4, 8, 16, 32k) DEFINED. ONE OF THE BLOCK SIZES IS
THE DEFAULT BLOCK SIZE AND ITS CACHE SIZE IS DEFINED BY DB_CACHE_SIZE.

Fixed portion: The size of the fixed portion is constant for a release and a plattform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters

Variable portion: The variable portion is called variable because its size (measured in bytes) can be changed. The variable portion consists of:

Large pool (optional) Provides working space for rman (although rman will also work without large pool).

Shared pool The shared pool is used for objects that are shared among all users. For example: table definitions, PL/SQL definitions, cursors and so on.The shared pool can further be subdivided into:

• Control structures


• Character sets


• Dictionary cache

The dictionary cache stores parts of the data dictionary because Oracle has to query the data dictionary very often as it is fundamental to the functioning of Oracle.

• Library cache

The library cache is further divided into

• Shared SQL Area,


• PL/SQL Procedures and


• Control Structures (Latches and Locks).

The size of the Shared Pool is essentially governed by the initialization parameter shared_pool_size (although shared_pool_size is usually smaller than the size of the shared pool, see here) and db_block_buffers (which plays a role for this size because the database buffer cache must be administered.)

v$db_object_cache displays objects (=tables, indexes, clusters, synonym definitions, PL/SQL procedures/packages and triggers) that are cached in the library cache.

The size for the variable portion is roughly equal to the result of the following statement:

select   sum(bytes)
from     v$sgastat
where    pool in ('shared pool', 'java pool', 'large pool');

Redo log buffer Redo Buffers is roughly equal to the parameter log_buffer.

Database buffer cache It's size is equal to db_block_size * db_block_buffers.

(Note: db_block_buffers is deprecated as of 9i, so if the init parameter db_cache_size) is set, the buffer cache's size will be set according to this value.

UGA If the instance is running in MTS mode, there'se also a UGA: user global area Showing information about the SGA You can use v$sga to show the amount of these sizes or alternatively use SHOW SGA in sql*plus.

The following initialization parameters affect the size of the SGA:

• db_block_buffers


• db_block_size


• db_cache_size


• db_keep_cache_size


• db_recycle_cache_size


• java_pool_size


• large_pool_size


• log_buffer


• shared_pool_size


• streams_pool_size

Limiting the size of the SGA for a user: The amount of SGA that a user can use can be limitted through profiles. Use the private_sga option in the create profile statement.

Dynamic SGA : Dynamic SGA allows to change the size of the buffer cache, the large pool, the shared pool and the process private memory on the fly, that is without shutting down the instance.





Granule size The granule size of the components can be found out with v$sga_dynamic_components.

Peeking into SGA The x$ tables are an sql interface to the SGA and allow to peek into the SGA.

Shared server vs dedicated server The request and response queues and other parts that are found in the pga as dedicated server are found in the sga when the server is a shared server.

Protecting data structures As the SGA is, after all, a shared area, and more than one process can perform read and write operations on the SGA, Oracle has to make sure that those operations don't get in their ways. This is ensured with locks and latches.

Locking SGA into memory On platform that support it, the init parameter lock_sga can be set to true, which will lock the entire SGA into physical memory.

(Source www.adp-gmbh.ch)

Automate the Sizing of SGA in Oracle 10g

SGA _TARGET provides the following:

• Single parameter for total SGA size


• Automatically sizes SGA components


• Memory is transferred to where most needed


• Uses workload information


• Uses internal advisory predictions


• STATISTICS_LEVEL must be set to TYPICAL

Enable SGA_TARGET

SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 600M

As we can see out automatic SGA tuning is not enabled so we can enable it by setting the SGA_TARGET parameter value.

SQL> alter system set sga_target=500m;
System altered.
SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
sga_target                           big integer 500M

Resize SGA_TARGET

• SGA_TARGET is dynamic


• Can be increased till SGA_MAX_SIZE


• Can be reduced till some component reaches minimum size


• Change in value of SGA_TARGET affects only automatically sized components

SQL> show parameter sga_max_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
sga_max_size                         big integer 600M

SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
sga_target                           big integer 500M

WE can resize it to only 600m if we will try to increase it from 600m we will get error.

SQL> alter system set sga_target=605m;
alter system set sga_target=605m *
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size

For that we must first increase our SGA_MAX_SIZE parameter value.But we must restart out instance because its STATIC parameter.

SQL> alter system set sga_max_size=500 scope=spfile;
System altered.
or
SQL> ALTER SYSTEM SET sga_max_size='524288000'scope=SPFILE  --sga_max_size set to 500MB
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size                  1337492 bytes
Variable Size             624953196 bytes
Database Buffers          369098752 bytes
Redo Buffers                4800512 bytes
Database mounted.
Database opened.
SQL> show parameter sga_max_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
sga_max_size                         big integer 500M

SQL> alter system set sga_target=450m;
System altered.

Disable SGA_TARGET

We can Disable our automatic SGA tuning by setting the parameter SGA_TARGET to value digit 0.

SQL> alter system set sga_target=0;

System altered.

To Take SGA Information Run following query.

SQL> select NAME,BYTES/(1024*1024) as "Size In MB",RESIZEABLE  from v$sgainfo