Oracle SGA 自动管理特性(sga_target参数)

    最近有网友对Oracle SGA内存自动管理特性不是很清楚,可能是由于当时翻译的 Oracle 10g SGA 的自动化管理 比较生涩,下面依旧是针对这个问题给出在Oracle 10g环境中描述,并给出示例以便更好的理解。

1、相关参数描述 a、参数SHARED_POOL_SIZE SHARED_POOL_SIZE = integer [K | M | G]

Default value   If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database).    If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.    If SGA_TARGET is not set (32-bit platforms): 32 M, rounded up to the nearest granule size.    If SGA_TARGET is not set (64-bit platforms): 84 M, rounded up to the nearest granule size. Range of values Minimum:     the granule size Maximum:    operating system-dependent b、参数SGA_TARGET SGA_TARGET = integer [K | M | G] Default value 0 (SGA autotuning is disabled)

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:     Buffer cache (DB_CACHE_SIZE)     Shared pool (SHARED_POOL_SIZE)     Large pool (LARGE_POOL_SIZE)     Java pool (JAVA_POOL_SIZE)     Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:   Log buffer   Other buffer caches, such as KEEP, RECYCLE, and other block sizes   Fixed SGA and other internal allocations

The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

2、参数sga_target为零值的情形

--#编辑一个临时的参数文件,并设置sga_target=0,以及设定几个pool池的size,db_cache_size,如下
robin@SZDB:/u02/database/SYBO2SZ> grep size SYBO2SZ.ora.tmp   
*.db_block_size=8192
*.db_cache_size=285212672
*.db_recovery_file_dest_size=1G
*.java_pool_size=4194304
*.large_pool_size=4194304
*.shared_pool_size=293601280
*.streams_pool_size=4194304
robin@SZDB:/u02/database/SYBO2SZ> grep target SYBO2SZ.ora.tmp
*.pga_aggregate_target=199229440
*.sga_target=0

--#使用临时的参数文件启动数据库
robin@SZDB:/u02/database/SYBO2SZ> sqlplus / as sysdba
idle> startup pfile=/u02/database/SYBO2SZ/SYBO2SZ.ora.tmp
ORACLE instance started.
---可以看到此时sga_target为0
idle> show parameter sga_tar

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0

-->查看此时内存分配的情况
idle> SELECT name, VALUE
  2    FROM v$parameter
  3   WHERE name IN
  4            ('shared_pool_size',
  5             'java_pool_size',
  6             'streams_pool_size',
  7             'log_buffer',
  8             'db_cache_size',
  9             'db_2k_cache_size',
 10             'db_4k_cache_size',
 11             'db_8k_cache_size',
 12             'db_16k_cache_size',
 13             'db_32k_cache_size',
 14             'db_keep_cache_size',
 15             'db_recycle_cache_size',
 16             'large_pool_size');

NAME                           VALUE
------------------------------ --------------------
shared_pool_size               293601280
large_pool_size                4194304
java_pool_size                 4194304
streams_pool_size              4194304
db_cache_size                  285212672
db_2k_cache_size               0
db_4k_cache_size               0
db_8k_cache_size               0
db_16k_cache_size              0
db_32k_cache_size              0
db_keep_cache_size             0
db_recycle_cache_size          0
log_buffer                     6120448

13 rows selected.

-- Author :Robinson
-- Blog   :http://blog.csdn.net/robinson_0612

--使用临时的pfile来创建spfile
idle>  create spfile from pfile='/u02/database/SYBO2SZ/SYBO2SZ.ora.tmp';

File created.

3、参数sga_target非零值的情形

--重启db
idle> startup force;
ORACLE instance started.

idle> select distinct isspecified from v$spparameter;

ISSPEC
------
TRUE    -->为true表名此时使用了spfile启动数据库
FALSE

--此时sga_max的值为572m
idle> show parameter sga_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 572M

--修改sga_target到非零值
idle> alter system set sga_target=572m;      

System altered.

--此时sga_target变成了576m,这个是由于分配粒度和当前已分配内存之和造成的,自动向上取整
idle> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 576M

--参数文件指定的值分配的总大小为564m
idle> select (285212672+4194304+4194304+293601280+4194304)/1024/1024 from dual;

(285212672+4194304+4194304+293601280+4194304)/1024/1024
-------------------------------------------------------
                                                    564
                                                    
--来看一下内存的分配粒度,此时为4mb
idle> @mem_granule

NAME                                VALUE                          DESCBTION
----------------------------------- ------------------------------ ----------------------------
_ksmg_granule_size                  4194304                        granule size in bytes
_ksmg_granule_locking_status        1                              granule locking status

--值572m也是4的整数倍,这个应该是有一部分固定或其他方面内存耗用所致当前被分配了576m
--如果参数文件中总大小缩小的话,我们可以指定sga_target<=sga_max_size
idle> select 576/4 from dual;

     576/4
----------
       144
       
--从spfile生成pfile来观察sga_target非零值发生了什么变化
idle> create pfile='/tmp/tmp.ora' from spfile;

File created.

--可以看到增加了以dbname开头的且带下划线的和sga_target内存分配相关的项
idle> ho grep size /tmp/tmp.ora
SYBO2SZ.__db_cache_size=285212672
SYBO2SZ.__java_pool_size=4194304
SYBO2SZ.__large_pool_size=4194304
SYBO2SZ.__shared_pool_size=297795584
SYBO2SZ.__streams_pool_size=4194304
*.db_block_size=8192
*.db_cache_size=285212672
*.db_recovery_file_dest_size=1G
*.java_pool_size=4194304
*.large_pool_size=4194304
*.shared_pool_size=293601280
*.streams_pool_size=4194304

--/tmp/tmp.ora文件中的sga_target变成了603979776=576m
idle> ho grep sga_target /tmp/tmp.ora
*.sga_target=603979776

--下面编辑/tmp/tmp.ora,移除原来的有关内存的配值,用带下划线的值来启动数据库
idle> vi /tmp/tmp.ora
idle> ho grep size /tmp/tmp.ora
SYBO2SZ.__db_cache_size=285212672
SYBO2SZ.__java_pool_size=4194304
SYBO2SZ.__large_pool_size=4194304
SYBO2SZ.__shared_pool_size=297795584
SYBO2SZ.__streams_pool_size=4194304
*.db_block_size=8192
#*.db_cache_size=285212672
*.db_recovery_file_dest_size=1G
#*.java_pool_size=4194304
#*.large_pool_size=4194304
#*.shared_pool_size=293601280
#*.streams_pool_size=4194304

--首先关闭数据库
idle> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

--使用临时的pfile启动
idle> startup pfile=/tmp/tmp.ora
ORACLE instance started.

--下面的查询可以看到所有和sga_target相关的内存分配全部变成了0
idle> SELECT name, VALUE
  2    FROM v$parameter
  3   WHERE name IN
  4            ('shared_pool_size',
  5             'java_pool_size',
  6             'streams_pool_size',
  7             'log_buffer',
  8             'db_cache_size',
  9             'db_2k_cache_size',
 10             'db_4k_cache_size',
 11             'db_8k_cache_size',
 12             'db_16k_cache_size',
 13             'db_32k_cache_size',
 14             'db_keep_cache_size',
 15             'db_recycle_cache_size',
 16             'large_pool_size');

NAME                                VALUE
----------------------------------- ------------------------------
shared_pool_size                    0
large_pool_size                     0
java_pool_size                      0
streams_pool_size                   0
db_cache_size                       0
db_2k_cache_size                    0
db_4k_cache_size                    0
db_8k_cache_size                    0
db_16k_cache_size                   0
db_32k_cache_size                   0
db_keep_cache_size                  0
db_recycle_cache_size               0
log_buffer                          6120448

13 rows selected.

--此时sga_max_size也变成了576m,这是因为sga_target必须小于等于sga_max_size
idle> show parameter sga_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 576M

4、小结 a、当设置sga_target为非零值时下列参数受到sga_targe的控制,但其总值不超过sga_target。    Buffer cache (DB_CACHE_SIZE)    Shared pool (SHARED_POOL_SIZE)    Large pool (LARGE_POOL_SIZE)    Java pool (JAVA_POOL_SIZE)    Streams pool (STREAMS_POOL_SIZE) b、非零值的sga_target时,几个参数之间的大小可以动态分配,且根据需要来动态调整各个部分的大小。 c、如果在sga_target非零值时,也设定了DB_CACHE_SIZE等上述几个参数,则sga_targe分配不小于所设置的值作为初始值。 d、当设置sga_target为零值时,SGA相关参数内存的分配由参数控制,且无法根据系统状况实施动态调整大小。 f、Oracle 10g之后建议将SGA的内存管理设置为由sga_target动态管理。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Spark学习技巧

实战phoenix

一 安装部署 1, 下载 http://archive.apache.org/dist/phoenix/ 本文下载的是apache-phoenix-4.12....

40310
来自专栏ChaMd5安全团队

老司机带你过常规WAF

0x00 前言 最近看了不少关于WAF绕过的文章,想把理论深化到实践当中去,于是就有了您正在看的这篇文章,这篇文章分为两大部分,分别写的是SQL注入相关的WAF...

56111
来自专栏AILearning

Apache Zeppelin 中 Cassandra CQL 解释器

Name Class Description %cassandra CassandraInterpreter 为Apac...

3079
来自专栏IT杂记

关于MySQL DNS解析探究之二:unauthenticated user

把这篇没写完的文章写完,2015年的事就不留到2016了 开启DNS解析 mysql> show variables like 'skip_name_resol...

2848
来自专栏王硕

原 为PostgreSQL添加插件

6335
来自专栏沃趣科技

事件统计 | performance_schema全方位介绍

在上一篇 《事件记录 | performance_schema全方位介绍"》中,我们详细介绍了performance_schema的事件记录表,恭喜大家在学习p...

2153
来自专栏Java帮帮-微信公众号-技术文章全总结

第三十天-加强2-多表查询&JDBC&连接池&DBUtils&综合案例【悟空教程】

第三十天-加强2-多表查询&JDBC&连接池&DBUtils&综合案例【悟空教程】

1724
来自专栏Android知识点总结

SpringBoot-07-之数据库JPA(CRUD)

1332
来自专栏Snova云数仓

Greenplum资源队列初识

在Greenplum的4.x版本之后,加入了资源队列的概念,其主要作用就是限制用户或者单个SQL对资源的消耗。避免出现消耗过多资源,影响其他用户或者SQL计算。...

81014
来自专栏张善友的专栏

Silverlight 2 Beta 2的Isolated Storage

Silverlight beta 2 的配置有一个重大变化就是对DRM 和Application Storage的配置 ? Application stora...

20610

扫码关注云+社区

领取腾讯云代金券