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
1011
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
