Friday , October 20 2017
Home / Oracle DBA / How to change the SGA_TARGET and SGA_MAX_TARGET in Oracle

How to change the SGA_TARGET and SGA_MAX_TARGET in Oracle

 ****  ENABLE SGA_TARGET *****

MEMORY_TARGET : Dynamic
MEMORY_MAX_TARGET : NotDynamic
*Prior to 11g, Oracle manages PGA seperately by the pga_aggregate_target parameter.
*Oracle introduces AMM feature from 11g which will manage SGA as well as PGA.
*When we set MEMORY_TARGET, Oracle will dynamically assign memory to SGA and PGA

Note : MEMORY_TARGET= SGA_TARGET + PGA_AGGREGATE_TARGET.

*You can see in this demo my requirement is to enable 11g feature Memory_Target and give 920m size to SGA.
*I have created database with DBCA and selected SGA size by enabling the feature.”Use Automatic Memory Management”
*Oracle created this database with 2gb SGA size, this size oracle calculated 40% from existing RAM which is available in my server.
SGA_TARGET provides the following:

  1. Single parameter for total SGA size
  2. Automatically sizes SGA components
  3. Memory is transferred to where most needed
  4. Uses workload information
  5. Uses internal advisory predictions
  6. STATISTICS_LEVEL must be set to TYPICAL
[oracle@dba2 ~]$ export ORACLE_SID=chsr
[oracle@dba2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 19 15:48:11 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.


SQL>startup
ORACLE instance started.
Total System Global Area 376635392 bytes
Fixed Size 1336652 bytes
Variable Size 234883764 bytes
Database Buffers 134217728 bytes
Redo Buffers 6197248 bytes
Database mounted.
Database opened.
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 400M

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

 

SQL>alter system set sga_target=160m;
System altered.
SQL>show parameter sga_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 160

We can resize it to only 360 m if we will try to increase it from 400 m we will get error.
system sga_target=400m
ERROR at line:ORA-02097:parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size


SQL>show parameter spfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/chsr.ora

SQL>select sum(value) /1024/1024/1024 TOTAL SGA(GB)from v$sga;

TOTAL SGA(GB).155891418

SQL>show parameter memory 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 400M
memory_target                        big integer 400M
shared_memory_address                integer     0

SQL>show parameter sga; 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 400M
sga_target                           big integer 160M

If you are using a server parameter file (SPFILE),the database remembers the sizes of the automatically tuned SGA components across instance shutdowns.


SQL>lter system set sga_max_size=524m scope=spfile;
System altered.
SQL>alter system set memory_max_target= 920m scope=spfile;
System altered.

SQL>alter system set memory_max_target=920m scope=spfile;
System altered.

To effect this SGA size you need to bounce the database.

SQL>shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;
TOTAL SGA (GB)

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

.510566711
SQL>startup
ORACLE instance started.
Total System Global Area 548216832 bytes
Fixed Size 1337804 bytes
Variable Size 461375028 bytes
Database Buffers 79691776 bytes
Redo Buffers 5812224 bytes

Comments

comments

Check Also

Moving a database from Normal File System to ASM storage

Default storage for oracle database is File System, where database files resides on local storage. …

Leave a Reply

Your email address will not be published. Required fields are marked *