一、今天在查看awr報(bào)告中，發(fā)現(xiàn)Top 5 Timed Foreground Events發(fā)現(xiàn)enq: HW - contention的等待事件；

創(chuàng)新互聯(lián)是一家專注網(wǎng)站建設(shè)、網(wǎng)絡(luò)營銷策劃、微信平臺(tái)小程序開發(fā)、電子商務(wù)建設(shè)、網(wǎng)絡(luò)推廣、移動(dòng)互聯(lián)開發(fā)、研究、服務(wù)為一體的技術(shù)型公司。公司成立10多年以來，已經(jīng)為上1000家成都VR全景各業(yè)的企業(yè)公司提供互聯(lián)網(wǎng)服務(wù)。現(xiàn)在，服務(wù)的上1000家客戶與我們一路同行，見證我們的成長；未來，我們一起分享成功的喜悅。

 cDNba0aAdouWi7sm73h2WV1B7XVqiQvCVVnALfM8GqZVWVarsd9SNfJvmJW+I5NU7Anrbupfdu3C2K/Nr3cC61TVvR75TAuUxmtG1zd1NWtL6fwfletmEVj3PMmqvZPEWTr2G+RjtfmYsPxZo14Yt+s9CKb3nT3EcIjKrLxI7vQMb1PTq7o98pJXN7IOsVhuAo18Xtad1yWGrFALHNRXGp3xzuVDaiSBC7hwijvMsXwUsUUo+ybpWnX5GNPxB3vVmRRMXcXRCaYp8gcy8kV/V49HpfBmhM/N9MANQceHScJDuF+md3TndTMYIuhQlXEuFxu8Zto4LhcR7L6GmGbzO65TmpmsMVQoSpiXC63+PU0ApCkD69SoSpiXC63+PU0ApCkD69SoSpiXC63+PU0ApCkD69SoSrK4vKHmAQnfjqhgXEEyQ2I4UMMFSnUuCQIgiBKMC4JgiBMkH92SYzDiZ9OaGAcQXIDYvgQQ0UK9Gb8+/1e1z9sg82Jn05oBCBJH16lQlPEuFxuMWkEIEkfXqVCU8S4XG4xaQQgSR9epUJTxLhcbjFpBCBJH16lQlMkx+UnwT3t8xfi0tmj5fhsorbO6c1J0aygAQoA1AzoceLV4+0RH8TdrJ5ucMDj7aYpLkvvqp6Ws3AP2JjTm5OimU5DLAztIUuPH68eb/t90I45iMtqT7zdbIvLy5Z02suNpkXCNCdFs5QG2Dvjk6Urrx5vHuISbJMlKx9RsXNf2uIyQ9U7xmXIuMQvQMS3I2UJefPq8eb8zbjlnfhTKrbty/JXl6XF1VkBmpOi8fzqkm/Gs+bh2WV5XZ2Y9cTbzWfejNtnBWhOioZxeVBjXPpsz/yqx7hIjOakaPirnoOa87h87W5u/SARGBM1Ky83RbPng0Tpdms1A3qcePV4c/uzy6b9jbeb/Jj6VovD0xh82nPi1eMthg8xVGiKGJfLLQ5Pg3E5pcXwIYYKTRHjcrnFpBGAJH14lQpNEeNyucWkEYAkfXiVCk1RHpc/xCQ48dMJDYwjSG5ADB9iqEihxiVBEARRgnFJEARhgvyzS2IcTvx0QgPjCJIbEMOHGCpSoDfj3+93XTy/B078dEID4wiSGxDDhxgqUjAul8OJn05oYBxBcgNi+BBDRQrG5XI48dMJDYwjSG5ADB9iqEjBuFwOJ346oYFxBMkNiOFDDBUp9sXl5/PJvi17wLeLaBgHj5CZ7mfybxnUe+7OzbXbRPLuCXDAxDrHqstZHnz4/IU2phx/94gq8IKYzzUvFvqOtpe4LL1wFZcjmOun0bSS+c4TaCEp0vYQEyMQqzq91jbLYVzeAFkp1hvYze4DPj0N+hY0xWX2hCA+59zfak8C4tNpZndr5FefujOq2qPVwcYpItaVvnYCRTJPnUAQE2WPq5hoBVaKz8VxcWk5sJkK7YxUzxQ4iSWTrEdbpO9o1+MSb7mmAZiicS2/xWgihm9hHGyZUmJ1XF7tOz2XBqAH3M5G/l64iok+aEqzg5qZkH3rxwdwnC+pAkHJiUsZzxQICnA2LYe96Wib4lLLOLw08CWbiBfUAE6dZeUmXQ7j8pQkqhr1bKZPRzUuqyMvTz7gjMNFuCEujT1XEegL47JPIZiVTbRLBQsaiYkbXJ3uLS6Np+5SdnoWDQsOjYluRIpLcJarLzUuT3E562hvfTMufotjC6DPMvG+xsFO4hI8bdhrdylaz8ANJzExgjfEZTnAWHLdMaedxDKvcYKL4+00On/VU+VRDcHxuCyJWahmjxoH26eUmP6b8RQiK7HH2weJRCFOYmIEZZ1Xt6ac5cQH+ym+zCXXcWa1C/FsXsnhzahmj3Yc7fkfJCotfjmclL4TGhhHkNyAGD7sUbEzcBiXy+Gk9J3QwDiC5AbE8GHpZ0LEF+mrwf8Jcjmc+OmEBsYRJDcghg8xVKRgXC6HEz+d0MA4guQGxPAhhooUKC5/iElw4qcTGhhHkNyAGD7EUJFCjUuCIAiiBOOSIAjCBPlnl8Q4nPjphAbGESQ3IIYPMVSkQG/Gv1H+mLqfP+VOGgFI0odXqdAUMS6XW0waAUjSh2ep0BQxLpdbTBoBSNKHV6nQFDEul1tMGgFI0odXqdAU1ePy8/ncX1vb4Ky+6d38p9yutPhe/N//f+sfe0/Wnz5qmb65dvs0xjtgdrvSb534UFZa+RDoyVRkiwwesezWGoe5rScux+V1zJpowbNxWW6zpcfoDJi+8wR2a3QSE5tbecKd+FCtvatlN1fHpeXUDDZTXN7bqX29x2jPNmLqpw9VZ4mDNeNKE7MF8dMRHgOWEhl2vFq3bLzY7zCJNEoifycx8aw/TnwAiWOMKmNc4ujQLMKPYv7drR6XmaqUX8YY2yfqAZqzHu12wDiNJ6CtrSkuWL2RWDSinGrFVEsBb4dGY0MDO1tq9BATjzSfcakVoeW8lyrEI2aMDsDBaVxmzMSzXUYJYC9arK0Dok0khvNOo435aCuDiimLpq9ijKWg2YhprGuWXUivPcTEI81hXAJ6lpNbqsBHLO2vhskFT5/IeUqbFpdVrpa4xNFWXaojLqv1AWg/FZcW2mXP/hOoqWBcYq8c+mCJy7Kn6WeXlhMHxruLyyzsWw+DqEfTXD6x4B2q8tHu0rR52vS7X5w1/Vc91dr1kERHkPTTHMal5XRcLbspLtIXHfi+2pTxNvqrnpRfmhdlT/Zouk45q3x0blxmrKpRLkZhGZeizFkfJCrv2zR982/GU/gk6apVa+YpVqAIO3azrArLUmXBX38PHVhqbnvyY+rrVD3SNDmz/By0y8kJDECSPrxKhaaIcblEDuMyGEn68CoVmqLdcfmS5sRPJzQCkKQPr1KhKWJcLreYNAKQpA+vUqEpyuPyh6gEJ346oYFxBMkNiOFDDBUp1LgkCIIgSjAuCYIgTJB/dkmMw4mfTmhgHEFyA2L4EENFCvRm/Bvlj6k/Cyd+OqGBcQTJDYjhQwwVKRiXy+HETyc0MI4guQExfIihIgXjcjmc+OmEBsYRJDcghg8xVKRgXC6HEz+d0MA4guQGxPAhhooUbXH5+XzurwDpsOrgnbjJaKwmsr2XqhZN8Q9S5J3a+Kae/bWbMfdJ0gPc+qBV4P0oHpypKMcPciuvMeFxzI/LuabMhTHo597LXvpiBVTHWHqaaExBmYwOSXqAWx9wNVb399oel5bjMwhTXN7WZF/xC59MAwh+y+sObVlwi08CkZs4JSOGJVR5in5q0GiIY8RhTpKopIEfdRUTD8KtD/hcaNfGuKweK7Gc8KNizzjqcZmpSi8wy2q6ld9qg0UO9lniOtUpQKmd52UufbFccBGIMZ2tcPc8/mY862dclnDog1iE90P42hKXxmNlzBmN3iw0xKXIzB6XOAK0sGj11DgryzVxSrlCKaF6x9JPDZaiFG9aDh7hMRGiIs3S3x4/MbEfqTPefKhu5WXYTe1wNR1/cbo4RjtQI9gXl1UqZZaJd6lGoWWWcRGjUrDgZSt94E/TQ87jsvqU4C0mtiFzxpsPlrgs+5t+dmk8/tp4d3GZhr1FmxaX1UjCMVftMa5TXQTQtrO6uuJyll3p9eNxadHoLSb2oDxEHnzArxXAgKa47HslBAYDwuOY/KuelKiYINqU7CGtp1wQ3EL0HS9SPeEdrPpeXYr3wjRwz7Nx+fkLPyQfh+iMEx9AEV6G06ElSWv1lqfj0g9sttRcnPcx9XVeLMIsPweFO9/WXxxBcgNi+BBDRQrG5XIwLu04guQGxPAhhooU58XlcXDipxMaGEeQ3IAYPsRQkYJxuRxO/HRCA+MIkhsQw4cYKlKguPwhJsGJn05oYBxBcgNi+BBDRQo1LgmCIIgSjEuCIAgT5J9dEuNw4qcTGhhHkNyAGD7EUJECvRn/Rvlj6n7+lDtpBCBJH16lQlPEuFxuMWkEIEkfXqVCU8S4XG4xaQQgSR9epUJTxLhcbjFpBCBJH16lQlNUj8vP53N/bW2Ds/qmL2LVbbF23+SfBlB7yodSc8TBac+ztVv8QxIy7XgHzO5P+q0fH6oVWFaypgKMHCRWPQizWk9cTvTdPitSXJbJmF1rj4o9YCmHSSRu6H3thOR+T3zGZTV6xGd9TcXquMSnZkozxeXtgvb1HlMe13uY9kRknCUOFs3CTERFS5tYNNp9m8JRqxvxLn5OIKDthOR+QxzGpeWA4MwyxmX1wIpM8KNV5n2tHpeZqpQfKH3cky1omaXdboSJGL5LLQZ7WZLRnhvEYsqufcalSDW99kDycWdc+QAOiJhfrXFpPLAgDZ3GZcZMfDbIIhWz1865uI520qpMwJqLPAUWV+/btOuW5wNMY3NjXFqcceWDMS4tJacd2ywNLWFS3vGwuKxytcSl8eTjuOy4y9L2SFyWzxmPn0DLrj1O8ql2XFyWu1ktOVAAl3LSAQfvcZmFPX6lgHuyBcsx5RNLR1xa1vQQl02B3j348RPIuLSb48cH+/N62T/xzbh3I3yOprfRX/Wk/LL0yXrKbLrXKWeVjxpDATBJR4rLLmqzPkiUkceDy7s8fgJFehntx0k+1U6Jy7QIq+O1JLGUehYd2o2q52hue/Jj6huiykOb5eegXX5O4Okk6cOrVGiKGJfLLX7QriNq9wiS9OFVKjRFu+PyJc2Jn05oBCBJH16lQlPEuFxuMWkEIEkfXqVCU5TH5Q8xCU78dEID4wiSGxDDhxgqUqhxSRAEQZRgXBIEQZjwX1x+CYIgCIjPs4FNEARxChiXBEEQJjAuCYIgTPg/1yU9avBPuhwAAAAASUVORK5CYII=">

二、enq: HW - contention的官方說明：

The HW enqueue is used to serialize the allocation of space beyond the high water mark of a segment.

?V$SESSION_WAIT.P2 / V$LOCK.ID1 is the tablespace number.

?V$SESSION_WAIT.P3 / V$LOCK.ID2 is the relative data block address (dba) of segment header of the object for which space is being allocated.

If this is a point of contention for an object, then manual allocation of extents solves the problem.

 

三、等待事件解釋

為防止多個(gè)進(jìn)程同時(shí)修改HWM而提供的鎖稱為HW鎖。想要移動(dòng)HWM的進(jìn)程必須獲得HW鎖。若在獲取HW鎖過程中發(fā)生爭(zhēng)用，則等待enq: HW - contention事件。HW鎖爭(zhēng)用大部分是因大量執(zhí)行insert所引發(fā)的，偶爾也會(huì)因大量執(zhí)行update在回滾段中發(fā)生HW鎖爭(zhēng)用現(xiàn)象。若是update，表中段的擴(kuò)展的大小雖然不多，但在創(chuàng)建回滾數(shù)據(jù)的過程中，需要回滾段的急速擴(kuò)張。HW鎖爭(zhēng)用是在急速空間擴(kuò)張時(shí)普遍出現(xiàn)的等待現(xiàn)象，有時(shí)也會(huì)引發(fā)嚴(yán)重的性能下降。

 

SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'enq: HW - contention';

EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3

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

250 enq: HW - contention name|mode table space # block

眾所周知，Oracle高水位線標(biāo)志著該線以下的block均被Oracle格式過，通俗一點(diǎn)講就是該高水位線以下的block都被Oracle使用過。 通常在執(zhí)行insert操作時(shí)，當(dāng)高水位線以下block不夠用時(shí)，Oracle將會(huì)推進(jìn)高水位線。更進(jìn)一步講，當(dāng)有多個(gè)進(jìn)程在同時(shí)進(jìn)行insert操作時(shí)，比較容易引起高水位線爭(zhēng)用，主要表現(xiàn)為enq: HW - contention。

 

四、如何找到事件：'enq: HW - contention' 熱點(diǎn)對(duì)象：

1、查看v$session_wait，應(yīng)該會(huì)有如下等待事件：

SQL> select p1, p2, p3 from v$session_wait where event = 'enq: HW - contention';

P1 P2 P3

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

1213661190 7 140003563

1213661190 7 140003563

1213661190 7 140003563

1213661190 7 140003563

1213661190 7 140003563

1213661190 7 140003563

1213661190 7 140003563

 

2、通過P3進(jìn)行DBMS_UTILITY轉(zhuǎn)換可以獲知發(fā)生爭(zhēng)用的文件和block：

SQL> select dbms_utility.data_block_address_block(140003563),dbms_utility.data_block_address_file(140003563) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(140003563) DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(140003563)

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

1591531 33

 

3、進(jìn)而通過file#和block#定位對(duì)象：

SQL> select owner, segment_type, segment_name

from dba_extents

where file_id = 33 and 1591531 between block_id and block_id + blocks - 1;

 

五、減少HW鎖爭(zhēng)用的方法如下：

If this is a point of contention for an object, then manual allocation of extents solves the problem.

腳本：alter table <TABNAME> allocate extent，提前分配空間；

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

本文作者：JOHN

ORACLE技術(shù)博客：ORACLE 獵人筆記               數(shù)據(jù)庫技術(shù)群：367875324 （請(qǐng)備注ORACLE管理 ）  

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

文章題目：等待事件enq:HW–contention說明及解決方法
分享鏈接：http://www.rwnh.cn/article0/gjhgoo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、服務(wù)器托管、云服務(wù)器、標(biāo)簽優(yōu)化、Google、定制網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)