概述：
后台进程启动时候，他们驻留的区域叫做PGA。用户进程启动时候，也要为他分配PGA。各个进程之间的PGA是互斥的（mutual exclutive）。各个进程之间，也是有共享内存的，这个内存就是SGA(systemglobal area)。数据库启动到nomount阶段就会启动后台进程，分配SGA，后台进程和SGA共同构成了数据库实例。
查看实例SGA分配状况：
SYS >show parametersga_target
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
sga_target |big integer|332M
SYS >showsga
Total System Global Area|346562560|bytes
Fixed Size | 2228264|bytes
Variable Size | 167776216|bytes
Database Buffers | 171966464|bytes
Redo Buffers | 4591616|bytes
上面的结果显示了SGA各个组件的内存分配，我的内存分配方式是自动分配(ASSM)。其中，固定区域(fixed size) 2228264 bytes，数据缓冲区内存(Database Buffers)171966464 bytes,重做日志缓冲区(Redo Buffers)4591616bytes,剩下的可变区域(Variable Size)由共享池，大型池，java池和留池组成。
下面sql可以单独查看可变区域大小
SYS >selectpool,sum(bytes)fromv$sgastat wherepool is not null groupby rollup(pool);
POOL |SUM(BYTES)
------------|----------
java pool | 4194304
large pool | 4194304
shared pool | 159383552
| 167772160
和上面的167776216有些出入是正常的
谈谈这几部分区域：
1. 固定区域
固定区域自然是实例运行时候，用户不能随便改动的区域，一般包括：
数据库和实例的一般信息，例如版本信息，SCN等
进程之间通信信息，例如锁
内部表x$ksmfsv记录了固定区域的相关信息,内部表x$ksmmem记录了整个sga地址映射关系，在64位机器上，每条记录代表8bytes
SYS >select* from v$version where rownum<=1;
BANNER
--------------------------------------------------------------------------------
OracleDatabase 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production
SYS >selectcount(*) fromx$ksmmem;
COUNT(*)
----------
43320320
1 rowselected.
Elapsed: 00:00:03.29
SYS >showsga
Total System Global Area|346562560|bytes
Fixed Size | 2228264|bytes
Variable Size | 167776216|bytes
Database Buffers | 171966464|bytes
Redo Buffers | 4591616|bytes
SYS >select346562560/8from dual;kim
346562560/8
-----------
43320320
1 rowselected.
现在通过内部表x$ksmfsv来查看系统scn。
SYS >select* from x$ksmfsv whereKSMFSNAM like '%kcsgscn_%';
ADDR | INDX| INST_ID|KSMFSNAM |KSMFSTYP |KSMFSADR | KSMFSSIZ
----------------|----------|----------|------------------------------|--------------------|----------------|----------
00000000096416C0| 3048| 1|kcsgscn_ |kcslf |0000000060019598| 48
我们看到，scn占用了48位
SYS >select ksmmmvalfromx$ksmmem where ADDR=
(selectKSMFSADRfrom x$ksmfsv whereKSMFSNAMlike '%kcsgscn_%');
KSMMMVAL
----------------
00000000007A2999
SYS >select to_number('7A2999','xxxxxxx')from dual;
TO_NUMBER('7A2999','XXXXXXX')
-----------------------------
8006041
下面两种办法也可以查看当前scn：
SYS >oradebugsetmypid
Statement processed.
SYS >oradebugdumpvarsga kcsgscn_
kcslf kcsgscn_ [060019598,0600195C8) = 007A29F1 0000000000000000 0000000000000B41
00000000 0000000000000000 0000000000000000 6001927800000000
SYS >select dbms_flashback.get_system_change_numberfrom dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
8006150
2. DATABUFFER
在自动共享内存管理下，我们看到，这部分区域占用内存最多。它用于存储最近使用的数据块,DATA BUFFER分为三部分，脏区，干净区和空闲区。
Oracle 9i以前db_block_size和db_block_buffers的乘积就是DATA BUFFER的大小
Oracle9i以后，oracle使用新参数db_cache_size确定数据库的DEFAULT缓冲池大小。db_cache_size表示一个粒度，是连续虚拟内存分配的单位，这个粒度受到参数SGA_MAX_SIZE的影响
在oracle 10gr2以后，如果SGA_MAX_SIZ<1g,那么粒度大小是4m，否则是16m。这个粒度受到隐藏参数_ksmg_granule_size的影响
SYS >altersystem set sga_max_size=350mscope=spfile;
SYS >startupforce;
SYS >start gethidpar
Enter value for par:_ksmg_granule_size
old 4:and x.ksppinm like'%&par%'
new 4:and x.ksppinm like'%_ksmg_granule_size%'
NAME |VALUE |DESCRIB
------------------------------|--------------------|----------------------------------------
_ksmg_granule_size |4194304 |granulesize inbytes
Elapsed: 00:00:00.07
SYS >show parameter cache_size
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
client_result_cache_size |big integer|0
db_16k_cache_size |big integer|16M
db_2k_cache_size |big integer|8M
db_32k_cache_size |big integer|0
db_4k_cache_size |big integer|0
db_8k_cache_size |big integer|0
db_cache_size |big integer|0
db_flash_cache_size |big integer|0
db_keep_cache_size |big integer|0
db_recycle_cache_size |big integer|0
修改sga_max_size
SYS >altersystem set sga_max_size=2g scope=spfile;
重启数据库
SYS >start gethidpar
Enter value for par:_ksmg_granule_size
old 4:and x.ksppinm like'%&par%'
new 4:and x.ksppinm like'%_ksmg_granule_size%'
NAME |VALUE |DESCRIB
------------------------------|--------------------|----------------------------------------
_ksmg_granule_size |16777216 |granulesize inbytes
SYS >show parameter cache_size
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
client_result_cache_size |big integer|0
db_16k_cache_size |big integer|16M
db_2k_cache_size |big integer|16M（因为最小是16M）
db_32k_cache_size |big integer|0
db_4k_cache_size |big integer|0
db_8k_cache_size |big integer|0
db_cache_size |big integer|0
db_flash_cache_size |big integer|0
db_keep_cache_size |big integer|0
db_recycle_cache_size |big integer|0
SYS >colCOMPONENTfor a40
SYS >/
COMPONENT |CURRENT_SIZE|GRANULE_SIZE| MIN_SIZE| MAX_SIZE
----------------------------------------|------------|------------|----------|----------
shared pool | 134217728| 4194304| 134217728| 134217728
large pool | 8388608| 4194304| 8388608| 16777216
java pool | 16777216| 4194304| 16777216| 16777216
streams pool | 0| 4194304| 0| 0
DEFAULT buffercache | 138412032| 4194304|138412032| 155189248
KEEP buffer cache | 8388608| 4194304| 0| 8388608
RECYCLE buffercache | 8388608| 4194304| 0| 8388608
DEFAULT 2K buffer cache | 8388608| 4194304| 8388608| 8388608
DEFAULT 4K buffer cache | 0| 4194304| 0| 0
DEFAULT 8K buffer cache | 0| 4194304| 0| 0
DEFAULT 16K buffer cache | 16777216| 4194304| 16777216| 16777216
DEFAULT 32K buffer cache | 0| 4194304| 0| 0
Shared IO Pool | 0| 4194304| 0| 0
ASM Buffer Cache | 0| 4194304| 0| 0
DATA BUFFER cache 分为default，keep,recycle三种类型，对于经常使用的表，建表时候可以指定缓存为keep，recycle适用于存放一次性读取的数据。Default则存储没有指定存储池的数据，按照LRU原理管理。默认所有表使用default池，可以使用全部的buffer cache。
SYS >select dbms_metadata.get_ddl('TABLE','TEST')FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','TEST')
--------------------------------------------------------------------------------
CREATE TABLE"SYS"."TEST"
( "X" NUMBER
) PCTFREE 10 PCTUSED 40INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULTCELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
PARALLEL 2
SYS >altersystem set db_keep_cache_size=4mscope=memory;
System altered.
Elapsed: 00:00:00.12
SYS >altersystem set db_recycle_cache_size=4mscope=memory;
System altered.
Elapsed: 00:00:00.03
SYS >show parameter cache_size;
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
client_result_cache_size |big integer|0
db_16k_cache_size |big integer|16M
db_2k_cache_size |big integer|8M
db_32k_cache_size |big integer|0
db_4k_cache_size |big integer|0
db_8k_cache_size |big integer|0
db_cache_size |big integer|0
db_flash_cache_size |big integer|0
db_keep_cache_size |big integer|8M
db_recycle_cache_size |big integer|8M
查看当前缓冲池中的设置：
SYS >selectNAME,BLOCK_SIZE,CURRENT_SIZE,TARGET_SIZEfromv$buffer_pool;
NAME |BLOCK_SIZE|CURRENT_SIZE|TARGET_SIZE
------------------------------|----------|------------|-----------
KEEP | 8192| 8| 8
RECYCLE | 8192| 8| 8
DEFAULT | 8192| 132| 132
DEFAULT | 2048| 8| 8
DEFAULT | 16384| 16| 16
还有一些参数db_nk_cache_size，通过这写参数，我们可以构建不同块大小的表空间，这样不同块大小的表空间中的数据才可以被读进buffer cache。
由于默认块大小是8k，我现在准备创建一个块大小是32k的表空间：
SYS >altersystem set db_32k_cache_size=16m;
SYS >createtablespace kkkkdatafile'/u01/oradata2/hx/32k.dbf' size 10m blocksize32k;
Tablespace created.
SYS >select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,MAX_EXTENTSfrom dba_tablespaces whereTABLESPACE_NAME='KKKK';
TABLESPACE_NAME |BLOCK_SIZE|INITIAL_EXTENT|MAX_EXTENTS
--------------------|----------|--------------|-----------
KKKK | 32768| 65536|2147483645
3. 共享池
共享池是一个很重要的区域，他可以存放执行过的sql相关信息，例如执行计划、hash_value、解析树等等。一条sql语句从pga进入共享池，就需要把他的hash与共享池中已经存在的hash对比，直接决定执行计划。如果已经执行过，就不用重新再进行sql语法解析，评估执行计划。
SYS >show parametershared_pool;
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
shared_pool_reserved_size |big integer|6710886
shared_pool_size |big integer|0
4. 日志缓冲区：
一条DML SQL执行之前，首先要记录重做信息，重做记录首先写入日志缓冲区，待commit或者其他因素把他写进日志文件，从而保护数据。日志缓冲区与databuffer不太一样，他没有那个所谓的粒度。
SYS >show parameter log_buffer;
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
log_buffer |integer |4259840
5. 其他部分
大型池：rman备份恢复，并行计算，共享服务器模式所用
Java池：jvm虚拟机运行所用
流池：来源为shard pool，为stream功能所用
各部分内存分配
SYS >select* from v$sga;
NAME | VALUE
--------------------|----------
Fixed Size | 2228464
Variable Size | 180358928
Database Buffers |180355072
RedoBuffers | 4497408
Variable Size部分包含了共享池，java池，大型池，但是sga_max_size去除db_cache_size部分也被分入可变部分，所以，VariableSize实际要大于v$sgastat计算出的三者之和：
SYS >selectpool,sum(bytes)fromv$sgastat wherepool is not null groupby rollup(pool);
POOL |SUM(BYTES)
------------|----------
java pool | 16777216
large pool | 8388608
shared pool | 134217728
| 159383552
SYS >select(180358928-159383552)/1024/1024from dual;
(180358928-159383552)/1024/1024
-------------------------------
20.0036774
SYS >show parametersga
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
lock_sga |boolean |FALSE
pre_page_sga |boolean |FALSE
sga_max_size |big integer|352M
sga_target |big integer|332M
计算vairable部分时候，还是要以v$sga结果为准。
同样，日志缓冲区redo buffer也要比log_buffer参数大一些，因为内存中需要设置保护也对log进行保护
SYS >show parameter log_buffer;
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
log_buffer |integer |4259840
SYS >showsga;
Total System Global Area|367439872|bytes
Fixed Size | 2228464|bytes
Variable Size | 184553232|bytes
Database Buffers | 176160768|bytes
Redo Buffers | 4497408|bytes
视图v$sgainfo同样记录了sga使用分配
SYS >select* from v$sgainfo;
NAME | BYTES|RES
--------------------------------|----------|---
Fixed SGA Size | 2228464|No
Redo Buffers | 4497408|No
Buffer Cache Size | 176160768|Yes
Shared Pool Size | 138412032|Yes
Large Pool Size | 8388608|Yes
Java Pool Size | 16777216|Yes
Streams Pool Size | 0|Yes
Shared IO PoolSize | 0|Yes
Granule Size | 4194304|No
Maximum SGA Size | 367439872|No
Startup overhead in SharedPool | 71161984|No
Free SGA Memory Available | 20971520|
操作系统共享内存分配：
oracle[~]$cat /proc/sys/kernel/shmmax–表示单个共享内存段最大大小，通常要调整，尽量让sga在一个内存共享内存段中。
536870912
oracle[~]$cat /proc/sys/kernel/shmmni
4096
oracle[~]$cat /proc/sys/kernel/shmall
oracle[~]$ipcs -sa
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 524288 oracle 640 8388608 26
0x00000000 557057 oracle 640 360710144 26
0x037cbed8 589826 oracle 640 2097152 26
------ Semaphore Arrays --------
key semid owner perms nsems
0xf0127c60 557058 oracle 640 154
------ Message Queues --------
key msqid owner perms used-bytes messages
为oracle用户分配了三块共享内存段：
oracle[~]$psaux |grep lgwr
oracle 3022 0.0 3.0 613032 23024 ? Ss 17:18 0:00 ora_lgwr_hexel
oracle 3316 0.0 0.1 105472 924 pts/2 S+ 19:47 0:00 grep lgwr
pmap查看共享内存的地址空间：
oracle[~]$pmap 3022
3022: ora_lgwr_hexel
0000000000400000 183412K r-x-- /u01/oracle/bin/oracle
000000000b91d000 1884K rwx-- /u01/oracle/bin/oracle
000000000baf4000 304K rwx-- [ anon ]
00007fb3faa47000 264K rwx-- /u01/oracle/lib/libnnz11.so
00007fb3faa89000 12K rwx-- [ anon ]
00007fb3faa98000 4K rwxs- /u01/oracle/dbs/hc_hexel.dat
00007fb3faa99000 8K rwx-- [ anon ]
00007fb3faa9b000 864K r-x-- /u01/oracle/lib/libskgxp11.so
00007fb3fab73000 1024K----- /u01/oracle/lib/libskgxp11.so
00007fb3fac73000 8K rwx-- /u01/oracle/lib/libskgxp11.so
00007fb3fac75000 392K r-x-- /u01/oracle/lib/libcell11.so
00007fb3facd7000 1024K----- /u01/oracle/lib/libcell11.so
00007fb3fadd7000 40K rwx-- /u01/oracle/lib/libcell11.so
00007fb3fade1000 8K rwx-- [ anon ]
00007fb3fade3000 4K r-x-- /u01/oracle/lib/libodmd11.so
00007fb3fade4000 1024K----- /u01/oracle/lib/libodmd11.so
00007fb3faee4000 4K rwx-- /u01/oracle/lib/libodmd11.so
00007fb3faee5000 4K rwx-- [ anon ]
00007fffd1578000 84K rwx-- [ stack]
00007fffd15ff000 4K r-x-- [ anon ]
ffffffffff600000 4K r-x-- [ anon ]
total 613032K
oracle[~]$more /proc/3022/maps
00400000-0b71d000 r-xp00000000 08:01952729 /u01/oracle/bin/oracle
0b91d000-0baf4000 rwxp0b31d00008:01952729 /u01/oracle/bin/oracle
0baf4000-0bb40000 rwxp00000000 00:000
0d8dc000-0d94a000 rwxp00000000 00:000 [heap]
60000000-60800000 rwxs00000000 00:04524288 /SYSV00000000 (deleted)
60800000-76000000 rwxs 00000000 00:04557057 /SYSV00000000 (deleted)
76000000-76200000 rwxs00000000 00:04589826 /SYSV037cbed8 (deleted)
361b600000-361b608000 r-xp00000000 08:01946167 /usr/lib64/libnuma.so.1
361b608000-361b807000---p 00008000 08:01 946167 /usr/lib64/libnuma.so.1
361b807000-361b808000 rwxp00007000 08:01946167 /usr/lib64/libnuma.so.1
Ipcrm命令强制释放共享内存：
SGA管理
Oracle 8i静态内存管理
修改参数需要重新启动数据库
db_cache_size
SYS >show parameter _pool_size;
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
global_context_pool_size |string |
java_pool_size |big integer|0
large_pool_size |big integer|0
olap_page_pool_size |big integer|0
shared_pool_size |big integer|0
streams_pool_size |big integer|0
参数均需要手动设置，一般是写在参数文件了
9i动态修改内存
引入sga_max_size参数，预先分配虚拟内存，可以在线修改参数
修改注意：
总和不能超过sga_max_size
修改值必须是粒度的整数倍，否则向上取整
SGA最低配置三个粒度，分别为固定区，data buffer和共享池
查看建议：
SYS >select tnamefrom tab where tnamelike '%ADVICE%';
TNAME
------------------------------
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_JAVA_POOL_ADVICE
DBA_HIST_MEMORY_TARGET_ADVICE
DBA_HIST_MTTR_TARGET_ADVICE
DBA_HIST_PGA_TARGET_ADVICE
DBA_HIST_SGA_TARGET_ADVICE
DBA_HIST_SHARED_POOL_ADVICE
DBA_HIST_STREAMS_POOL_ADVICE
GV_$DB_CACHE_ADVICE
GV_$JAVA_POOL_ADVICE
GV_$MEMORY_TARGET_ADVICE
GV_$MTTR_TARGET_ADVICE
GV_$PGATARGET_ADVICE_HISTOGRAM
GV_$PGA_TARGET_ADVICE
GV_$PX_BUFFER_ADVICE
GV_$SGA_TARGET_ADVICE
GV_$SHARED_POOL_ADVICE
TNAME
------------------------------
GV_$STREAMS_POOL_ADVICE
V_$DB_CACHE_ADVICE
V_$JAVA_POOL_ADVICE
V_$MEMORY_TARGET_ADVICE
V_$MTTR_TARGET_ADVICE
V_$PGA_TARGET_ADVICE
V_$PGA_TARGET_ADVICE_HISTOGRAM
V_$PX_BUFFER_ADVICE
V_$SGA_TARGET_ADVICE
V_$STREAMS_POOL_ADVICE
WRH$_DB_CACHE_ADVICE
WRH$_DB_CACHE_ADVICE_BL
WRH$_JAVA_POOL_ADVICE
WRH$_MEMORY_TARGET_ADVICE
WRH$_MTTR_TARGET_ADVICE
WRH$_PGA_TARGET_ADVICE
TNAME
------------------------------
WRH$_SGA_TARGET_ADVICE
WRH$_SHARED_POOL_ADVICE
WRH$_STREAMS_POOL_ADVICE
SYS >show parameter db_cache;
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
db_cache_advice |string |ON
db_cache_size |big integer|0
db_cache_advice参数为on，表示开启建议，为ready表示关闭了，但是分配了内存，off表示关闭
查看参数statistic_level的影响范围
SYS >select STATISTICS_NAME,SESSION_status,SYSTEM_Status,ACTIVATION_LEVEL,STATISTICS_VIEW_NAMEfrom v$statistics_level;
STATISTICS_NAME ,SESSION_,SYSTEM_S,ACTIVAT,STATISTICS_VIEW_NAME
------------------------------,--------,--------,-------,--------------------
Buffer CacheAdvice ,ENABLED ,ENABLED ,TYPICAL,V$DB_CACHE_ADVICE
MTTR Advice ,ENABLED,ENABLED ,TYPICAL,V$MTTR_TARGET_ADVICE
Timed Statistics ,ENABLED ,ENABLED,TYPICAL,
Timed OS Statistics ,DISABLED,DISABLED,ALL ,
Segment Level Statistics ,ENABLED,ENABLED ,TYPICAL,V$SEGSTAT
PGA Advice ,ENABLED,ENABLED ,TYPICAL,V$PGA_TARGET_ADVICE
Plan Execution Statistics ,DISABLED,DISABLED,ALL ,V$SQL_PLAN_STATISTIC
, , , ,S
Shared PoolAdvice ,ENABLED ,ENABLED ,TYPICAL,V$SHARED_POOL_ADVICE
Modification Monitoring ,ENABLED ,ENABLED,TYPICAL,
Longops Statistics ,ENABLED,ENABLED ,TYPICAL,V$SESSION_LONGOPS
Bind DataCapture ,ENABLED ,ENABLED,TYPICAL,V$SQL_BIND_CAPTURE
Ultrafast Latch Statistics ,ENABLED ,ENABLED,TYPICAL,
Threshold-basedAlerts ,ENABLED ,ENABLED,TYPICAL,
Global Cache Statistics ,ENABLED,ENABLED ,TYPICAL,
Active SessionHistory ,ENABLED ,ENABLED,TYPICAL,V$ACTIVE_SESSION_HIS
STATISTICS_NAME ,SESSION_,SYSTEM_S,ACTIVAT,STATISTICS_VIEW_NAME
------------------------------,--------,--------,-------,--------------------
, , , ,TORY
Undo Advisor,Alertsand Fast ,ENABLED ,ENABLED,TYPICAL,V$UNDOSTAT
Ramp up , , , ,
Streams PoolAdvice ,ENABLED ,ENABLED,TYPICAL,V$STREAMS_POOL_ADVIC
, , , ,E
Time Model Events ,ENABLED,ENABLED ,TYPICAL,V$SESS_TIME_MODEL
Plan Execution Sampling ,ENABLED ,ENABLED ,TYPICAL,V$ACTIVE_SESSION_HIS
, , , ,TORY
Automated Maintenance Tasks ,ENABLED,ENABLED ,TYPICAL,
SQL Monitoring ,ENABLED ,ENABLED ,TYPICAL,V$SQL_MONITORING
Adaptive Thresholds Enabled ,ENABLED ,ENABLED,TYPICAL,
V$IOSTAT_* statistics ,ENABLED,ENABLED ,TYPICAL,
繁忙的系统修改参数一定要注意，避免触发bug。
SYS >alter system set shared_pool_size=138412033;
System altered.
修改完了有等待状态，查看等待状态（11g已经不存在）：
SYS >select SID,EVENT,WAIT_TIMEfromv$session_wait wheresid=142;
SID,EVENT , WAIT_TIME
----------,----------------------------------------------------------------,----------
142,SQL*Net messagefromclient , 0
23 rows selected.
SYS >select* from v$lock where sid=142;
ADDR ,KADDR , SID,TY, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK
----------------,----------------,----------,--,----------,----------,----------,----------,----------,----------
0000000074CD88C0,0000000074CD8918, 142,AE, 100, 0, 4, 0,
145, 0
Oracle10g自动共享内存管理：
Auto shared memroy maganement
目标：同时满足OLTP时候使得data buffer增大i/O性能，olap系统批处理时候，内存转给large pool，使得并行查询获得更多内存，速度更快。
Sga_targer：手动设置这个参数为非0，设置参数statistic_level参数为typical或者all,那么oracle就可以启用自动内存管理。需要注意的是，设置了这个参数以后，sga_max_siza也会自动设置成和sga_target参数相同。如果两个参数都手动设置，那么sga_max_size不能小于sga_target，否则报错。Sga_target设置后，orale可以根据实际需要，动态为各组件分配内存。注意，SGA中的LOG BUFFER和streambuffer还是需要手动设置的。Block_size的cache值keep/default/recycle buffer cache参数也是需要手动设置的。设置了参数SGA_TARGET以后，sga相关参数都会是未设置状态，这时候，内存真正大小由一系列隐含参数决定，这些参数的值会自动写入spfile文件，下次启动时候生效
SYS >start gethidpar
Enter value for par:pool_size
old 4:and x.ksppinm like'%&par%'
new 4:and x.ksppinm like'%pool_size%'
NAME |VALUE |DESCRIB
------------------------------|--------------------|------------------------------------------
_NUMA_pool_size |Not specified |aggregate size in bytes of NUMApool
__shared_pool_size |142606336 |Actual size in bytes of shared pool
shared_pool_size |142606336 |sizein bytes of shared pool
__large_pool_size |8388608 |Actual size in bytes of largepool
large_pool_size |0 |size inbytes of large pool
__java_pool_size |16777216 |Actual size in bytes of java pool
java_pool_size |0 |size inbytes of javapool
__streams_pool_size |0 |Actual size in bytes ofstreams pool
streams_pool_size |0 |size inbytes of thestreamspool
SYS >selectpool,sum(bytes)fromv$sgastat groupby pool;
POOL |SUM(BYTES)
------------|----------
| 174498032
java pool | 16777216
shared pool | 146800640
large pool | 8388608
可以通过视图v$sga_dynamic_components可以查看各组件调整时间和调整类型等信息：通过这个视图，一目了然：
col LAST_OPER_TYPE for a7
col COMPONENT for a23
select COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE,USER_SPECIFIED_SIZE,
LAST_OPER_TYPE,GRANULE_SIZE,LAST_OPER_TIME
from v$sga_dynamic_components;;
COMPONENT |CURRENT_SIZE| MIN_SIZE| MAX_SIZE|USER_SPECIFIED_SIZE|LAST_OP|GRANULE_SIZE|
-----------------------|------------|----------|----------|-------------------|-------|------------|-
shared pool | 146800640|146800640| 146800640| 0|STATIC | 4194304|
large pool | 8388608| 8388608| 8388608| 0|STATIC | 4194304|
java pool | 16777216| 16777216| 16777216| 0|STATIC | 4194304|
streams pool | 0| 0| 0| 0|STATIC| 4194304|
DEFAULT buffercache | 125829120| 125829120|125829120| 0|INITIAL| 4194304|
| | | | |IZING | |
KEEP buffer cache | 0| 0| 0| 0|STATIC| 4194304|
RECYCLE buffercache | 0| 0| 0| 0|STATIC| 4194304|
DEFAULT 2K buffer cache| 8388608| 8388608| 8388608| 8388608|STATIC| 4194304|
DEFAULT 4K buffer cache| 0| 0| 0| 0|STATIC| 4194304|
DEFAULT 8K buffer cache| 0| 0| 0| 0|STATIC| 4194304|
DEFAULT 16K buffer cach| 16777216| 16777216| 16777216| 16777216|STATIC| 4194304|
e | | | | | | |
DEFAULT 32K buffer cach| 16777216| 16777216| 16777216| 16777216|STATIC| 4194304|
e | | | | | | |
Shared IO Pool | 0| 0| 0| 0|STATIC| 4194304|
ASM Buffer Cache | 0| 0| 0| 0|STATIC| 4194304|
那么，什么进程来负责自动分配内存呢：
他就是mman进程,他动态调整内存的依据来源于系统不间断收集的建议，这就是参数statistic_level设置成typical的理由。
PSP0 started with pid=3, OSid=1405
Thu Jun 13 20:33:462013
VKTM started with pid=4, OSid=1407
VKTM running at (100ms)precision
Thu Jun 13 20:33:462013
GEN0 started with pid=5, OSid=1411
Thu Jun 13 20:33:462013
DIAG started with pid=6, OSid=1413
Thu Jun 13 20:33:462013
DBRM started with pid=7, OSid=1415
Thu Jun 13 20:33:462013
DIA0 started with pid=8, OSid=1417
Thu Jun 13 20:33:472013
MMAN started with pid=9, OS id=1419
Thu Jun 13 20:33:472013
DBW0 started with pid=10, OSid=1421
Thu Jun 13 20:33:472013
LGWRstarted with pid=11, OSid=1423
Oracle11g的自动内存管理（AMM）
Oracle 11g的自动内存管理方式实际是结合了9i的pga自动管理和10g的ASSM.指定参数MEMORY_TARGET,oracle根据这个参数进行pga和sga的自动分配：
伴随着MEMORY_TARGET参数，oracle’引入了参数memory_max_target,MEMORY_TARGET不能超过memory_max_target。还需注意的是，memory_target参数还不能超过/dev/shm
在linux的vlm管理中，使用了shmfs/tmpfs选项，如果/dev/shm不够，就会报错，例如：
oracle[~/diag/rdbms/hexel/hexel/trace]$df-h
文件系统 容量 已用 可用 已用%% 挂载点
/dev/sda1 28G 16G 11G 61% /
tmpfs 368M 0 368M 0% /dev/shm
/dev/sdb1 20G 7.8G 11G 42% /u01/oradata2
SYS >altersystem set memory_target=400mscope=spfile;
System altered.
Starting ORACLE instance (normal)
WARNING: Youare trying to use the MEMORY_TARGET feature. Thisfeature requires the/dev/shm file system to be mounted for at least 419430400 bytes. /dev/shm is eithernot mounted or is mountedwith available spaceless than thissize. Please fix this sothat MEMORY_TARGET can work asexpected.Current available is385564672 and usedis 0bytes. Ensure thatthe mount point is /dev/shmfor this directory.
memory_target needs larger /dev/shm
[~]#umount /dev/shm
[~]#mount -t tmpfs shmfs -o size=500m/dev/shm
[~]#df -h
文件系统 容量 已用 可用 已用%% 挂载点
/dev/sda1 28G 16G 11G 61% /
/dev/sdb1 20G 7.8G 11G 42% /u01/oradata2
shmfs 500M 0 500M 0% /dev/shm
SYS >startupnomount;
SYS >show parametermemory;
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
SYS >show parameter pga;
NAME |TYPE |VALUE
------------------------------------|----------------------|------------------------------
pga_aggregate_target |big integer |0
SYS >show parametersga;
NAME |TYPE |VALUE
------------------------------------|----------------------|------------------------------
lock_sga |boolean |FALSE
pre_page_sga |boolean |FALSE
sga_max_size |big integer |400M
sga_target |big integer |0
分析内存分配：
SYS >start gethidpar.sql
Enter value for par:sga_target
old 4:and x.ksppinm like'%&par%'
new 4:and x.ksppinm like'%sga_target%'
NAME |VALUE |DESCRIB
------------------------------|--------------------|-------------------------------------------------
sga_target |0 |Targetsize of SGA
__sga_target |251658240 |Actual size of SGA
Elapsed: 00:00:00.04
SYS >start gethidpar.sql
Enter value for par:pga_agg
old 4:and x.ksppinm like'%&par%'
new 4:and x.ksppinm like'%pga_agg%'
NAME |VALUE |DESCRIB
------------------------------|--------------------|-------------------------------------------------
pga_aggregate_target |0 |Targetsizefor the aggregate PGA memory consumed by the ins
| |tance
__pga_aggregate_target |167772160 |Currenttargetsize for the aggregate PGA memory consumed
查看分配历史(全部自动分配的)
SYS >colLAST_OPER_TYPEfor a7
SYS >colCOMPONENTfor a23
SYS >select COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE,USER_SPECIFIED_SIZE,
LAST_OPER_TYPE,GRANULE_SIZE,LAST_OPER_TIME
from v$sga_dynamic_components;
COMPONENT |CURRENT_SIZE| MIN_SIZE| MAX_SIZE|USER_SPECIFIED_SIZE|LAST_OP|GRANULE_SIZE|LAST_OPER_TIME
-----------------------|------------|----------|----------|-------------------|-------|------------|-------------------
shared pool | 96468992| 79691776| 96468992| 0|GROW | 4194304|2013-07-0919:58:34
large pool | 4194304| 0| 4194304| 0|GROW | 4194304|2013-07-0919:58:34
java pool | 4194304| 4194304| 4194304| 0|STATIC | 4194304|
streams pool | 0| 0| 0| 0|STATIC| 4194304|
DEFAULT buffercache | 134217728| 134217728|134217728| 0|INITIAL| 4194304|2013-07-0919:58:34
| | | | |IZING | |
KEEP buffer cache | 0| 0| 0| 0|STATIC| 4194304|
查看内存页面映射，此时的granule为4m;
oracle[~]$ls /dev/shm/-l
总用量 245344
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:38 ora_hexel_32768_0
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:37 ora_hexel_32768_1
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:37 ora_hexel_32768_2
-rw-r----- 1 oracle oinstall 0 7月 9 23:35 ora_hexel_65537_0
-rw-r----- 1 oracle oinstall 0 7月 9 23:35 ora_hexel_65537_38
-rw-r----- 1 oracle oinstall 0 7月 9 23:35 ora_hexel_65537_39
-rw-r----- 1 oracle oinstall 0 7月 9 23:35 ora_hexel_65537_4
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:38 ora_hexel_65537_40
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:38 ora_hexel_65537_41
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:37 ora_hexel_65537_42
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:36 ora_hexel_65537_43
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:36 ora_hexel_65537_44
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:36 ora_hexel_65537_45
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:38 ora_hexel_65537_46
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:38 ora_hexel_65537_47
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:37 ora_hexel_65537_48
-rw-r----- 1 oracle oinstall 4194304 7月 9 23:37 ora_hexel_65537_49
-rw-r----- 1 oracle oinstall 0 7月 9 23:35 ora_hexel_65537_5
Oracle内存分配注意事项
前面已经讨论了pga和sga，已经很清楚oracle在oltp系统和dss系统中分配内存时候的注意事项。这里不再论述。需要注意的是，如果操作系统分配不当，发生了分页或者交换，也就是说使用过多的交换分区，将会带来严重的问题：
Oracle运行缓慢，从操作系统层面注意：
1. shmax需要设置成尽量容纳一个共享内存段。
2. sga不可设置过大，否则内存耗尽，将会使用交换分区，产生i/O超时，引发bug。
3. 小于1g的专有模式服务器，sga通常小于1/2*raw,不能照本搬初。
4. Swap设置合理：如果raw较小，小于4g，通常可以设置swap=2*raw。如果较大，可以设置swap<=raw（一般是一半大小）
SYS >show parameter disk_a; --sun操作系统上的一步i/o问题
NAME |TYPE |VALUE
------------------------------------|-----------|------------------------------
disk_asynch_io |boolean |TRUE
SYS >start gethidpar
Enter value for par: aio
old 4:and x.ksppinm like'%&par%'
new 4:and x.ksppinm like'%aio%'
NAME |VALUE |DESCRIB
------------------------------|--------------------|-------------------------------------------------
_aiowait_timeouts |100 |Number ofaiowait timeouts before error is reported
这个值导致把报错前较长等待时间，可以缩短
SYS >altersystem set "_aiowait_timeouts"=3scope=spfile;
当cpu耗用过高，那就要考虑时候是有问题的sql语句在作祟。当然，也可能是后台进程存在bug，或者其他原因导致。
534
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
