Increased PGA and SGA in Oracle

Modifying the Oracle Database SGA and PGA size

Size of the SGA: general physical memory 20% is used as the operating system reservation, and the other 80% is for the database.
The SGA Normal database can be allocated between 40%-60% and the PGA can be allocated between 20%-40%.

1. Log in As System
and view the SGA information:
sql>show parameter SGA;
To view the PGA information:
sql>Show parameter PGA;

2, modify Sga_target
Sql>alter system set sga_target=436m;

3, modify Sga_max_size
sql> alter system set sga_max_size=436m Scope=spfile;
Www.2cto.com
4, restart the database for it to take effect:
Sql>shutdown immediate;
Note that the above two operations must be completed before restarting, and sga_target shall not be greater than sga_max_size, General Keep the two equal. this may cause the database to fail to start.
Sql>startup

5 to see if the SGA is in effect:
sql>show parameter SGA
NAME TYPE VALUE
------------------------ ----------------------------
Lock_sga Boolean false
Pre_page_sga Boolean false
Sga_max_size big integer 436M
Sga_target Big integer 436M

6, query PGA size
SQL> Show parameter Workarea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Workarea_size_policy string AUTO

SQL> Show parameter PGA
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Pga_aggregate_target Big Integer 90M

Pga_aggregate_target default Special small only 90M
Of course it is too small, because we are currently using a non-production environment, 90M does not matter, because we do not have any connection, in the end need to meet how many connections exactly how much?

General is the physical memory of the 80%*20% is 16%, general production inside are 16G space, 16g*16% = 2.56G close to 3G to the PGA

_pga_max_size parameter: Is the maximum amount of space each session can allocate, this value is not fixed

sql>Select Ksppinm "Name", ksppstvl/1024/1024 | | ' M ' "Value", Ksppdesc "Desc" from X$ksppi x, X$KSPPCV y where x.indx = y.indx and ksppinm = ' _pga_max_size ';
Results
Name Value Desc
------------- ------ --------------------------------------------------
_pga_max_size 200M Maximum size of the PGA memory for one process

From the results of this parameter is 200M (the default), that is, for each session to the maximum can only be divided into 200M, for the current session of the most in the PGA space can only be divided into 200M

7, modify the PGA Automatic management mode and size
Sql> alter system set Workarea_size_policy=auto Scope=both;
Sql> alter system set PGA_AGGREGATE_TARGET=3072M Scope=both;
Restart the database after modification.

The online Oracle database recommends memory modification methods:

1), Total system memory: 64G
2), the current database uses memory:
sga:2g
pga:90m
3), the recommended size after adjustment:
sga:30g
pga:10g

4), view the specific parameters:

Sql> Show parameter SGA; --------------View the area size of the SGA
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
LOCK_SGA Boolean FALSE
PRE_PAGE_SGA Boolean FALSE
Sga_max_size Big Integer 2G
Sga_target Big Integer 2G

Sql> Show parameter PGA; --------------View the PGA's default zone size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Pga_aggregate_target Big Integer 90M

sql> show parameter workarea;----------See how Oracle Memory areas work (make sure it's in automatic mode)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Workarea_size_policy string AUTO

5), modify the parameters:
SQL> Alter system set sga_max_size=30720m Scope=spfile;---------------Modify the maximum PGA size first

sql> shutdown immediate;-----------Restart to make the above changes effective
Sql> Startup
Sql> alter system set sga_target=30720m; ---------then modify the size of the SGA (the size cannot exceed the maximum PGA size set above.) otherwise error)

Sql> alter system set pga_aggregate_target=10240m Scope=both;

sql> shutdown immediate;-----------Restart to make the above changes effective
Sql> Startup

6) to see the effect after the change
Sql> Show parameter SGA;
Sql> Show parameter PGA;

Increased PGA and SGA in Oracle