调整SGA大小
如何设置SGA
Oracle的一个重要发展方向就是自动
管理，当然，SGA与PGA也不例外。
从Oracle 9i开始，就出现了一个新的参数sga_max_size，可以保证在此数值之内的内存可以自由地修改与调配。
如指定了sga_max_size，就可以在这个范围内自由地设置Shared pool、Data buffer等的大小。
从Oracle 9i开始，就出现了一个新的参数sga_max_size，可以保证在此数值之内的内存可以自由地修改与调配。
如指定了sga_max_size，就可以在这个范围内自由地设置Shared pool、Data buffer等的大小。
从Oracle
10g开始，又出现了另外一个新的参数，sga_target，只要设置了这个参数.
所有的SGA的组件，如Shared pool、Data buffer、Large pool等，都不需要手工指定了，
Oracle会自动管理。这一特性被称为自动共享内存管理（Automatic Shared Memory Management，ASMM）
也就是说，Oracle会根据需要随时改变各个内存组件的大小，以达到最佳使用状态。
但这个参数的最大值会受sga_max_size值限制
所有的SGA的组件，如Shared pool、Data buffer、Large pool等，都不需要手工指定了，
Oracle会自动管理。这一特性被称为自动共享内存管理（Automatic Shared Memory Management，ASMM）
也就是说，Oracle会根据需要随时改变各个内存组件的大小，以达到最佳使用状态。
但这个参数的最大值会受sga_max_size值限制
关闭自动管理 只需将这个sga_target参数设置为0
SGA中内存被各个组件已粒组(GRANULE)为单位进行划分
粒组的计算方法因SGA总大小和平台而异:
granule_size在不同平台、不同版本中，Oracle的设置的缺省值都可能不同
Oracle9i的官方文档如下描述:
Granule size is determined by total SGA size.
On most platforms, the size of a granule is 4 MB if the total SGA size is less than 128 MB,
and it is 16 MB for larger SGAs. There may be some platform. dependency,
for example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 128 MB.
Oracle10gR2文档如下描述:
Granule size is determined by total SGA size.
On most platforms, the size of a granule is 4 MB if the total SGA size is less than 1 GB,
and granule size is 16MB for larger SGAs.
Some platform. dependencies arise.
For example, on 32-bit Windows, the granule size is 8 M for SGAs larger than 1 GB.
Granule size is determined by total SGA size.
On most platforms, the size of a granule is 4 MB if the total SGA size is less than 1 GB,
and granule size is 16MB for larger SGAs.
Some platform. dependencies arise.
For example, on 32-bit Windows, the granule size is 8 M for SGAs larger than 1 GB.
所以我们知道对于这种没固定的内容了解即可
那么如果粒度为16M 某个组件原来占32M,之后你改成了35M,实际则是48M大小
查询各个组件内存分配情况和粒度值 V$SGA_DYNAMIC_COMPONENTS
col COMPONENT for a30
col CURRENT_SIZE for a30
col GRANULE_SIZE for a30
SQL> select COMPONENT,CURRENT_SIZE/1048576||'M' CURRENT_SIZE,GRANULE_SIZE/1048576||'M' GRANULE_SIZE
from V$SGA_DYNAMIC_COMPONENTS;
那么如果粒度为16M 某个组件原来占32M,之后你改成了35M,实际则是48M大小
查询各个组件内存分配情况和粒度值 V$SGA_DYNAMIC_COMPONENTS
col COMPONENT for a30
col CURRENT_SIZE for a30
col GRANULE_SIZE for a30
SQL> select COMPONENT,CURRENT_SIZE/1048576||'M' CURRENT_SIZE,GRANULE_SIZE/1048576||'M' GRANULE_SIZE
from V$SGA_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE GRANULE_SIZE
------------------------------ ------------------------------ ------------------------------
shared pool 68M 4M
large pool 4M 4M
java pool 4M 4M
streams pool 0M 4M
DEFAULT buffer cache 192M 4M
KEEP buffer cache 0M 4M
RECYCLE buffer cache 0M 4M
DEFAULT 2K buffer cache 0M 4M
DEFAULT 4K buffer cache 0M 4M
DEFAULT 8K buffer cache 0M 4M
DEFAULT 16K buffer cache 0M 4M
DEFAULT 32K buffer cache 0M 4M
ASM Buffer Cache 0M 4M
------------------------------ ------------------------------ ------------------------------
shared pool 68M 4M
large pool 4M 4M
java pool 4M 4M
streams pool 0M 4M
DEFAULT buffer cache 192M 4M
KEEP buffer cache 0M 4M
RECYCLE buffer cache 0M 4M
DEFAULT 2K buffer cache 0M 4M
DEFAULT 4K buffer cache 0M 4M
DEFAULT 8K buffer cache 0M 4M
DEFAULT 16K buffer cache 0M 4M
DEFAULT 32K buffer cache 0M 4M
ASM Buffer Cache 0M 4M
13 rows selected.
SQL>
如果自动管理只给sga_target一个值即可,之后内部组件的分配大小oracle会自动调配
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 272M
sga_target big integer 272M
SQL> create pfile from spfile;
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 272M
sga_target big integer 272M
SQL> create pfile from spfile;
File created.
SQL>
SQL> alter system set sga_target=300M;
alter system set sga_target=300M
*
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=300M;
alter system set sga_target=300M
*
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>
反过来,如果sga_target值已经设置了272M,再将sga_max_size改成200M小于sga_target的值
SQL> alter system set sga_max_size=200M;
alter system set sga_max_size=200
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
静态参数不能直接修改内存
alter system set sga_max_size=200
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
静态参数不能直接修改内存
SQL> alter system set sga_max_size=200M scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter sga
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 272M
sga_target big integer 272M
SQL>
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 272M
sga_target big integer 272M
SQL>
没问题运行正常,因为oracle检测到sga_max_size设置的比当前sga_target还小 就自动调整sga_max_size至sga_target相等
再设置sga_max_size值超越sga_target
SQL> alter system set sga_max_size=300M scope=spfile;
System altered.
Elapsed: 00:00:00.02
SQL> startup force
ORACLE instance started.
SQL> startup force
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1267260 bytes
Variable Size 121637316 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> show parameter sga
Fixed Size 1267260 bytes
Variable Size 121637316 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 272M
SQL>
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 272M
SQL>
如此设置才能被接受 此时也可以动态调整sga_target<=sga_max_size了
SQL> alter system set sga_target=290M;
SQL> alter system set sga_target=290M;
System altered.
Elapsed: 00:00:00.04
SQL> show parameter sga
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 292M
SQL>
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 292M
SQL>
总结:
sga_max_size 是静态的 设置必须重启库
sga_target 是动态的 直接可以修改内存
设置大于0的确定值既是开启ASMM 设置0就是关闭ASMM
当sga_max_size设置小于SGA_TARget时,则sga_max_size=SGA_TARget
当sga_max_size设置大于sga_target时,则正常生效
当sga_target设置大于sga_max_size时 报错
ORA-00823: Specified value of sga_target greater than sga_max_size
当sga_target设置小于sga_max_size时 正常 并可动态修改 这也是oracle给我们的灵活的之处
10G中有了sga_target弥补了9I中设置sga_max_size需要重启的缺陷
10G生产中 我们就可以给sga_max_size一个大值,动态的调整sga_target来寻求合适的大小.
SGA和物理内存的比照值
这只是初期设置参考 我们要根据业务运行一段时间后来确定最终值
这只是初期设置参考 我们要根据业务运行一段时间后来确定最终值
查看SGA中各个组件的大小 show sga查到的值并不十分精确 要查v$sgainfo
如果自动管理 查 v$sga_dynamic_components
SQL> select * from v$sgainfo;
NAME BYTES RES
-------------------------------------------------- ---------- ---
Fixed SGA Size 1218580 No
Redo Buffers 2973696 No
Buffer Cache Size 130023424 Yes
Shared Pool Size 62914560 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 209715200 No
Startup overhead in Shared Pool 37748736 No
Free SGA Memory Available 4194304
-------------------------------------------------- ---------- ---
Fixed SGA Size 1218580 No
Redo Buffers 2973696 No
Buffer Cache Size 130023424 Yes
Shared Pool Size 62914560 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 209715200 No
Startup overhead in Shared Pool 37748736 No
Free SGA Memory Available 4194304
11 rows selected.
Elapsed: 00:00:00.01
SQL>
SQL> select COMPONENT,CURRENT_SIZE from v$sga_dynamic_components where CURRENT_SIZE <> 0;
SQL>
SQL> select COMPONENT,CURRENT_SIZE from v$sga_dynamic_components where CURRENT_SIZE <> 0;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 62914560
large pool 4194304
java pool 4194304
DEFAULT buffer cache 117440512
KEEP buffer cache 8388608
RECYCLE buffer cache 4194304
---------------------------------------------------------------- ------------
shared pool 62914560
large pool 4194304
java pool 4194304
DEFAULT buffer cache 117440512
KEEP buffer cache 8388608
RECYCLE buffer cache 4194304
6 rows selected.
其他SGA参数
lock_sga boolean FALSE
pre_page_sga boolean FALSE
pre_page_sga
默认FALSE
当启动时SGA中的内存是虚拟内存 使用每个内存页前要通过touch动作
修改true
就会在启动时将所有申请的虚拟内存都touch一遍 以后用就不需touch了
但相应的会使启动速度变慢
lock_sga
将全部SGA都锁在物理内存中 禁止做页入页出
将全部SGA都锁在物理内存中 禁止做页入页出
上面两个参数要靠平台支持才可以启效果.
/etc/sysctl.conf 中的
安装时参数:
kernel.shmmax 要大于等于SGA_MAX_SIZE
如果小于 就会变成一个SGA有两个内存段 造成在两个内存段中来回切换 性能下降
[root@dba ~]# ipcs -m
kernel.shmmax 要大于等于SGA_MAX_SIZE
如果小于 就会变成一个SGA有两个内存段 造成在两个内存段中来回切换 性能下降
[root@dba ~]# ipcs -m
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x740203cd 1605632 root 600 4 0
0x264feb88 2293761 oracle 640 318767104 18
0x740203cc 1572866 root 600 4 0
key shmid owner perms bytes nattch status
0x740203cd 1605632 root 600 4 0
0x264feb88 2293761 oracle 640 318767104 18
0x740203cc 1572866 root 600 4 0
[root@dba ~]#
如果你的oracle异常停止 内存段不能释放 这时也无法启动
释放内存段的方法
[root@dba ~]# ipcrm -m SHMID
如果这条命令还不能释放 只能重新启动操作系统.
释放内存段的方法
[root@dba ~]# ipcrm -m SHMID
如果这条命令还不能释放 只能重新启动操作系统.
扫一扫
1637
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
