52 积分	0 好友	6 主题

发消息

[SQL调优] 11.2.0.2.0 数据库一个节点hang

1^#

发表于 2013-3-19 22:30:44 | 查看: 6009| 回复: 8

本帖最后由 xinxin415415 于 2013-3-19 22:49 编辑

os：redhat 5.5
db: oracle 11.2.0.2.0
19号的早上8点多hang

DISTRIB TRAN DDLDB.bbcc2fd.6365.29.1622
is local tran 6365.29.1622 (hex=18dd.1d.656))
delete pending committed tran, scn=20830620629 (hex=4.d99a0bd5)
Tue Mar 19 02:00:00 2013
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Tue Mar 19 02:00:19 2013
Auto-tuning: Starting background process GTX1
Starting background process GTX1
Tue Mar 19 02:00:19 2013
GTX1 started with pid=215, OS id=28043
Tue Mar 19 02:02:38 2013
Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc (incident=333947):
ORA-04030: out of process memory when trying to allocate 8528 bytes (pga heap,kgh stack)
Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333947/DDLDB2_m000_28045_i333947.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Mar 19 02:02:40 2013
Dumping diagnostic data in directory=[cdmp_20130319020240], requested by (instance=1, osid=24162 (ARC0)), summary=[incident=289003].
Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc (incident=333948):
ORA-04030: out of process memory when trying to allocate 8528 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 8528 bytes (pga heap,kgh stack)
Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333948/DDLDB2_m000_28045_i333948.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc (incident=333949):
ORA-04030: out of process memory when trying to allocate 123416 bytes (QERHJ hash-joi,kllcqas:kllsltba)
Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333949/DDLDB2_m000_28045_i333949.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Mar 19 02:02:43 2013
Sweep [inc][333949]: completed
Sweep [inc][333948]: completed
Sweep [inc][333947]: completed
Sweep [inc2][333948]: completed
Sweep [inc2][333947]: completed
Dumping diagnostic data in directory=[cdmp_20130319020243], requested by (instance=2, osid=28045 (M000)), summary=[incident=333949].
Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc (incident=333950):
ORA-04030: out of process memory when trying to allocate ORA-04030: out of process memory when trying to allocate 123416 bytes (QERHJ hash-joi,kllcqas:kllsltba)
bytes (,)
Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333950/DDLDB2_m000_28045_i333950.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Mar 19 02:03:43 2013
Sweep [inc][333950]: completed
Sweep [inc2][333950]: completed
Sweep [inc2][333949]: completed

复制代码

分享0

收藏0 回复只看该作者道具举报

xinxin415415

2^#

发表于 2013-3-19 22:35:17

alert见附件

alert.rar

76.4 KB, 下载次数: 1519

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2013-3-19 22:40:05

Tue Mar 19 02:02:40 2013
Dumping diagnostic data in directory=[cdmp_20130319020240], requested by (instance=1, osid=24162 (ARC0)), summary=[incident=289003].
Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc (incident=333948):
ORA-04030: out of process memory when trying to allocate 8528 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 8528 bytes (pga heap,kgh stack)
Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333948/DDLDB2_m000_28045_i333948.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc (incident=333949):
ORA-04030: out of process memory when trying to allocate 123416 bytes (QERHJ hash-joi,kllcqas:kllsltba)
Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333949/DDLDB2_m000_28045_i333949.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Mar 19 02:02:43 2013
Sweep [inc][333949]: completed
Sweep [inc][333948]: completed
Sweep [inc][333947]: completed
Sweep [inc2][333948]: completed
Sweep [inc2][333947]: completed
Dumping diagnostic data in directory=[cdmp_20130319020243], requested by (instance=2, osid=28045 (M000)), summary=[incident=333949].
Errors in file /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_m000_28045.trc (incident=333950):
ORA-04030: out of process memory when trying to allocate ORA-04030: out of process memory when trying to allocate 123416 bytes (QERHJ hash-joi,kllcqas:kllsltba)
bytes (,)
Incident details in: /u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_333950/DDLDB2_m000_28045_i333950.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Mar 19 02:03:43 2013
Sweep [inc][333950]: completed
Sweep [inc2][333950]: completed
Sweep [inc2][333949]: completed
Tue Mar 19 02:08:19 2013
DISTRIB TRAN DDLDB.bbcc2fd.6130.29.3340
is local tran 6130.29.3340 (hex=17f2.1d.d0c))
delete pending committed tran, scn=20830665088 (hex=4.d99ab980)
DISTRIB TRAN DDLDB.bbcc2fd.6172.33.3179
is local tran 6172.33.3179 (hex=181c.21.c6b))
delete pending committed tran, scn=20830665075 (hex=4.d99ab973)
Tue Mar 19 02:08:37 2013
DISTRIB TRAN DDLDB.bbcc2fd.6398.1.723
is local tran 6398.1.723 (hex=18fe.01.2d3))
delete pending committed tran, scn=20830663272 (hex=4.d99ab268)
DISTRIB TRAN DDLDB.bbcc2fd.6497.2.491

复制代码

现象 4030+大量DISTRIB TRAN 　分布式事务

/u01/app/oracle/diag/rdbms/ddldb/DDLDB2/trace/DDLDB2_dia0_23705.trc
/u01/app/oracle/diag/rdbms/ddldb/DDLDB2/incident/incdir_332292/DDLDB2_dia0_23705_i332292.trc

这2个TRACE打包上传，再弄一个快照时段或者附近的一个小时的AWR

回复只看该作者道具举报

xinxin415415

4^#

发表于 2013-3-19 22:42:40

trace见附件

23705.rar

248.5 KB, 下载次数: 1523

回复只看该作者道具举报

Maclean Liu(刘相兵

5^#

发表于 2013-3-19 22:57:44

There are resolvable hangs on your system.  Hang Manger will
attempt to resolve these hangs.  Some information about these
is output below.  Complete information is available in an
incident trace file on instance 1.

If the hang is to be resolved by terminating the session or
process that is the root or victim of the hang, additional
information will be output on the local instance of that
session or process.  Below are the hangs for which resolution
will be attempted.

*** 2013-03-19 08:13:42.142
Resolvable Hangs in the System
                  Root    Chain Total             Hang
Hang Hang       Inst Root  #hung #hung  Hang Hang  Resolution
   ID Type Status Num  Sess Sess  Sess  Conf Span  Action
  ----- ---- -------- ---- ----- ----- ----- ------ ------ -------------------
   54 HANG RSLNPEND 2  1423    2    2 HIGH  LOCAL Terminate Process
  Hang Resolution Reason: Although the number of affected sessions did not
justify automatic hang resolution initially, this previously ignored
hang was automatically resolved.

   inst# SessId  Ser#    OSPID Event
   ----- ------ ----- --------- -----
      2    26 891    92787 enq: WF - contention
      2 1423 165    21105 not in wait

Dumping process info of pid[59.2110] (sid:1423, ser#:165)
requested by master DIA0 process on instance 1.

*** 2013-03-19 08:13:42.143
Process diagnostic dump for oracle@crmrac2.tslcrm.com (M000), OS id=2110,
pid: 59, proc_ser: 30, sid: 1423, sess_ser: 165

os thread scheduling delay history: (sampling every 1.000000 secs)
  0.000000 secs at [ 08:13:41 ]
NOTE: scheduling delay has not been sampled for 0.232123 secs  0.000000 secs from [ 08:13:37 - 08:13:42 ], 5 sec avg
  0.000000 secs from [ 08:12:42 - 08:13:42 ], 1 min avg
  0.000000 secs from [ 08:08:42 - 08:13:42 ], 5 min avg
loadavg : 3.65 3.28 3.23
Memory (Avail / Total) = 44021.43M / 64446.93M
Swap (Avail / Total) = 64001.13M /  64001.13M
F S UID       PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY       TIME CMD
0 R oracle 2110    1 99  85 0 - 2161886 stext 07:00 ?    01:13:24 ora_m000_DDLDB2
Short stack dump:
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1900<-sspuser()+112<-__sighandler()<-kglic0()+1192<-kksIterCursorStat()+300<-kewrrtsq_rank_tops
ql()+240<-kewrbtsq_build_topsql()+128<-kewrftsq_flush_topsql()+679<-kewrft_flush_table()+397<-kewrftec_flush_table_ehdlcx()+752<-kewrf
at_flush_all_tables()+1406<-kewrfos_flush_onesnap()+170<-kewrfsc_flush_snapshot_c()+612<-kewrafs_auto_flush_slave()+757<-kebm_slave_ma
in()+586<-ksvrdp()+1779<-opirip()+674<-opidrv()+603<-sou2o()+103<-opimai_real()+266<-ssthrdmain()+252<-main()+201<-__libc_start_main()
+244<-_start()+36

[0004]: pga_aggregate_target=5412749312 ==> 5162MB

      2    26 891    92787 enq: WF - contention  ==> AWR Flush

   查一下，考虑先KILL 前台进程

   select * from dba_hist_wr_control;

回复只看该作者道具举报