pga分配了3G，而实际消耗却达到17G左右以上？

分析结论

1,深入学习dba_hist_process_mem_summary及 v$process_memory,

  了解进程在内存使用方面一些细节，分析到底内存是如何消耗及使用的

  其相关重要列信息如下：

      allocated_sttdev表明标准方差，如果开始及结束差异过大，表明PGA主要由某些少量进程所消耗

      num_processes表明数据库的进程个数

      non_zero_allocs表明分配了PGA的进程个数，此值总小于等于num_processes

2，基于WINDOW平台的ORACLE PATCH叫BUNDLE PATCH，而LINUX或UNIX叫PSU，其实就是个标识，叫法不同而已

3，查阅基于WINDOWS平台相关BUNDLE PATCH的MOS文章为：

Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (文档 ID 1454618.1)

4，关于本文PGA消耗过大，可尝试应用最新的11.2.0.3的BUNDLE PATCH,即21104036

5，本文报ORA-04030错误相关的TRC文件会显示消耗PGA的TOP10及所有进程信息，且会详细显示最消耗PGA物理内存的用户进程的具体信息
    （主要包括：进程的会话信息，会话运行的SQL及相关信息等）

6，PGA即使通过参数PGA_AGGREGATE_TARGET，在实际使用仍可能会超过其指定的值，不过这种情况很少

分析过程

1， oracle alert告警日志发现ora-04030，进而无法SPAWN数据库进程，导致RAC本实例被重启

Archived Log entry 205568 added for thread 1 sequence 32767 ID 0x59b04886 dest 1:

Thu Aug 06 02:43:20 2015

Warning: VKTM detected a time drift.

Thu Aug 06 02:40:07 2015

Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\wjsf\wjsf1\trace\wjsf1_ora_14376.trc (incident=581055):

ORA-04030: 嘗試配置 8528 個位元組時, 發生處理作業記憶體不足 (pga heap, kgh stack)

ORA-01403: 找不到資料

Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.

Incident details in: E:\APP\ADMINISTRATOR\diag\rdbms\wjsf\wjsf1\incident\incdir_581055\wjsf1_ora_14376_i581055.trc

Use ADRCI or Support Workbench to package the incident.

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

Thu Aug 06 02:40:07 2015

Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\wjsf\wjsf1\trace\wjsf1_ora_12732.trc (incident=582199):

ORA-04030: 嘗試配置 64544 個位元組時, 發生處理作業記憶體不足 (sort subheap, sort key)

Incident details in: E:\APP\ADMINISTRATOR\diag\rdbms\wjsf\wjsf1\incident\incdir_582199\wjsf1_ora_12732_i582199.trc

Use ADRCI or Support Workbench to package the incident.

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

Thu Aug 06 02:43:45 2015

Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\wjsf\wjsf1\trace\wjsf1_ora_12212.trc (incident=581527):

ORA-04030: 嘗試配置 64 個位元組時, 發生處理作業記憶體不足 (callheap, bind grad array)

ORA-01403: 找不到資料

Thu Aug 06 02:43:47 2015

minact-scn master exiting with err:12751

Incident details in: E:\APP\ADMINISTRATOR\diag\rdbms\wjsf\wjsf1\incident\incdir_581527\wjsf1_ora_12212_i581527.trc

Use ADRCI or Support Workbench to package the incident.

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

Thu Aug 06 02:43:59 2015

Dumping diagnostic data in directory=[cdmp_20150806024359], requested by (instance=1, osid=12732), summary=[incident=582199].

Thu Aug 06 02:44:04 2015

Error occured while spawning process O000; error = 3135

Thu Aug 06 02:44:16 2015

Error occured while spawning process O000; error = 3135

Thu Aug 06 02:44:24 2015

Received an instance abort message from instance 3

Thu Aug 06 02:44:24 2015

Received an instance abort message from instance 3

Please check instance 3 alert and LMON trace files for detail.

Thu Aug 06 02:44:30 2015

Error occured while spawning process O000; error = 3135

Please check instance 3 alert and LMON trace files for detail.

LMS0 (ospid: 5452): terminating the instance due to error 481

Thu Aug 06 02:44:37 2015

opiodr aborting process unknown ospid (14556) as a result of ORA-1092

Thu Aug 06 02:44:36 2015

opiodr aborting process unknown ospid (2216) as a result of ORA-1092

Thu Aug 06 02:44:41 2015

opiodr aborting process unknown ospid (14012) as a result of ORA-1092

Thu Aug 06 02:44:41 2015

Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\wjsf\wjsf1\trace\wjsf1_lmon_14676.trc (incident=579559):

ORA-29740: evicted by instance number 3, group incarnation 68

Thu Aug 06 02:44:42 2015

opiodr aborting process unknown ospid (14468) as a result of ORA-1092

Thu Aug 06 02:44:43 2015

opiodr aborting process unknown ospid (1784) as a result of ORA-1092

Incident details in: E:\APP\ADMINISTRATOR\diag\rdbms\wjsf\wjsf1\incident\incdir_579559\wjsf1_lmon_14676_i579559.trc

Thu Aug 06 02:44:44 2015

ORA-1092 : opitsk aborting process

Thu Aug 06 02:44:44 2015

opiodr aborting process unknown ospid (10008) as a result of ORA-1092

Thu Aug 06 02:44:44 2015

ORA-1092 : opitsk aborting process

Thu Aug 06 02:45:23 2015

Starting ORACLE instance (normal)

2， 查看上述告警日志中的E:\APP\ADMINISTRATOR\diag\rdbms\wjsf\wjsf1\trace\wjsf1_ora_12732.trc的内容

Incident 582199 created, dump file: E:\APP\ADMINISTRATOR\diag\rdbms\wjsf\wjsf1\incident\incdir_582199\wjsf1_ora_12732_i582199.trc

ORA-04030: 嘗試配置 64544 個位元組時, 發生處理作業記憶體不足 (sort subheap, sort key)

继续查看上述引用的E:\APP\ADMINISTRATOR\diag\rdbms\wjsf\wjsf1\incident\incdir_582199\wjsf1_ora_12732_i582199.trc的内容

   消耗PGA最高的前10个用户进程信息，前2位198及257 这2个进程各消耗内存各为10G及8G左右

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

Top 10 processes:

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

(percentage is of 20 GB total allocated memory)

47% pid 198: 9257 MB used of 10 GB allocated

42% pid 257: 8301 MB used of 8839 MB allocated

0% pid 38: 41 MB used of 45 MB allocated (1088 KB freeable)

0% pid 40: 41 MB used of 45 MB allocated (1088 KB freeable)

0% pid 43: 41 MB used of 45 MB allocated (1088 KB freeable)

0% pid 44: 41 MB used of 45 MB allocated (1088 KB freeable)

0% pid 45: 41 MB used of 45 MB allocated (1088 KB freeable)

0% pid 46: 41 MB used of 45 MB allocated (1088 KB freeable)

0% pid 47: 41 MB used of 45 MB allocated (1088 KB freeable)

0% pid 48: 41 MB used of 45 MB allocated (1088 KB freeable)

   继续看198及257 2个进程到底在作什么，为何消耗这么多的内存

TRC文件列出为所有消耗PGA内存的所有进程的信息，而且消耗内存明显的198及257会显示更多详细信息

用于进一步分析

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

All processes:

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

(session detail when over 1043 MB allocated)

中间略（略去其它进程的信息，因为它们消耗内存很低）

198号进程没有运行任何SQL

pid 198: 9257 MB used of 10 GB allocated

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

Begin session detail for pid 198

sid: 487 ser: 1897 audsid: 1612851028 user: 76/WJ_ADP

flags: (0x100041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

flags2: (0x40009) -/-/INC

pid: 198 O/S info: user: SYSTEM, term: CNWJNDBMES01, ospid: 14376

image: ORACLE.EXE (SHAD)

client details:

O/S info: user: Administrator, term: MPA-BISCAN01, ospid: 448:452

machine: FACTORYWJ\MPA-BISCAN01 program: OffLine.exe

application name: OffLine.exe, hash value=2542059239

current SQL: <none>

End session detail for pid 198

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

257进程为INSERT语句

pid 257: 8301 MB used of 8839 MB allocated

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

Begin session detail for pid 257

sid: 1385 ser: 34259 audsid: 1612849958 user: 76/WJ_ADP

flags: (0x100041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

flags2: (0x40009) -/-/INC

pid: 257 O/S info: user: SYSTEM, term: CNWJNDBMES01, ospid: 12212

image: ORACLE.EXE (SHAD)

client details:

O/S info: user: Administrator, term: MPA-1100-BI08, ospid: 3832:3836

machine: FACTORYWJ\MPA-1100-BI08 program: OffLine.exe

application name: OffLine.exe, hash value=2542059239

current SQL:

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

PRIVATE MEMORY USAGE FOR LARGEST PROCESS

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

Begin memory detail for largest PGA user, pid 198

*** 2015-08-06 02:43:46.664

dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)

----- Current SQL Statement for this session (sql_id=8yavtbcdfscwt) -----

SELECT MO_NUMBER,MODEL_NAME,OP_CODE,MO_BASE,QTY_PCB_UNIT,TARGET_QTY, OUTPUT_QTY,TOTAL_SCRAP_QTY,MO_START_DATE,MO_TARGET_DATE, MO_CREATE_DATE,OWNER FROM R_MO_SMT_T ORDER BY MO_START_DATE DESC

INSERT INTO R_STATION_REC_T(WORK_DATE,WORK_SECTION,MO_NUMBER,

LINE_NAME,GROUP_NAME,SECTION_NAME,WIP_QTY,PASS_QTY,FAIL_QTY,

REPASS_QTY,REFAIL_QTY,LAST_FLAG,MODEL_NAME)

VALUES(:MO_DATE,:W_SECTION,:MO,:LINE,:MYGROUP,:SECTION,0

,0,0,1,0,'0',:C_MODEL)

End session detail for pid 257

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

3，按理说一个进程运行SELECT查询，却消耗了10G内存，而另一个进程仅运行一个非常简单的INSERT，奇怪却消耗8G内存，有些诧异？

数据库版本11.2.0.3，RAC，WINDOWS平台


4，pga分配了3G，而实际消耗却达到17G左右？




5，我们再看看PGA内存的具体使用情况

由上可知开始OTHER分类分配了PGA 2656MB左右，而结束升高17441MB左右，且平均设备偏差变化极大（由27M变成476MB），可见是个别进程大量消耗了PGA内存

从这儿看好像是PGA分配内存的BUG

解决方案：

1，增加PGA大小

2，应用基于WINDOWS的11.2.0.3最新的BUNDLE PATCH

经查阅MOS，发现如下文章

11.2.0.x Oracle Database and Networking Patches for Microsoft Platforms (文档 ID 1114533.1)

目前最新的BUNDLE PATCH为

11.2.0.3.0 Patch 36 (11.2.0.3.36P) 32-Bit Patch:20233167 64-Bit (x64) Patch:20233168 : OJVM 11.2.0.3.2 Patch:20227195

因为我们是WINDOWS 64BIT,相关BUNDLE PATCH的信息如下：

从描述可知，可用超集BUNDLE PATCH 20420395取代本BUNDLE PATCH

而最新的替代PATCH是21104036,所以我们直接查看21104036即可

PATCH 21104036的内容如下：

同时参考查阅关于WINDOWS平台的BUNDLE PATCH最新信息，和上面一致，即最新的BUNDLE PATCH为21104036

Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (文档 ID 1454618.1)

个人简介

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。

服务过的客户：

中国电信

中国移动

中国联通

中国电通

国家电网

四川达州商业银行

湖南老百姓大药房

山西省公安厅

中国邮政

北京302医院     

河北廊坊新奥集团公司

 项目经验:

中国电信3G项目AAA系统数据库部署及优化
      中国联通4G数据库性能分析与优化

中国联通CRM数据库性能优化

中国移动10086电商平台数据库部署及优化

湖南老百姓大药房ERR数据库sql优化项目

四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化

四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化

北京高铁信号监控系统RAC数据库部署及优化

河南宇通客车数据库性能优化

中国电信电商平台核心采购模块表模型设计及优化

中国邮政储蓄系统数据库性能优化及sql优化

北京302医院数据库迁移实施

河北廊坊新奥data guard部署及优化

山西公安厅身份证审计数据库系统故障评估

国家电网上海灾备项目4 node rac+adg 
       贵州移动crm及客服数据库性能优化项目
       贵州移动crm及客服务数据库sql审核项目
       深圳穆迪软件有限公司数据库性能优化项目

联系方式：

手机：18201115468

qq   :   305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900    

itpub博客名称：wisdomone1    http://blog.itpub.net/9240380/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1773373/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1773373/