今天网友问到了v$sgainfo中的Free SGA Memory Available的一些情况,开始我也比较迷糊,为什么会出现Free SGA Memory Available的值不为0,通过查询一些试验和测试,对这个问题进行了有力的说明
数据库版本10G
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production
当前SGA各组件信息
SQL> select name,bytes/1024/1024 from v$sgainfo; NAME BYTES/1024/1024 -------------------------------- --------------- Fixed SGA Size 1.20853043 Redo Buffers 6.7890625 Buffer Cache Size 192 Shared Pool Size 88 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 8 Granule Size 4 Maximum SGA Size 304 Startup overhead in Shared Pool 36 Free SGA Memory Available 0 11 rows selected.
sga配置
SQL> show parameter sga; NAME TYPE VALUE ------------------------------------ ----------- --------------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 304M sga_target big integer 304M
在10g中，当采用asmm管理时,如果sga_max_size=sga_target,则Free SGA Memory Available为0
修改sga_target
SQL> alter system set sga_target=290M; System altered.
再次查询v$sgainfo
SQL> select name,bytes/1024/1024 from v$sgainfo; NAME BYTES/1024/1024 -------------------------------- --------------- Fixed SGA Size 1.20853043 Redo Buffers 6.7890625 Buffer Cache Size 180 Shared Pool Size 88 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 8 Granule Size 4 Maximum SGA Size 304 Startup overhead in Shared Pool 36 Free SGA Memory Available 12 11 rows selected.
再次查看sga_target值
SQL> show parameter sga; NAME TYPE VALUE ------------------------------------ ----------- -------------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 304M sga_target big integer 292M
在10g中，当采用asmm管理时,如果sga_max_size>sga_target,则Free SGA Memory Available为sga_max_size-sga_target
找出sga_target修改为292M而不是290M原因
SQL> select component, granule_size from v$sga_dynamic_components;
COMPONENT GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool 4194304
large pool 4194304
java pool 4194304
streams pool 4194304
DEFAULT buffer cache 4194304
KEEP buffer cache 4194304
RECYCLE buffer cache 4194304
DEFAULT 2K buffer cache 4194304
DEFAULT 4K buffer cache 4194304
DEFAULT 8K buffer cache 4194304
DEFAULT 16K buffer cache 4194304
DEFAULT 32K buffer cache 4194304
ASM Buffer Cache 4194304
13 rows selected.
SQL> select 4194304/1024/1024 from dual;
4194304/1024/1024
-----------------
4
SQL> select 292/4 from dual;
292/4
----------
73
因为sga的内存分配是按照GRANULE为单位进行的,而在该库中sga对应组件的GRANULE为4M,所以我们修改的290M的最近的GRANULE整数倍为292M
sga_target为0的情况
SQL> show parameter sga_target; NAME TYPE VALUE ------------------------------------ ----------- ---------- sga_target big integer 0 SQL> select name,bytes/1024/1024 from v$sgainfo; NAME BYTES/1024/1024 -------------------------------- --------------- Fixed SGA Size 1.20846176 Redo Buffers 6.7890625 Buffer Cache Size 180 Shared Pool Size 88 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 8 Granule Size 4 Maximum SGA Size 292 Startup overhead in Shared Pool 36 Free SGA Memory Available 0 11 rows selected.
在10g中，如果不采用asmm内存管理模式,Free SGA Memory Available为0
11g数据库版本
SQL> select * from v$version; BANNER ----------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production
查询v$sgainfo信息
SQL> select name,bytes/1024/1024 from v$sgainfo; NAME BYTES/1024/1024 -------------------------------- --------------- Fixed SGA Size 1.28236008 Redo Buffers 6.03515625 Buffer Cache Size 20 Shared Pool Size 116 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 8 Shared IO Pool Size 0 Granule Size 4 Maximum SGA Size 299.320313 Startup overhead in Shared Pool 52.2684898 Free SGA Memory Available 140
oracle内存相关参数设置
SQL> show parameter memory; NAME TYPE VALUE ------------------------------------ ----------- ------------ hi_shared_memory_address integer 0 memory_max_target big integer 300M memory_target big integer 300M shared_memory_address integer 0 SQL> show parameter pga; NAME TYPE VALUE ------------------------------------ ----------- ------------ pga_aggregate_target big integer 0
数据库动态组件内存分配
SQL> select COMPONENT,CURRENT_SIZE/1024/1024 CURRENT_SIZE from V$MEMORY_DYNAMIC_COMPONENTS; COMPONENT CURRENT_SIZE ---------------------------------------------------------------- ------------ shared pool 116 large pool 4 java pool 4 streams pool 8 SGA Target 160 DEFAULT buffer cache 20 KEEP buffer cache 0 RECYCLE buffer cache 0 DEFAULT 2K buffer cache 0 DEFAULT 4K buffer cache 0 DEFAULT 8K buffer cache 0 DEFAULT 16K buffer cache 0 DEFAULT 32K buffer cache 0 Shared IO Pool 0 PGA Target 140 ASM Buffer Cache 0 16 rows selected.
初步怀疑:在11g的amm内存管理模式下Free SGA Memory Available和PGA Target相等
尝试修改pga_aggregate_target值
SQL> alter system set pga_aggregate_target=150M; System altered.
再次查询v$sgainfo
SQL> select name,bytes/1024/1024 from v$sgainfo; NAME BYTES/1024/1024 -------------------------------- --------------- Fixed SGA Size 1.28236008 Redo Buffers 6.03515625 Buffer Cache Size 8 Shared Pool Size 116 Large Pool Size 4 Java Pool Size 4 Streams Pool Size 8 Shared IO Pool Size 0 Granule Size 4 Maximum SGA Size 299.320313 Startup overhead in Shared Pool 52.2684898 Free SGA Memory Available 152
查询动态组件内存分布
SQL> select COMPONENT,CURRENT_SIZE/1024/1024 CURRENT_SIZE from V$MEMORY_DYNAMIC_COMPONENTS; COMPONENT CURRENT_SIZE ---------------------------------------------------------------- ------------ shared pool 116 large pool 4 java pool 4 streams pool 8 SGA Target 148 DEFAULT buffer cache 8 KEEP buffer cache 0 RECYCLE buffer cache 0 DEFAULT 2K buffer cache 0 DEFAULT 4K buffer cache 0 DEFAULT 8K buffer cache 0 DEFAULT 16K buffer cache 0 DEFAULT 32K buffer cache 0 Shared IO Pool 0 PGA Target 152 ASM Buffer Cache 0 16 rows selected.
进一步证明:在11g的amm内存管理模式下Free SGA Memory Available和PGA Target相等
设置pga_aggregate_target为150M,PGA Target为152M原因分析
SQL> select COMPONENT,GRANULE_SIZE from V$MEMORY_DYNAMIC_COMPONENTS; COMPONENT GRANULE_SIZE ---------------------------------------------------------------- ------------ shared pool 4194304 large pool 4194304 java pool 4194304 streams pool 4194304 SGA Target 4194304 DEFAULT buffer cache 4194304 KEEP buffer cache 4194304 RECYCLE buffer cache 4194304 DEFAULT 2K buffer cache 4194304 DEFAULT 4K buffer cache 4194304 DEFAULT 8K buffer cache 4194304 DEFAULT 16K buffer cache 4194304 DEFAULT 32K buffer cache 4194304 Shared IO Pool 4194304 PGA Target 4194304 ASM Buffer Cache 4194304 16 rows selected.
原理同上述10g中的sga_target分析，不再重复,主要就是:150M不能被4M整除，所以取最近的152M
整体总结
1.如果不采用asmm和amm,Free SGA Memory Available为0
2.如果采用asmm,当sga_max_size=sga_target,则Free SGA Memory Available为0
3.如果采用asmm,当sga_max_size>sga_target,则Free SGA Memory Available为sga_max_size-sga_target
4.如果采用amm,Free SGA Memory Available和PGA Target相等