在Windows上调整SGA大小遭遇ora-27100、ora-27102错误的

页面导航:首页 > 数据库 > oracle > 在Windows上调整SGA大小遭遇ora-27100、ora-27102错误的

在Windows上调整SGA大小遭遇ora-27100、ora-27102错误的

来源: 作者: 时间:2016-02-16 09:27 【

今天早上去一公司合作伙伴那里,协助处理他们某客户的数据库性能问题,那个库是Oracle 10 2 0 1的,前台业务系统是政府某机构查询系统,碰到的问题是首页展示非常慢,与之相关的S
今天早上去一公司合作伙伴那里,协助处理他们某客户的性能问题,那个库是 10.2.0.1的,前台业务是政府某机构查询系统,碰到的问题是首页展示非常慢,与之相关的SQL语句查询结果需要跑59s多,而其他页面相关模块的查询都只需要几秒就可以出结果了。
 
碰到数据库性能问题通常从两个方面着手调整: 1. 内存参数调整 2. SQL语句优化
因此,首先就查看了该库的SGA参数,发现只分配了1.2G,而数据库服务器的物理内存为8G,显然这个值太小了。拉了一份AWR报告,显示shared pool只分配到了200多M,简直少的令人发指。这个数据库是运行在Windows 2003 Enterprise x64上面的,因此应该不存在SGA不能超过1.7G的限制,于是对SGA参数进行调整,目标是调整到OS物理内存的50%,即SGA=4G。
 
由于开始并未设置过sga_max_size的值,所以当调整实例sga_target为某个固定的值再重启后,如果sga_target的值大于sga_max_size的值,那么sga_max_size的值就会随着sga_target自动增加为相同的值,反之,则不会变。此时这2个值都是1200M。尽管sga_target是动态参数,但此时是不允许调大的,当我们需要设置sga_target=4G,就超过了sga_max_size的值,数据库会报错,所以,要调大SGA,还必须先修改sga_max_size,而该参数是静态参数,也就意味着需要停库,中午向客户申请了20分钟的停机时间,然后着手对该参数进行调整。
 
依次执行以下命令: SQL> alter system set sga_max_size=4G scope=spfile; SQL> shutdown immediate
 
当再次启动数据库的时候,碰到了问题,报了ora-27102: out of memory
 
SQL> startup ORA-27102: out of memory OSD-00022: Message 22 not found; product=RDBMS; facility=SOSD O/S-Error: (OS 8) Not enough storage is available to process this command. SQL>
 
之后无论是关闭或者启动数据库,哪怕只是启动到mount,都会报ora-27100错误:
SQL> shutdown immediate; ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist SQL> startup; ORA-27100: shared memory realm already exists SQL>
 
看来是设置sga_max_size=4G,造成了oracle占用OS内存过大,导致数据库无法启动,这里比较纳闷,为何设置SGA为物理内存的50%也会报错呢?Windows又不像/Unix那样,还有个maxshmall的限制。
 
由于是在spfile中修改的sga_max_size的值,现在数据库却无法启动了,由于还未进入到oracle实例,spfile也无法再次修改回来,相当于spfile被人为地损坏了,更糟糕的是,之前修改参数值的时候,忘记先生成一个pfile作为备份了,这可麻烦了。还好测试库上有一个同样10g实例,于是生成一个pfile,然后修改其中的路径及实例名为生产库的值后进行替换,拷贝到生产库的%ORACLE_HOME/database下面,再用这个pfile来启动数据库
SQL> startup pfile=E:\oracle\app\product\10.2.0\db_1\database\initnt.ora; ORA-27100: shared memory realm already exists
 
错误依然存在,难道数据库就这样无法启动了嘛?当然不会,这可是生产库,停了以后业务就都挂了,眼看20分钟的停机时间就要到了。
 
其实,在上运行的oracle实例有一点特殊,如果启动数据库实例时,由于sga_max_size设置过大而造成实例启动失败,尽管把实例启动,但此时仍然会有一个错误的实例存在,因而会导致shutdown immediate及shutdown abort都关闭不了,也无法startup,始终会报ora-27100。这是因为在缺省安装时,oracle实例的服务(oracleSERVICESID)会在windows启动时自动启动,且每次启动服务时,都会自动用默认的spfile启动实例(如果存在的话),因此就导致了一直出现ora-27100的内存错误。
 
知道了这个机制,那么再处理之前的内存错误就很容易了,先把错误的那个spfile删除掉,然后停止oracle实例相应的服务,再重新把服务起来,再用pifle启动数据库即可。
SQL> startup pfile=E:\oracle\app\product\10.2.0\db_1\database\initnt.ora;
 
这次数据库不在报ora-27100了,但是仍然会报ora-27102,这是怎么了,来来回回出现相同的问题,后来通过一次次的尝试,终于发现了一个事实,就是在pfile中设置成2G、3G时,再用之前的方法启动数据库,数据库都可以正常启动,唯独设置成4G时,就会出现ora-27102。只能接受这个现实了。于是就把sga_max_size设置为3G,sga_target也调整为3G,好歹也是比之前1G要多了2倍了。重新启动数据库之后,再用pfile重新创建了一个正确的spfile,调整SGA的任务算是完成了
SQL> alter system set sga_target=3G scope=both; SQL> create spfile from pfile; SQL> shutdown immediate; SQL> startup --用spfile再次启动数据库(推荐)
 
SGA增大之后,由于是采用10g的自动内存管理,shared pool的值也得到了相应的增加,对于跑SQL语句而言是有极大好处的。
 
调整完内存参数后,现在就要对相应的SQL语句来调整,由于SQL语句我并没有拿到,只能凭回忆说一下大致的情况,这个首页调用的SQL语句是个视图,视图中还有一个由存储过程生成的视图,用了半连接的in进行多表连接,查看了执行计划发现,2个视图中的子查询的多表连接都采用了union的方式,询问了一下,此处并无排序的需求,因此建议改成了union all,可以避免排序操作。另外视图中连接的这些表(共3个),无一例外地都是走了Full Table Scan,即全表扫描,没有一个用到索引,显然这不太合理,通过在一个查询字段”currentstate“上建立索引后,再次查询发现,该条语句单独跑的时候,cost立即从原来的800多降低到了200多,以此类推,我建议了他们在相应的查询列上建立索引,来优化这条SQL语句。优化思路提出来了,具体的优化过程由他们自己完成。
 
总结:
 
再次强调一下,数据库性能问题,先从两方面着手,一是调整数据库参数(查看内存参数设置是否合理等),二是对SQL语句进行调整(优化),分析执行计划,查看索引是否被高效地利用起来,另外需要结合AWR报告分析数据库是否负载过高(DB Time过高),存在性能瓶颈(TOP 5 event),命中率过低(Buffer Hit%、Library Hit%过低)等不利因素。
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<