记录一次生产库遇到的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
-------