Automatic managment SGA Components using SGA_TARGET

Subject:	Automatic managment SGA Components using SGA_TARGET
	Doc ID:	Note:256913.1	Type:	BULLETIN
	Last Revision Date:	13-MAR-2006	Status:	PUBLISHED

PURPOSE

-------

Automatic managment SGA Components using SGA_TARGET

SCOPE & APPLICATION

-------------------

All dba's.

Automatic managment SGA Components using SGA_TARGET

---------------------------------------------------

In Oracle 10G, you need not manully provide values for individual SGA 

components like:

    shared_pool_size

    java_pool_size

    large_pool_size

    db_cache_size

You do not have to estimate the size of above SGA components and set it

in init.ora. In fact there is no need to set all the above 

parameters defining SGA size.

All you have to do is to set a new parameter called SGA_TARGET. The 

parameter SGA_TARGET takes a value which indicate the maximum size

of SGA required for your instance. 

Consider that you set SGA_TARGET to say 152M. This indicates that 

maximum size to which SGA can grow is 152M. All the SGA components like

shared pool, buffer cache, large pool, java pool will be allocated from 

this 150M maximum SGA. Oracle will automatically calculate the initial 

size of these components and resizes it as per the requirement without 

any manual intervention.

You do not have to explicitly define values for shared pool, buffer 

cache, large pool and java pool if you set SGA_TARGET.

For example:

I have set SGA_TARGET=152M (block size is 8K).

SQL> show parameter sga_target

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_target                           big integer 152M

Oracle has automatically defined the size of SGA components as follows:

SQL> show sga

Total System Global Area  159383552 bytes

Fixed Size                   769328 bytes

Variable Size              72270544 bytes

Database Buffers           62914560 bytes

Redo Buffers               23429120 bytes

Key points to note:

1.  When you set SGA_TARGET, the value of SGA_MAX_SIZE is also set to 

    SGA_TARGET. That is,  SGA_TARGET = SGA_MAX_SIZE.

SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 152M

sga_target                           big integer 152M

2.  You cannot increase SGA_TARGET to a value greater than SGA_MAX_SIZE.

    That is, SGA_TARGET <= SGA_MAX_SIZE.

SQL> alter system set sga_target=160M;

alter system set sga_target=160M

*

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

RELATED DOCUMENTS

-----------------

Oracle? Database Concepts 10g Release 1 (10.1)

Part No. B10743-01

Chapter 8: Memory Architecture