参考文献
1.Simplified Approach to Resolve ORA-4031
一、背景
客户反应所有产品均无法正常使用的消息,说应用出现平均时长超时告警,查看数据库告警信息,发现数据库1节点实例挂了,2节点出现活动会话告警且CPU利用率几乎100%。
二、初步分析
查看1节点alert日志,发现实例在重启之前出现ora-04031错误,具体错误如下
ORA-04031: unable to allocate 4144 bytes of shared memory ("shared pool","select (t3.l1_name||'/'||t3....","qksqb.c.kgght","qksqb.c.kgght")
首先看看ora-04031错误究竟是个什么东西
04031, 00000, "unable to allocate %s bytes of shared memory ("%s","%s","%s,"%s")" // *Cause: More shared memory is needed than was allocated in the shared pool. // *Action: If the shared pool is out of memory, either use the // dbms_shared_pool package to pin large packages, // reduce your use of shared memory, or increase the amount of // available shared memory by increasing the value of the // INIT.ORA parameters "shared_pool_reserved_size" and // "shared_pool_size". // If the large pool is out of memory, increase the INIT.ORA // parameter "large_pool_size".
出现ora-04031错误是因为shared pool内存不够,于是查看shared pool内存大小
SQL> show parameter shared_pool NAME TYPE VALUE -------------------------- ----------- ------------------------------ shared_pool_reserved_size big integer 3516504473 shared_pool_size big integer 40G
40G的shared pool内存,已经不小了。
三、临时解决方案
方案1、 简单粗暴,直接刷新shared pool
alter system flush shared_pool;
此方案针对报ora-04031但是还未宕机的情况,数据库实例若已宕机重启,shared pool自然会重新分配,也就不用刷新了,此外,在大库上刷新shared pool也有导致宕机的风险。
方案2、增大shared pool
既然是因为shared pool内存不够才报错,那就增大shared pool内存,但shared pool内存位于SGA(System Global area)中,而SGA用的是机器的物理内存,因此得考虑sga和机器实际物理内存大小。
#查看机器物理内存 [oracle@dgg-dggxxxxxxxxx ~]$ free -g total used free shared buffers cached Mem: 504 416 87 2 8 114 -/+ buffers/cache: 293 210 Swap: 31 0 31
机器上还有87g的内存
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 200G sga_target big integer 200G SQL> select sum(bytes)/1024/1024/1024 GB from v$sgastat; GB ---------- 199.107129 SQL> select sum(value)/1024/1024/1024 GB from v$sga; GB ---------- 199.107132
SGA大小为200g,因此可以考虑将shared pool调大25%,即增大10g
alter system set sga_max_size=210g scope=spfile; alter system set sga_target=210g scope=spfile; alter system set shared_pool_size=50g scope=spfile;
重启数据库实例,使之生效。
四、根本原因分析
shared pool包括两部分,Dictionary cache和Library cache。Dictionary cache记录的是Oracle系统内部管理所需要的数据字典信息,例如用户名、数据对象和权限等,大小变化不大。Library cache记录了sql的相关信息,例如sql文本、执行计划等,是可变的,shared pool内存不足原因就在于存放了大量的sql信息。
1. 查询各用户下sql占用shared pool信息
SELECT parsing_schema_name, Sum (sharable_mem) / 1024 / 1024 / 1024 gb FROM gv$sqlarea GROUP BY parsing_schema_name ORDER BY 2 DESC;
2. 查询用户sql有无硬解析
SELECT parsing_schema_name, Substr (sql_text, 1, 150), Sum(sharable_mem) / 1024 / 1024 MB, Count(Substr (sql_text, 1, 150)) cnt FROM v$sqlarea WHERE parsing_schema_name = '&schema_name' GROUP BY parsing_schema_name, Substr (sql_text, 1, 150) HAVING Count(Substr (sql_text, 1, 150)) > 100 ORDER BY 4 DESC, 3 DESC;
若查询结果中,cnt字段有值,则该用户下sql存在有硬解析的情况。
五、关于SGA
查看SGA大小
SQL> show parameter sga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 200G sga_target big integer 200G SQL> select sum(bytes)/1024/1024/1024 GB from v$sgastat; GB ---------- 199.107129 SQL> select sum(value)/1024/1024/1024 GB from v$sga; GB ---------- 199.107132
查看SGA空间的组成
-- 也可以直接 show sga SQL> select name,value/1024/1024 MB from v$sga; NAME MB -------------------- ---------- Fixed Size 2.13716888 Variable Size 79360.0035 Database Buffers 124416 Redo Buffers 107.5625
在v$sga视图中,SGA空间被分为4部分,Fixed Size存放一些与数据库版本和平台相关的信息;Database Buffers存放数据库缓存信息,太大会增加数据库管理负担,太小会降低数据库命中率,增加IO负担,一般启用自动内存管理;Redo Buffers存放重做日志的缓冲,重做日志并不是直接写到重做日志文件,而是从Redo Buffers写到重做日志文件,因为内存IO速率比文件要高;Variable Size,我们来看下Variable Size里有什么吧。
查看Variable Size组成
select pool,sum(bytes)/1024/1024 MB from v$sgastat group by pool; POOL MB ------------ ---------- 124525.7 java pool 3584 streams pool 8192 shared pool 65536 large pool 2048 SQL> select sum(bytes)/1024/1024 MB from v$sgastat where pool !=' '; MB ---------- 79360
v$sga视图中Variable Size的大小等于java pool、streams pool、shared pool和large pool 大小之和,v$sgastat视图pool字段为空的行大小等于Fixed Size、Database Buffers 和Redo Buffers 大小之和。
查询shared pool剩余空间大小
SELECT * FROM v$sgastat a WHERE a.pool = 'shared pool' AND a.NAME = 'free memory';
六、注意事项
- 在启用自动内存管理的情况下,直接调大shared pool而不调大SGA,数据库会自动调小Database Buffers。
- 不使用绑定变量直接在程序中写入大量类似的sql会导致大量硬解析,占用大量的shared pool,引发ora-04031报错甚至导致数据库宕机,此外,大量的硬解析还会导致主机CPU使用率长期保持在高位。