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

enq: HW - contention Description:

Hardware queues are used to allocate space outside the high water mark of serialized segments.

The problem can be solved by manually assigning ranges.

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.

Inspection found problems:

select event,count(*) from gv$session_wait where wait_class 'Idle'group by event;

1 enq: HW - contention 274

Identify the session in question:

select sid,event,P2,P3 from gv$session_wait where event like '%HW%';

The DBMS_UTILITY package is used to locate objects for which HW contention occurs.

SELECT DBMS_UTILITY.data_block_address_file(ID2) FILE#, DBMS_UTILITY.data_block_address_block(ID2) BLOCK#FROM GV$LOCK WHERE TYPE = 'HW';

Locate the waiting object by looking up the file number and block ID.

SELECT OWNER , SEGMENT_TYPE, SEGMENT_NAMEFROM DBA_EXTENTSWHERE FILE_ID = 111AND 126423 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS -1 ;

DnSN+jK70gfP53Y1rnKR5yx+cn+oJUjtfBx8cIqu9RjGILHkIX7wugo85WGmwRnC+WHoePVRquCU9zG6DrOLrzmJrXJXPHGBaEB8jEHVogFdk/Edhy9Dza1+eBBfN3YfMY/iJ/dHaxk2n/LB6Vht9a3wrmG+wWsq8FIPcxyO+PSQYCuqxvkzZdSLn+Ukl86BrfpK55qI5cPO8sF1Gs+teYmD65C5XAMP4mesuOJDcwY/uT/UN+pV2Wd91D7yVbHMhYaYwb0VeNrDTIP1ACinPFvmMKhObXQei4Z41XW2X3y0no8xfnCFVw02GHkqmzlcwxiMeP00n/KV3ekzX8aRu/OhOYOf3B+vZYzjy3g4kBrrGBv0XMoTP3hdBf74w+wNZ2tZ451jDFa5Ml5jmPMIq4vv+avcK7xqsMFH5yEe1H1mnPrd7vSZL+PI2fnQnMFP7o/XkjEYdVSbuiqX2RlX5SLn4P4KbHuYs+Zpk3XpGX+WC302J/N4Ph+j61AvPvOh23xq4wczn3JqR0w3dt/KHBGjX5VDNWp3+syXceTrfGjO4Cf3J6slXGB8jLWmyqmd6dWvtuYb+5oKbH2YtbldIzPfKqdzqE18oPI/B//9gUa5ztaLHzriQWIZK+LL4tCpD7363I9P0TXkqXhiVdfZnT7zZRz58SniewQ/uT/U0OumvNrolFM7/N0YnyI5B/dXYPvDvH+Jz8voF/95K5mZswpMf7KqDPcOFZiHuenip1x8/VcQdlOW27imP7dpxSxkcwXmYW4K+ikXvynBrV3Tn1u3Zxb3jQr8epjDmN/UYM7AnIE5A/c4A/O//Sz+ZosDOt99KzD9uW9vZmXfq0Cc7XmYixrOxS8KcxN6+nOTRswytldgHuampHPxm+LcwDX9uUET3nAJ8R/As6/iM+13ueWHOVuUX4xM41yM+bF4xuARj/9q9P1dPd875o+edh89z3Tqy/xZfzQm5s3inPeYar1VLvRdHvVlefCTC7wbH+uq1oSPtYOVHj7QP3xX8eT1uZl3lSfPbjx8mKuFxkL8YvhmQqOc2upznk0672N0V6Hv76p53jlv1zP3nR17f6r4io+6dz7vi2vV774zY9Xe2c7q5TXQ9Xf6Tqe+K2zWHLmz/HBHSJ4r8PBhZlIWyTjw6GKERuPU1jyrfKXTXDtt39/O3J+Sq+rZCu8aH3t/3E+Nndex2ugrrLQrvGt0/Ip2VqPYR7UX1atGeWz1X2HrPFl+5ULLGCT+SvyjD7NuUjdVbdh6H2uOK2y/+FfM8e45q56t8K7xcdYf11BfeBA+MOPUj13pVnjX6PiV7Firrtdroz60yq3UW/VX2LqGLL9ylZZ9X4V//GHWjbKpKIT+jnj8V2N28a+e893y+yFnfyu8nolMX/Un08KBrAOsePyBlWaFD43+qrya62qbPek8cKxPfZWtMZWm4plHsdLu4qv1wsc8OlfH67p32k95mNkoG/EiZHylQXsFVhf/irneNWfVtxXeNT7u+uPaqG/Gad0f9Vdxyqvta1Hfp9laf+xn1cDn13Voz5wnbic+7WFe2agXwMc7C5Hl6i5+ph/u9wpUPVvhXePjo/643se/r7Z/vKv4Fd41On4HO/agv6jtyr6OerCS4xGNrlXjdd2rfLaH73J/9GE+u9FK/91Nr8YfXfzVPJ+s8x5qLdx3duz9ORsfazmK6dbb+Y7yqv/V7aoO1b6O6l7F7eKP1ss8R6h6dtuHD3Mszn8swi9G8JWWGPVnXPj51O44fLsx29/uOd49n/a76icarwU86P6sP2gD/cu40HQxmkN1WS71a5zP0cVWcXfnWZ/vjZrgV8TnMaHBp/qdPHl9buZd5cmzGw8f5m7C7GJ0+lfzvfv+Xq0fvt7pj1dkxjsq4I8yOSse/06ch7mp5lz8pjg3cF3VH/1XE/YNtjtL+KAKzMPcNPuqi99MOa4TFZj+nCjWSF+qAr8e5jDmNzWYMzBnYM7APc7Av3Iplva+CwTvAAAAAElFTkSuQmCC">

Find the corresponding table according to the lob field.

SELECT OWNER , TABLE_NAME , SEGMENT_NAME FROM DBA_LOBS WHERE SEGMENT_NAME = 'SYS_LOB0000080646C00004$$';

Now, we have found out which LOB segment caused HW wait. You should manually add a new extent to the segment to resolve the problem.

Add an extent of the same size as the largest extent in the segment.

Query the size of the object's maximum extent.

SELECT DISTINCT BYTES FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000080646C00004$$'AND OWNER ='USER'

Add extents to lob fields.

alter table USER.TAB modify lob ('COL_NAME') (allocate extents (size 64M));

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.