这几天Oracle 11g 数据库遇到个问题,需要调整SGA的大小,我直接修改了SGA_MAX_SIZE 后,重启数据库,报错,数据库启动不了,报错内容大致是 SGA_MAX_SIZE 大于MEMORY_TARGET的值。由于自己本来对这一块了解的不多,就上网查了相关资料,现整理如下:
SGA(系统全局区)主要包括shared pool、buffer pool等若干内存池,对于SGA可用show parameter sga查询SGA的大小,也可用以下命令查询SGA的组成
SQL>select * from v$sga;
NAME                      VALUE
-------------------- ----------
Fixed Size              1374304
Variable Size         239077280
Database Buffers       67108864
Redo Buffers            6299648
Fixed Size
oracle 的不同平台和不同版本下可能不一样,但对于确定环境是一个固定的值,里面存储了SGA 各部分组件的信息,可以看作引导建立SGA的区域。
Variable Size
主要包括shared_pool_size、java_pool_size、large_pool_size 等内存设置
Database Buffers
指数据缓冲区,主要是db_cache_size 。
Redo Buffers
指日志缓冲区,log_buffer。
现在修改如下:
Log_buffer
对于日志缓冲区的大小设置,参考LGWR写的触发条件之后,我们会发现通常超过3M意义不是很大。作为一个正式系统,可能考虑先设置这部分为log_buffer=1—3M 大小,然后针对具体情况再调整。
Large_pool_size
这部分主要用来保存并行查询时候的一些信息,还有就是RMAN在备份的时候可能会使用到。,假如不使用MTS,建议在20—30M 足够了。
这部分是为了缓存已经被解析过的SQL,而使其能被重用,不再解析。对于一个新的SQL(shared_pool 里面不存在已经解析的可用的相同的SQL),数据库将执行硬解析,这是一个很消耗资源的过程。而若已经存在,则进行的仅仅是软分析(在共享池中寻找相同SQL),这样消耗的资源大大减少。所以我们期望能多共享一些SQL,并且如果该参数设置不够大,经常会出现ora-04031错误,表示为了解析新的
SQL,没有可用的足够大的连续空闲空间,这样自然我们期望该参数能大一些。但是该参数
的增大,却也有负面的影响,因为需要维护共享的结构,内存的增大也会使得SQL 的老化的代价更高,带来大量的管理的开销,所有这些可能会导致CPU 的严重问题。在一个充分使用绑定变量的比较大的统中,shared_pool_size 的开销通常应该维持在300M 以内。除非系统使用了大量的存储过程、函数、包,比如oracle
erp这样的应用,可能会达到500M甚至更高。于是我们假定一个1G内存的系统,可能考虑设置该参数为100M,2G 的系统考虑设置为150M,8G 的系统可以考虑设置为200—300M。
Data buffer
主要作用缓存DB BLOCK,减少甚至避免从磁盘上获取数据,通常,在允许的情况下,我们都尝试使得这部分内存更大。
对于以上参数,都可以用show parameter +[参数名称]查询响应的大小。使用自动内存管理后查询结构可能为0,也可以在pfile文件中查看这些参数的大小。
Oracle在简化内存管理方面过去几年做了巨大的努力,从Oracle 9i通过PGA_AGGREGATE_TARGET参数实现PGA自动管理开始,Oracle 10g通过SGA_TARGET参数实现了SGA的自动管理,Oracle 11g更是惊人地实现了数据库所有内存块的全自动化管理,它使得动态管理SGA和PGA成为现实。
9i 和以前的版本我们需要手动调整上述参数大小来优化Oracle数据库。从10g开始Oracle提供了自动SGA的管理(简称ASMM)就是指我们不再需要手工设置shared pool、buffer pool等若干内存池的大小,而是为SGA设置一个总的大小尺寸即可。Oracle 10g数据库会根据系统负载的变化,自动调整各个组件的大小,从而使得内存始终能够流向最需要它的地方。同时,初始化参数statistics_level必须设置为typical或all才能启动ASMM,否则如果设置为basic,则关闭ASMM。
10G增加了新参数SGA_TARGET,来启动ASMM,该参数定义了SGA的总大小,只需修改SGA_TARGET 的值即可 ,命令如下:
SQL>alter system set sga_target=400M  scope=spfile;
Oracle 10g还提供了另一个初始化参数sga_max_size。sga_target的值不能超过sga_max_size的值,修改sga_max_size时,必须重启实例才能生效,而sga_target则可以在线修改,立即生效,无须重启实例。
MEMORY_TARGET:动态控制SGA和PGA时,Oracle总共可以使用的共享内存大小,这个参数是动态的,因此提供给Oracle的内存总量是可以动态增大,也可以动态减小的。它不能超过MEMORY_MAX_TARGET参数设置的大小。默认值是0。
MEMORY_MAX_TARGET:这个参数定义了MEMORY_TARGET最大可以达到而不用重启实例的值,如果没有设置MEMORY_MAX_TARGET值,默认等于MEMORY_TARGET的值。
使用动态内存管理时,SGA_TARGET和PGA_AGGREGATE_TARGET代表它们各自内存区域的最小设置,要让Oracle完全控制内存管理,这两个参数应该设置为0。