關於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.

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

思考：

但為什麼手動分配extent就可以解決問題呢？extent和high water mark有什麼關係呢？

首先是關於MSSM和ASSM下的high water mark資料

MSSM uses free lists to manage segment space. At table creation, no blocks in the segment are formatted. When a session first inserts rows into the table, the database searches the free list for usable blocks. If the database finds no usable blocks, then it preformats a group of blocks, places them on the free list, and begins inserting data into the blocks. In MSSM, a full table scan reads all blocks below the HWM.

ASSM does not use free lists and so must manage space differently. When a session first inserts data into a table, the database formats a single bitmap block instead of preformatting a group of blocks as in MSSM. The bitmap tracks the state of blocks in the segment, taking the place of the free list. The database uses the bitmap to find free blocks and then formats each block before filling it with data. ASSM spread out inserts among blocks to avoid concurrency issues.

另外：

1）首先HWM的位置是儲存在段頭塊，並且HWM的提升會增加Freelist中的free blocks，而freelist中的塊數的紀錄，也儲存在段頭(當然如果freelist group=n>1，那麼儲存在段頭後面的n個塊中~)，所以這個時候要修改的是段頭塊(以及freelist 所在的塊)，還有一個地方要修改，就是原來freelist中最後一個block，這個裡面記錄了freelist單項列表的最後一個地址，要把他修改，指向新加入freelist的塊的地址~還要在所有新加入freelist的塊上建立freelist單項鍊表~ 可見提高HWM是需要修改很多塊的~

但是如果新加入一個extent呢？對於字典管理表空間，需要修改資料字典等待為ST enqueue（印象中）~ 而本地管理表空間，僅僅需要修改以下資料檔案頭上的點陣圖~
這個工作量是非常小的~ 我不認為分配extent會造成多大的等待~ 當然就算有等待也不是hw enqueue

2）ASSM high-HWM extensions are not done over extent boundaries IIRC, thus with small extents you'd have lots of extensions to do, each requiring HW-enqueue get. ASSM relieves HW-enqueue contention by increasing HHWM in large sizes (depending on extent and current segment size), so less HW operations have to be done.

The LHWM extensions can be done without having HW-enqueue as they involve only changing few records in a BMB block and the BMB is pinned for that exclusively anyway. The actual batch formatting of datablocks is done while having FB enqueue, allowing finer granularity of locking block ranges in case of multiple parallel inserts.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24104518/viewspace-730201/，如需轉載，請註明出處，否則將追究法律責任。