Oracle 通过不段的完善，目前SGA已经通过Oracle内部组件ASMM进行自动动态的管理内存的分配
如果人工修改这些参数，需要注意以下3点：
修改的内存大小必须是粒度大小的整数倍。否则Oracle 会自动向上取整。
sga的总大小不能超过sga_max_size.
sga最低配置为3个粒度，一个粒度用于固定SGA，一个粒度用于缓冲区高速缓冲，一个粒度用于共享池。
伴随SGA动态管理的新特性，Oracle推出一系列的内存设置建议功能，同时引入一系列的动态性能视图。
SQL> select tname from tab where tname like '%ADVICE%'; TNAME -------------------------------------------------------------------------------- WRH$_STREAMS_POOL_ADVICE WRH$_SHARED_POOL_ADVICE WRH$_SGA_TARGET_ADVICE WRH$_PGA_TARGET_ADVICE WRH$_MTTR_TARGET_ADVICE WRH$_MEMORY_TARGET_ADVICE WRH$_JAVA_POOL_ADVICE WRH$_DB_CACHE_ADVICE_BL WRH$_DB_CACHE_ADVICE V_$STREAMS_POOL_ADVICE V_$SHARED_POOL_ADVICE V_$SGA_TARGET_ADVICE V_$PX_BUFFER_ADVICE V_$PGA_TARGET_ADVICE_HISTOGRAM V_$PGA_TARGET_ADVICE V_$MTTR_TARGET_ADVICE V_$MEMORY_TARGET_ADVICE V_$JAVA_POOL_ADVICE V_$DB_CACHE_ADVICE SAM_SPARSITY_ADVICE GV_$STREAMS_POOL_ADVICE GV_$SHARED_POOL_ADVICE GV_$SGA_TARGET_ADVICE GV_$PX_BUFFER_ADVICE GV_$PGA_TARGET_ADVICE GV_$PGATARGET_ADVICE_HISTOGRAM GV_$MTTR_TARGET_ADVICE GV_$MEMORY_TARGET_ADVICE GV_$JAVA_POOL_ADVICE GV_$DB_CACHE_ADVICE DBA_HIST_STREAMS_POOL_ADVICE DBA_HIST_SHARED_POOL_ADVICE DBA_HIST_SGA_TARGET_ADVICE DBA_HIST_PGA_TARGET_ADVICE DBA_HIST_MTTR_TARGET_ADVICE DBA_HIST_MEMORY_TARGET_ADVICE DBA_HIST_JAVA_POOL_ADVICE DBA_HIST_DB_CACHE_ADVICE
这类建议也受初始化参数的控制：
V_$DB_CACHE_ADVICE SQL> show parameter db_cache NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_advice string OFF V_$SHARED_POOL_ADVICE SQL> show parameter statistics_level NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ statistics_level string TYPICAL
通过视图v$statistics_level可以查看该参数影响范围：
SQL> select statistics_name,session_status,system_status,activation_level,session_settable from v$statistics_level; STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES -------------------------------------- -------- -------- ------- --- Buffer Cache Advice DISABLED DISABLED TYPICAL NO MTTR Advice ENABLED ENABLED TYPICAL NO Timed Statistics ENABLED ENABLED TYPICAL YES Timed OS Statistics DISABLED DISABLED ALL YES Segment Level Statistics ENABLED ENABLED TYPICAL NO PGA Advice ENABLED ENABLED TYPICAL NO Plan Execution Statistics DISABLED DISABLED ALL YES Shared Pool Advice ENABLED ENABLED TYPICAL NO Modification Monitoring ENABLED ENABLED TYPICAL NO Longops Statistics ENABLED ENABLED TYPICAL NO Bind Data Capture ENABLED ENABLED TYPICAL NO Ultrafast Latch Statistics ENABLED ENABLED TYPICAL NO Threshold-based Alerts ENABLED ENABLED TYPICAL NO Global Cache Statistics ENABLED ENABLED TYPICAL NO Active Session History ENABLED ENABLED TYPICAL NO Undo Advisor, Alerts and Fast Ramp up ENABLED ENABLED TYPICAL NO Streams Pool Advice ENABLED ENABLED TYPICAL NO Time Model Events ENABLED ENABLED TYPICAL YES Plan Execution Sampling ENABLED ENABLED TYPICAL YES Automated Maintenance Tasks ENABLED ENABLED TYPICAL NO SQL Monitoring ENABLED ENABLED TYPICAL YES Adaptive Thresholds Enabled ENABLED ENABLED TYPICAL NO V$IOSTAT_* statistics ENABLED ENABLED TYPICAL NO
可以尝试STATISTICS_LEVEL三个等级basic、typical、all来比对，动态视图v$statistics_level的变化。另外Timed Statistics还受系统参数timed_statistics的控制
SQL> show parameter timed_statistics NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ timed_statistics boolean TRUE
当进行动态参数修改时，修改session会出于等待状态，等待事件为background parameter adjustment，查看shared_pool大小建议
SQL> select shared_pool_size_for_estimate spsfe,shared_pool_size_factor spsf,estd_lc_size els,estd_lc_memory_objects elo,estd_lc_time_saved elts,estd_lc_time_saved_factor eltsf,estd_lc_memory_object_hits from v$shared_pool_advice; SPSFE SPSF ELS ELO ELTS ELTSF ESTD_LC_MEMORY_OBJECT_HITS ---------- ---------- ---------- ---------- ---------- ---------- -------------------------- 256 .2857 41 2690 102 1 25331 384 .4286 43 2810 102 1 26467 512 .5714 43 2810 102 1 26467 640 .7143 43 2810 102 1 26467 768 .8571 43 2810 102 1 26467 896 1 43 2810 102 1 26467 1024 1.1429 43 2810 102 1 26467 1152 1.2857 43 2810 102 1 26467 1280 1.4286 43 2810 102 1 26467 1408 1.5714 43 2810 102 1 26467 1536 1.7143 43 2810 102 1 26467 1664 1.8571 43 2810 102 1 26467 1792 2 43 2810 102 1 26467
调整shared_pool_size大小，因为我这里不在业务环境下调整，速度非常快不存在等待事件，自然也没有锁的参数，我把书中的方法记录下来，以便后续查看：
SQL> alter system set shared_pool_size=896M scope=both; SQL> select sid,seq#,event,seconds_in_wait,state from v$session_wait where event='background parameter adjustment'; SQL> select * from v$lock where sid=(select sid from v$session_wait where event='background parameter adjustment');
以上的调整方式主要适用于10G以前的版本，10G后，Oracle引入sga_target参数对SGA内存分配进行自动管理，但sga_target大小不能超过sga_max_size的大小。
SQL> show parameter sga_max_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_max_size big integer 6464M NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_target big integer 0 SQL> alter system set sga_target=7000M scope=both; alter system set sga_target=7000M scope=both * 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 SQL> alter system set sga_target=3000M scope=both; System altered.
自动调整以下组成部分：
Buffer Cache
Shared Pool
Java Pool
Large Pool
自动管理需要statistics_level参数设置为typical或all，而以下几个组成部分需手动调整：
非标准BLOCK_SIZE(DB_nk_CACHE_SIZE)
Keep/Recycle Buffer Cache
Redo Log Buffer
Stream Pool
Oracle为自动管理内存引入了一个新后台进程MMAN，从告警日志可以看到该进程启动的顺序在DBWR和LGWR进程之前。
Fri Feb 20 14:57:45 2015 MMAN started with pid=9, OS id=6295 Fri Feb 20 14:57:45 2015 DBW0 started with pid=10, OS id=6297 Fri Feb 20 14:57:45 2015 LGWR started with pid=11, OS id=6299
也可以通过v$process视图查看这些进程在系统中的信息
SQL> select pid,spid,program from v$process; PID SPID PROGRAM ---------- ------------------------ ------------------------------------------------ 1 PSEUDO 2 6279 oracle@021Y-SH-BKAP (PMON) 3 6281 oracle@021Y-SH-BKAP (VKTM) 4 6285 oracle@021Y-SH-BKAP (GEN0) 5 6287 oracle@021Y-SH-BKAP (DIAG) 6 6289 oracle@021Y-SH-BKAP (DBRM) 7 6291 oracle@021Y-SH-BKAP (PSP0) 8 6293 oracle@021Y-SH-BKAP (DIA0) 9 6295 oracle@021Y-SH-BKAP (MMAN) 10 6297 oracle@021Y-SH-BKAP (DBW0) 11 6299 oracle@021Y-SH-BKAP (LGWR) 12 6301 oracle@021Y-SH-BKAP (CKPT) 13 6303 oracle@021Y-SH-BKAP (SMON) 14 6305 oracle@021Y-SH-BKAP (RECO) 15 6307 oracle@021Y-SH-BKAP (MMON) 16 6309 oracle@021Y-SH-BKAP (MMNL) 17 6311 oracle@021Y-SH-BKAP (D000) 18 6313 oracle@021Y-SH-BKAP (S000) 19 14053 oracle@021Y-SH-BKAP (W000) 20 6348 oracle@021Y-SH-BKAP (ARC0) 21 6351 oracle@021Y-SH-BKAP (ARC1) 22 6353 oracle@021Y-SH-BKAP (ARC2) 23 6355 oracle@021Y-SH-BKAP (ARC3) 24 6359 oracle@021Y-SH-BKAP (QMNC) 25 14902 oracle@021Y-SH-BKAP (J000) 26 6373 oracle@021Y-SH-BKAP (CJQ0) 27 14904 oracle@021Y-SH-BKAP (J001) 28 6915 oracle@021Y-SH-BKAP (SMCO) 29 6381 oracle@021Y-SH-BKAP (Q000) 30 6383 oracle@021Y-SH-BKAP (Q001) 31 7915 oracle@021Y-SH-BKAP (TNS V1-V3)
而真正决定各组成部分大小，则由新引入的隐藏参数来决定：
SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ from x$ksppi x,x$ksppcv y where x.inst_id=userenv('Instance') and y.inst_id=userenv('Instance') and x.indx=y.indx and x.ksppinm like '%pool_size%';
NAME VALUE DESCRIB
-------------------- -------------------- ----------------------------------------
_NUMA_pool_size Not specified aggregate size in bytes of NUMA pool
__shared_pool_size 3221225472 Actual size in bytes of shared pool
shared_pool_size 939524096 size in bytes of shared pool
__large_pool_size 67108864 Actual size in bytes of large pool
large_pool_size 0 size in bytes of large pool
__java_pool_size 67108864 Actual size in bytes of java pool
java_pool_size 0 size in bytes of java pool
__streams_pool_size 0 Actual size in bytes of streams pool
streams_pool_size 0 size in bytes of the streams pool
_io_shared_pool_size 4194304 Size of I/O buffer pool from SGA
_backup_io_pool_size 1048576 memory to reserve from the large pool
__shared_io_pool_siz 0 Actual size of shared IO pool
e
_shared_io_pool_size 0 Size of shared IO pool
global_context_pool_ Global Application Context Pool Size in
size Bytes
olap_page_pool_size 0 size of the olap page pool in bytes
_trace_pool_size trace pool size in bytes
