背景： 2017-04-21 14：00 收到用户反馈数据库db1连接不上，报错信息是“ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool",...)”
问题： 是什么原因导致shared pool内存不足呢？
分析： 1. shared pool内存不足可能由如下原因造成： 1.1 shared pool szie配置过少 1.2 硬解析过多导致shared pool内存消耗超出预期 1.3 触发bug 2. 数据说话，生成awr验证，生成13-14点awr，发现这个时间可能不是第一现场时间，原因是shared pool size 已经在13点时变成48G,buffer cache只有128mb，由此可以证明shared pool在13点以前就出现空间不足，数据库自动扩展了，数据如下：
3. 调查shared pool size内存不足的真实现场时间点，运行如下sql语句： select * from sys.DBA_HIST_MEM_DYNAMIC_COMP where component='shared pool' order by snap_id desc; 发现在2017-04-21 11:26分左右shared pool内存自动调整。 从而推测该时间为第一现场时间，于是生成11-12点的awr报告。 4. 11-12的awr重要数据如下：
从以上数据可以看出11-12点果然是第一现场时间，11点开始时shared pool size还是24G，但是12点就变成48G，说明在这段时间发生了一些事严重占用shared pool，所以数据库自动调整。 接着发现，每秒有2.1parse 却有1.6个hard parses ，约有8成的SQL语句是硬解析 <<<<<这是很严重的。 （这里犯了一个轻率的错语，事实上从每秒2.1解析来看，不一定会令shared pool size占满。所以本篇就当成hard parse原因造成的处理，我将在另一篇文章中"2017-04-24 DBA日记，触发BUG导致shared pool中的PRTMV memry剧增"说明ora-04031的在我面对场景的真正原因.） shared pool现有内存24G，还是产生大量硬解析，那就是说明不是因shared pool size设置不当所致，那就是非常有可能是由于程序代码所致。 5. 调研分析是那些代码导致hard parse，从awr的sql order by executions上看，最有可能是以下语句造成的：
6. 进一步确认，找出硬解析最多的SQL语句如下： select to_char(force_matching_signature),count(*) hard_parses from v$sqlarea group by to_char(force_matching_signature) having count(*)>5 order by 2 desc;
select * from v$sqlarea where force_matching_signature=7756258419218828704;
整合后： select b.*,a.* from v$sqlarea a,( select force_matching_signature,count(*) hard_parses from v$sqlarea group by force_matching_signature having count(*)>5) b where A.FORCE_MATCHING_SIGNATURE=b.force_matching_signature and b.force_matching_signature >0 order by 2 desc; 判断硬解析的重要指标force_matching_signature，当该指标具有相同值时，就是说明采用同一语句，只是条件值不同，在cursor_sharing=force的情况下是可以共享同一计划，在exact下不能，必须显式使用绑定变量方式。 旧办法： col sqltext format a50 select dbms_lob.substr(sql_text,20,1 ) sqltext,count(*) from dba_hist_sqltext group by dbms_lob.substr(sql_text,20,1 ) having count(*)>=2 order by 2; 7. 找出问题语句，改造为绑定变量方式。 解决方法： 1. 找出问题的语句，改为绑定变量的方式： 2. 绑定变量的使用如下： plsql: execute immediate "select * from t1 where col1=:1" using v_col1; java方式： String v_id = 'xxxxx'; String v_sql = 'select name from table_a where id = ? '; //嵌入绑定变量 stmt = con.prepareStatement( v_sql ); stmt.setString(1, v_id ); //为绑定变量赋值 stmt.executeQuery();
补充备注与shared pool使用相关的系统表： select * From SYS.V_$SHARED_SERVER_MONITOR;
select * from SYS.V_$SHARED_POOL_RESERVED;
select * from SYS.V_$LIBRARYCACHE;
select * from SYS.V_$LIBRARY_CACHE_MEMORY;
select * from SYS.V_$SGASTAT where pool='shared pool' order by 3 desc;