1) 原则：SGA+PGA+OS使用内存<总物理RAM
2) 通过sga+pga就能大概判断系统oracle使用了多少内存了
32位版本的oracle最大支持1.75GB的SGA
sga组件包括shared pool、 data buffer、 large pool
SGA=. db_block_buffers*db_block_size+ shared_pool_size+ log_buffer+Java_pool+size+large_pool_size
oracle推荐OLTP(on-line Transaction Processing)系统oracle占系统总内存的80%,然后再分配80%给SGA,20%给PGA。也就是
SGA=system_total_memory*80%*80%
PGA=system_total_memory*80%*20%
SGA占的物理内存不能超过75%，最多只能占70%，否则Oracle可能会出现各种的异常
3) 11g 中新增MEMORY_MAX_TARGET 参数是设定Oracle能占OS多大的内存空间
11g MEMORY_MAX_TARGET =SGA+PGA
4) 10g及其以后版本的SGA_MAX_SIZE 参数
表示Oracle SGA 区最大能占多大内存空间
5) 修改SGA大小（需要重启实例，为了以防万一，修改前先备份PFILE文件）
1. SQL> show parameter sga; //查看当前SGA，是1.7GB
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1696M
sga_target big integer 0
2. SQL> alter system set sga_max_size=2048M scope=spfile;//修改sga最大值到2GB
System altered.
3. 重启数据库报错
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 2147483648 cannot be set to more than MEMORY_TARGET 1778384896.
SQL> show parameter memory
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 1705 Serial number: 5
可以通过sga_max_size(11g新参数)调整oracle在启动时所使用的最小sga大小，但该参数不能超过memory_target（ORACLE使用内存大小）所指定的大小
如memory_target为1G,那么sga_max_size只能小于或等于1G
否则在启动时会出现以上错误提示：
SQL> show parameter memory_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 1696M
MEMORY_TARGET相当于sga_target + pga_aggregate_target的总和
4. 利用之前备份的PFILE启动数据库，修改memory_target和sga_max_size参数，再重启顺利
SQL> alter system set memory_target=3200M scope=spfile;
System altered.
SQL> alter system set sga_max_size=2048M scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 520094568 bytes
Database Buffers 1610612736 bytes
Redo Buffers 4964352 bytes
Database mounted.
Database opened.
//正常启动
5. 查询PGA大小，这里是102MB
SQL> select value/1024/1024||'M' M from v$pgastat where name like 'total PGA allocated';
M
-----------------------------------------
102.5947265625M
6. PGA_AGGREGATE_TARGET-此参数用来指定所有session总计可以使用最大PGA内存，默认是0不设置
如果要设置PGA最大值
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL> alter system set pga_aggregate_target=500M scope=spfile; //修改后重启数据库
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 520094568 bytes
Database Buffers 1610612736 bytes
Redo Buffers 4964352 bytes
Database mounted.
Database opened.
SQL> show parameter pga_aggregate_target; //生效
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 500M