Shulou(Shulou.com)06/01 Report--

1. In viewing the awr report today, we found that Top 5 Timed Foreground Events found a waiting event for enq: HW-contention.

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

2. Official statement of 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.

Waiting for the event to be explained

The locks provided to prevent multiple processes from modifying HWM at the same time are called HW locks. A process that wants to move a HWM must acquire a HW lock. If contention occurs during the acquisition of a HW lock, wait for the enq: HW-contention event. Most of the HW lock contention is caused by the massive execution of insert, and occasionally the HW lock contention occurs in the rollback segment due to the massive execution of update. In the case of update, the size of the extension in the middle of the table is small, but the rapid expansion of the rollback segment is needed in the process of creating rollback data. HW lock contention is a common waiting phenomenon in rapid space expansion, and sometimes leads to serious performance degradation.

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

EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3

Enq: HW-contention name | mode table space # block

As we all know, the Oracle high water mark marks that the block below the line has been formatted by Oracle, and generally speaking, the block below the high water mark has been used by Oracle. Usually when performing an insert operation, when there is not enough block below the high water mark, the Oracle will push the high water mark. Furthermore, when multiple processes are performing insert operations at the same time, it is easy to cause high water mark contention, mainly as enq: HW-contention.

4. How to find the event: 'enq: HW-contention' hotspot object:

1. When you view the v$session_wait, there should be the following waiting events:

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. Through DBMS_UTILITY conversion through P3, you can learn about the contended files and 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. Then locate the object through file# and block#:

SQL > select owner, segment_type, segment_name

From dba_extents

Where file_id = 33 and 1591531 between block_id and block_id + blocks-1

5. The methods to reduce the contention for HW locks are as follows:

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

Script: alter table allocate extent, allocate space in advance

+

Author: JOHN

ORACLE technology blog: ORACLE hunter's note database technology group: 367875324 (please note ORACLE management)

+

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.