您好,登錄后才能下訂單哦!
enq: HW - contention 說明:
硬件隊列用于序列化段的高水位線以外的空間分配。
可以用手動分配范圍即可解決問題。
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.
巡檢發現問題:
select event,count(*) from gv$session_wait where wait_class <> 'Idle' group by event;
1 enq: HW - contention 274
識別發生問題的會話:
select sid,event,P2,P3 from gv$session_wait where event like '%HW%';
通過DBMS_UTILITY包,來定位發生HW爭用的對象。
SELECT DBMS_UTILITY.data_block_address_file(ID2) FILE#, DBMS_UTILITY.data_block_address_block(ID2) BLOCK# FROM GV$LOCK WHERE TYPE = 'HW';
通過上面查到的 文件號 和 塊ID 定位到等待的對象。
SELECT OWNER , SEGMENT_TYPE, SEGMENT_NAME FROM DBA_EXTENTS WHERE FILE_ID = 111 AND 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">
根據lob字段 查到相對應的表。
SELECT OWNER , TABLE_NAME , SEGMENT_NAME FROM DBA_LOBS WHERE SEGMENT_NAME = 'SYS_LOB0000080646C00004$$';
現在,已經查到是哪個LOB段造成的HW等待。應該向段中手動添加一個新的擴展區來解決問題。
添加一個該段中最大擴展區大小相同的擴展區。
查詢對象最大擴展區的大小。
SELECT DISTINCT BYTES FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'SYS_LOB0000080646C00004$$' AND OWNER ='USER'
向lob字段添加擴展區。
alter table USER.TAB modify lob ('COL_NAME') (allocate extents (size 64M));
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。