Using SQLPLUS*

This article may require cleanup to meet OraFAQ's quality standards. Please improve this article if you can.

Introducing Automatic SGA Memory Management The Oracle Database 10g

• Single parameter for total SGA size
  
• Automatically sizes SGA components
  
• Memory is transferred to where most needed
  
• Uses workload information
  
• Uses internal advisory predictions
  

By using one parameter we don't need to use all other SGA parameters like.

DB_CACHE_SIZE
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE

Four most commonly configured components are automatically sized:

• Shared Pool
• Large Pool
• Java Pool
• Buffer Cache (DEFAULT buffer pool)
• STATISTICS_LEVEL must be set to TYPICAL
  

Enabling 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

Resizing 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=956 scope=spfile;
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 956M
 
SQL> alter system set sga_target=900m;
System altered.