一条SQL引起的ORA-04031错误

1 查看后台报错

Mon Jul 15 15:32:40 2019

Errors in file /u01/xxx/trace/xxxx_cjq0_11002.trc  (incident=148290):

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","SQLA","tmp")

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /u01/xxx/trace/xxxx_cjq0_11002.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","SQLA","tmp")

2 查看申请内存失败大小的尺寸

              HIT                     LAST MISS LAST FAILURE

    REQUESTS   %          FAILURES        SIZE         SIZE

------------ ---- ---------------- ------------ ------------

           4  100          360,051            0        4,160

3  查看数据库共享池最少保留的的值为4400 ，并确认失败时是否在4400 以下

_shared_pool_minsize_on                  FALSE

_shared_pool_reserved_min_alloc           4400

_shared_pool_reserved_pct                 5

SYS@xxx>select count(*) from v$shared_pool_reserved where request_failures > 0 and last_failure_size < 4400;

  COUNT(*)

----------

         1

4 查看SGA 内存变化，发现SGA有收缩和扩展：

5 查看trace 文件的等待事件，发现SGA在等待扩展，说明SGA不够用了：

6  查看librarycache使用的情况，发现再SQL AREA区域失败次数特别多，故认为有可能时低效的SQL将内存耗尽导致的ORA-04031错误。

7  查看ORA-04031 报错时运行的SQL ，发现bts5q1d28056d 和 b9qc0ymf966m3 SQL _ ID 占用活动 60% ，故认为有可能是这两个 SQL 的引起的 ORA-04031 错误

7 月15 日

7月16日：

查看 b9qc0ymf966m3 的执行计划，发现全表扫描：

查看优化建议：

1- Index Finding (see explain plans section below)

--------------------------------------------------

  The execution plan of this statement can be improved by creating one or more indices.

  Recommendation (estimated benefit: 99.84%)

  ------------------------------------------

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

    create index usera.IDX$$_26BD70001 on usera.table_a("CONS_NO");

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

    create index userb.IDX$$_26BD70002 on   userb.table_b ("BASE_TACTIC_NO");

 优化后发现性能比以前提升了60多倍：

8  经过一个月的观察，优化 b9qc0ymf966m3 这个SQL后，再没有报ORA-04031错误，故认为问题解决。

总结：

          此系统运行几年，都没有报ORA-04031错误，怎么会突然报ORA-04031错误，故认为SQL引起的可能性特别大 ，故没有调整SGA的大小，首先优化相关SQL进行观察，发现数据库确实不在报ORA-04031错误，故认为问题彻底解决。