重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: 分区压缩表上区段分配缓慢导致enq: TX - contention和L1 validation等待事件 [打印本页]

作者: 刘泽宇 时间: 5 天前
标题: 分区压缩表上区段分配缓慢导致enq: TX - contention和L1 validation等待事件
现象：
分区压缩表上的区段分配缓慢导致性能变差并发生enq: tx - contention和L1 validation等待事件
当它发生时，大多数PX Secondary等待“TX - contention”并且阻塞者的堆栈（PX secondary as well）包含ktsp_bump_hwm，这意味着我们正在尝试扩展该段以获得新空间

例如：
short stack: ksedsts()+346<-ksdxfstk()+71<-ksdxcb()+912<-sspuser()+217<-__sighandler()<-ktfbbssearch()+2253<-ktfbfsearch()+1312<-ktfbtgex()+1839<-ktsxs_add()+2110<-ktspnr_next()+1446<-ktrsexec()+670<-ktspbmphwm()+928<-ktspmvhwm()+155<-ktsp_bump_hwm()+265<-ktspgsp_main()+3040<-kdtgsp()+939<-kdtgsph()+3012<-kdtgrs()+393<-kdtInsRow()+592<-kauMovRow()+1793<-kauupd()+21893<-updrow()+1826<-qerusEvalUpdWhere()+164<-qerusRop()+1530<-qertqiFetch()+962<-qerusFetch()+704<-qertqoFetch()+1271<-qerpxSlaveFetch()+1717<-qerpxFetch()+12994<-up
10046跟踪文件显示如下：

*** 2018-09-17T17:28:26.248941+02:00
WAIT #140264763153528: nam='latch free' ela= 16 address=31446131200 number=172 why=0 obj#=6694052 tim=8495087417348
WAIT #140264763153528: nam='enq: TX - contention' ela= 148638 name|mode=1415053316 usn<<16 | slot=23199771 sequence=387130 obj#=6694052 tim=8495087417466
WAIT #140264763153528: nam='latch: ges resource hash list' ela= 1379 address=31453792280 number=179 why=0 obj#=6694052 tim=8495087418928
WAIT #140264763153528: nam='enq: TX - contention' ela= 200096 name|mode=1415053316 usn<<16 | slot=131098 sequence=1697391 obj#=6694052 tim=8495087619158
WAIT #140264763153528: nam='latch: ges resource hash list' ela= 410 address=31453467680 number=179 why=0 obj#=6694052 tim=8495087619789
WAIT #140264763153528: nam='latch: cache buffers chains' ela= 1561 address=2449361488 number=289 why=53540881 obj#=6694052 tim=8495087621393
WAIT #140264763153528: nam='enq: TX - contention' ela= 190485 name|mode=1415053316 usn<<16 | slot=22675480 sequence=441489 obj#=6694052 tim=8495087811943
WAIT #140264763153528: nam='latch: ges resource hash list' ela= 571 address=31468272312 number=179 why=0 obj#=6694052 tim=8495087812622
WAIT #140264763153528: nam='L1 validation' ela= 5 seghdr=16 l1bmb=53540881 p3=0 obj#=6694052 tim=8495087812752
WAIT #140264763153528: nam='L1 validation' ela= 10070 seghdr=16 l1bmb=53540881 p3=0 obj#=6694052 tim=8495087822910
WAIT #140264763153528: nam='enq: TX - contention' ela= 174628 name|mode=1415053316 usn<<16 | slot=22413335 sequence=373979 obj#=6694052 tim=8495087997617
WAIT #140264763153528: nam='latch: ges resource hash list' ela= 808 address=31450784680 number=179 why=0 obj#=6694052 tim=8495087998558
WAIT #140264763153528: nam='L1 validation' ela= 3 seghdr=16 l1bmb=53540881 p3=0 obj#=6694052 tim=8495087998669
WAIT #140264763153528: nam='L1 validation' ela= 10080 seghdr=16 l1bmb=53540881 p3=0 obj#=6694052 tim=8495088008836
WAIT #140264763153528: nam='latch free' ela= 15 address=31446131200 number=172 why=0 obj#=6694052 tim=8495088174418
WAIT #140264763153528: nam='enq: TX - contention' ela= 165551 name|mode=1415053316 usn<<16 | slot=23199747 sequence=389612 obj#=6694052 tim=8495088174480
WAIT #140264763153528: nam='latch: ges resource hash list' ela= 1041 address=31450736280 number=179 why=0 obj#=6694052 tim=8495088175557
WAIT #140264763153528: nam='L1 validation' ela= 4 seghdr=16 l1bmb=53540881 p3=0 obj#=6694052 tim=8495088175661
WAIT #140264763153528: nam='L1 validation' ela= 10068 seghdr=16 l1bmb=53540881 p3=0 obj#=6694052 tim=8495088185796

解决方案:
应用补丁27986817

原因:
这个问题是由于
BUG 27986817 - L1 VALIDATION WAITS WITH HIGH CONCURRENT WORKLAOD

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)