ORA-04031 问题请教，有大量free memory仍报

Ghcost 发表于 2015-1-16 17:34:46

本帖最后由 Ghcost 于 2015-1-16 17:38 编辑

各位好，我这边数据库出现一个ora-04031错误，请帮忙一起分析一下，谢谢！

背景：
Linux + Oracle RAC 11.2.0.4，启用了EM
64GB内存，
32GB SGA，
buffer cache 和 share pool size 没有设。

1月13号早上，系统变慢，buffer cache 越来越小，最后只有256MB，share pool 增大到了 31GB，并有一些 ORA-04031错误出现。这时重启了数据库，恢复正常。## ALERT LOG 错误列表
# 第一次出现
Tue Jan 13 08:54:37 2015
Line 169: ORA-04031 heap dump being written to trace file tr2_ora_31775.trc # 超大日志，500MB
Line 172: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","TOPERATOR","KGLS^5011279e","kglHeapInitialize:temp")
Line 176: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","TTEMPLATEQUERYCONFIG","KGLS^dd5bd578","kglHeapInitialize:temp")
Line 180: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select vc_tradeday_type into...","SQLA","tmp")
Line 184: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","TMENUITEM","KGLS^9fe1e841","kglHeapInitialize:temp")
Line 188: ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select * from (select distin...","SQLA^f05a9da4","xplSetRws:planLines_ctxdef")
Line 192: ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select sysdate, dual.ROWID f...","SQLA","tmp")
Line 204: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT COUNT(*) FROM TUSERST...","SQLA","tmp")
删掉一些
Line 331: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","PROFILE[ DISCUZ_CODE_0 ]quot;,"KGLS^96449cd2","kglHeapInitialize:temp")
Line 336: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select /*+ all_rows leading(...","SQLA","tmp")
Line 341: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","SQLA","tmp")
Line 346: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","SQLA","tmp")
Line 351: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT COUNT(*) FROM TUSERST...","SQLA","tmp")
Line 356: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","PROFILE[ DISCUZ_CODE_0 ]quot;,"KGLS^96449cd2","kglHeapInitialize:temp")
Line 357: ORA-00604: error occurred at recursive SQL level 1
Line 358: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","PROFILE[ DISCUZ_CODE_0 ]quot;,"KGLS^96449cd2","kglHeapInitialize:temp")
Line 363: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select vc_tradeday_type into...","SQLA","tmp")
Line 367: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","SQLA","tmp")
Line 371: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","SQLA","tmp")
Line 376: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","SQLA","tmp")
Line 381: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","PROFILE[ DISCUZ_CODE_0 ]quot;,"KGLS^96449cd2","kglHeapInitialize:temp")
Line 382: ORA-00604: error occurred at recursive SQL level 1
Line 383: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","PROFILE[ DISCUZ_CODE_0 ]quot;,"KGLS^96449cd2","kglHeapInitialize:temp")
Line 388: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select vc_tradeday_type into...","SQLA","tmp")
Line 393: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","TOPERATOR","KGLS^5011279e","kglHeapInitialize:temp")
Line 398: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT COUNT(*) FROM TUSERST...","SQLA","tmp")
Line 446: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select count(*) from SYS.AQ$...","SQLA","tmp")
Line 451: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT COUNT(*) FROM TUSERST...","SQLA","tmp")
Line 456: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","SQLA","tmp")
Line 459: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","declare cnt number; begin cn...","SQLP","tmp")
Line 466: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","UPDATE TUSERSTATE SET (L_REF...","SQLA","tmp")
Line 471: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT COUNT(*) FROM TUSERST...","SQLA","tmp")
Line 479: ORA-00604: error occurred at recursive SQL level 1
Line 480: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","DBMS_LOGSTDBY","PLMCD^bd2af6d6","kglHeapInitialize:temp")
Line 492: ORA-00604: error occurred at recursive SQL level 1
Line 493: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","DBMS_LOGSTDBY","PLMCD^bd2af6d6","kglHeapInitialize:temp")
Line 496: ORA-00604: error occurred at recursive SQL level 1
Line 497: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","with v_unitstock as (select ...","KGLS^96fb4821","kglHeapInitialize:temp")
Line 499: ORA-4031 encountered when generating server alert SMG-3503

Tue Jan 13 09:01:23 2015
Errors in file tr2_smon_10891.trc:
Line 502: ORA-00604: error occurred at recursive SQL level 1
重复几十次
Line 768: ORA-00604: error occurred at recursive SQL level 1
Line 769: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","update sys.col_usage$ set ...","SQLA","tmp")
Tue Jan 13 09:05:21 2015
Errors in file tr2_smon_10891.trc:
Line 772: ORA-00604: error occurred at recursive SQL level 1
Line 773: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","update sys.col_usage$ set ...","SQLA","tmp")

## tr2_smon_10891.trc 一些重要信息:
==============================================
TOP 20 MEMORY USES ACROSS SGA HEAP 1 - 7
----------------------------------------------
"free memory " 17 GB 55%
"SQLA " 10 GB 31%
"KGLH0 " 1261 MB 4%
"gcs resources " 801 MB 3%
"gcs shadows " 554 MB 2%
"kglsim object batch " 236 MB 1%
"KGLHD " 183 MB 1%
"db_block_hash_buckets " 178 MB 1%
"kglsim heap " 138 MB 0%
"ASH buffers " 128 MB 0%
"SQLP " 71 MB 0%
"KGLDA " 48 MB 0%
"gc name table " 48 MB 0%
"ges enqueues " 38 MB 0%
"ges resource " 38 MB 0%
"dbwriter coalesce buffer " 32 MB 0%
"gcs res hash bucket " 32 MB 0%
"dirty object counts array " 32 MB 0%
"ges big msg buffers " 30 MB 0%
"event statistics per sess " 29 MB 0%
TOTALS ---------------------------------------
Total free memory 17 GB
Total memory alloc. 14 GB
Grand total 31 GB
==============================================

==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 3
----------------------------------------------
"free memory " 15 GB 95%
"SQLA " 292 MB 2%
"KGLH0 " 219 MB 1%
"gcs resources " 114 MB 1%
"gcs shadows " 80 MB 0%
"KGLHD " 29 MB 0%
"db_block_hash_buckets " 25 MB 0%
"ASH buffers " 18 MB 0%
"SQLP " 12 MB 0%
"kglsim object batch " 9618 KB 0%
 -----------------------------------------
free memory 15 GB
memory alloc. 882 MB
Sub total 16 GB
==============================================

===============================
End 4031 Diagnostic Information
===============================

*** 2015-01-13 09:01:23.398
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","update sys.col_usage$ set ...","SQLA","tmp")

多次

*** 2015-01-13 09:01:48.416
SMON: following errors trapped and ignored:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","update sys.col_usage$ set ...","SQLA","tmp")
节点1日常没有做业务使用，share pool 不到4G，远未达上限，不过也出现了这个错误，2次。
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT NVL(SUM(FAILURES), 0)...","SQLA","tmp")

前面了解了一些share pool碎片的问题，不过还没有很深入。

请问大家这个问题是怎样引起的？如何进一步分析解决？

Liu Maclean(刘相兵 发表于 2015-1-16 22:34:35

awr和4031的trace 也上传下

页: [1]

Oracle数据库数据恢复、性能优化's Archiver

ORA-04031 问题请教，有大量free memory仍报