记录一次生产库遇到的4031错误，后来通过调整sga大小将问题解决了

报错信息：

ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select user#,password,datats...","SQLA","tmp")
Incident details in: /u01/app/oracle/diag/rdbms/twprod/xxxxx/incident/incdir_237848/xxxxx_ora_36005_i237848.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Jun 06 19:57:24 2017
Errors in file /u01/app/oracle/diag/rdbms/xxxx/xxxx/trace/xxxx_e004_106234.trc (incident=238416):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select /*+ INDEX(TAB AQ$_AQ...","SQLA","tmp")
Incident details in: /u01/app/oracle/diag/rdbms/xxxx/xxxx1/incident/incdir_238416/xxxx1_e004_106234_i238416.trc

 

查看当前sga设置：

SYS@ xxxx1> show parameter sga

NAME                                    TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                                 boolean      FALSE
pre_page_sga                        boolean      FALSE
sga_max_size                         big integer  20G
sga_target                              big integer  20G

 

调整sga为30g并重启数据库使参数生效：

SYS@ xxxx1> alter system set sga_max_size=30G scope=spfile;

SYS@ xxxx1> alter system set sga_target=30G scop=spfile;

SYS@ xxxx1> shutdown immediate

SYS@ xxxx1> startup

 

以下是Mos的解决方法，以供参考：

ORA-04031: Unable To Allocate 32 Bytes Of Shared Memory ("shared pool","select tablespace_id, rfno, ...","SQLA","tmp")" (文档 ID 1986741.1)  

In this Document

 

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later

Information in this document applies to any platform.

SYMPTOMS

Alert log reports errors like below in almost all the instances:

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

 

CAUSE

The trace file shows there are 6 sga subpools allocated.

... 

****************** End of process map dump ************

==============================================

TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 1

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

"KGH: NO ACCESS " 4056 MB 61%

"gcs dynamic s " 713 MB 11%

"free memory " 625 MB 9%

"gcs dynamic r " 501 MB 8%

"init_heap_kfsg " 195 MB 3%

"FileOpenBlock " 122 MB 2%

"gcs resources " 64 MB 1%

"ges enqueues " 62 MB 1%

"gcs shadows " 45 MB 1%

"PRTMV " 40 MB 1%

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

free memory 625 MB

memory alloc. 6031 MB

Sub total 6656 MB

==============================================

TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 2

-------