enqueue HW wait 引起表空間突然大量擴充套件

我們在10.2.0.4上遇到過好幾次了，enqueue HW wait不僅堵塞了事務，表空間也一下被用了很多，後來每次都預留幾十GB的空間，但事務還是被堵塞，下面說說這個WAIT。

在高併發的oltp資料庫，平時150多的active session，資料庫的總session數會經常波動，可能從1000波動到1500或2000，幾分鐘後又回到1000左右。

一個表同時有大量的insert或update時，會有enq HW wait。因為insert/update會請求空間，而如果HWM下的free list 用完，沒有剩餘的資料塊時，請求段內的空資料塊，高水位往上移。而每個請求都需要HW enqeue。出現排隊，此時如果高水位之上沒有空餘空間了，會出現段空間擴充套件。等待段空間擴充套件會使佇列更長。

HW enqueue在非ASSM和ASSM都會出現，在我們的一個ASSM上的表，出現過段空間突然擴充套件9GB，同時表空間擴充套件15GB。可見ASSM oracle為了滿足這麼多HW enqueue 佇列，會一下擴充套件很多。
應對方法看後面

[@more@]

non ASSM的解決，調整_bump_highwater_mark_count ：

In non-ASSM tablespaces, HWM is bumped up by 5 blocks at a time ( Actually, undocumented parameter _bump_highwater_mark_count controls this behavior and defaults to 5). Heavy inserts in to a table can result in increased HWM activity leading to HW enqueue contention. This issue is prevalent if the table has LOB columns or if the row length is big.

ASSM的解決：

oracle記錄了bug 6376915,不過按下面說法，應該只對LOB有用。

This fix causes ASSM LOB space to batch up reclaim instead of just reclaiming the requested/required number of LOB chunks. To enable this fix, set event 44951 to the maximum number of chunks that you would like to have reclaimed per attempt. The maximum allowed is 1024. Anything larger becomes 1024. However, if the requested amount of space is larger than the event’s value, the reclaim will be for the requested amount of space.

而如果表裡根本沒有LOB，這個event沒用，怎麼做。

能想到的是，低峰期手工擴充套件，防止高峰期HW enqueue對事務的阻塞，影響業務。

首先看看unused空間，我推測應該是HWM以上空間+free list的空間。所以要給unused多預留一些：

set serveroutput on size 10000;
DECLARE
su NUMBER;
sa NUMBER;
cp NUMBER;
BEGIN
dbms_space.object_space_usage('USER','TEST_TABLE','TABLE', NULL, su, sa, cp);

dbms_output.put_line('Space Used: ' || round(su/1024/1024,0));
dbms_output.put_line('Space Allocated: ' || sa/1024/1024);
dbms_output.put_line('space unused: ' || round(sa/1024/1024-su/1024/1024,0));
dbms_output.put_line('Chained Percentage: ' || TO_CHAR(cp));
END;
/

手工擴充套件，注意會使相關sql age out，所以要在低峰期跑。

alter table test_table allocate extent (size 1024m);

如果你用dbms_space.space_usage 檢視HWM下的塊情況，會發現擴充套件前後HWM下的空間並沒有變化。

而你用dbms_space.unused_space看這個段，發現unused bytes剛好是你擴充套件的空間。

而前面dbms_space.object_space_usage裡的unused space大於dbms_space.unused_space裡的unused bytes。

我推測dbms_space.unused_space裡的unused bytes是HWM以上的空餘空間，而dbms_space.object_space_usage裡的unused space是HWM以上的空間+free list得到的。