ITPub博客

首页 > 数据库 > Oracle > pga分配了3G,而实际消耗却达到17G左右以上?

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

原创 Oracle 作者:wisdomone1 时间:2015-08-16 19:41:30 0 删除 编辑

分析结论

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博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-1773373/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb qq: 305076427 微博: wisdomone9

注册时间:2008-04-04

  • 博文量
    2149
  • 访问量
    11891915