参考文献
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使用率长期保持在高位。
