How could one determine the position of the Low High Water Mark (LHWM) for a non-partitioned heap table segment in Oracle?
I am concerned, that if we only do direct path inserts and never update, delete, or table reorganization, then we will we always be creating an ever widening gap between the low high water mark (LHWM) and the high high water mark (HHWM).
My understanding is that direct path inserts allocate new extents, so there is no chance that we will use/format blocks between the current LHWM and HHWM.
Suppose the first extent had one unformatted block, then the LHWM is necessarrily below this unformatted block.
"The database reads all blocks up to the low HWM because they are known to be formatted, and then carefully reads only the formatted blocks between the low HWM and the HWM."
Oracle Concept Guide - Logical Storage Structure
From then on, every direct path insert will continue to advance the HHWM to cover the new extent(s) while leaving the LHWM behind (and unmodified).
I would like to test this concern by seeing if the LHWM was indeed constant under this use case, but I first need a way to determine the LHWM.
If there was a LHWM for each extent, then I would not be as concerned. However, the discussions seem to be regarding a single LHWM for the segment, not for each extent.
Using dbms_rowid.rowid_block_number(rowid) let's me know which blocks have a row, but does not tell me about the blocks that do not have a row. Those blocks in the extent that do not have a row could be
- formatted but not have data,
- bitmap blocks, segment header, or extent map blocks
- unformatted and below the HHWM
- above the HHWM
Furthermore, just because a block has a row does not mean that it is below the LHWM. Formatted and used blocks can exist between the LHWM and HHWM (Oracle Concept Guide - Logical Storage Structure - Figure 12-26). What's more, the blocks of an extent are not used sequentially, so even if I found the maximum block number of rows within the extent I cannot assume all blocks in the extent less than this block are formatted.
Using dbms_space.space_usage's unformatted_blocks out parameter would give a lower bound for the distance between the LHWM and the HHWM, but because we do not know which extents contain those unformatted_blocks they could all be scattered throughout the segment. All it would take is one unformatted block in the first extent and the LHWM would be stuck there.
Some testing using dbms_space.space_usage's unformatted_blocks seems to show that the direct path insert formats all unformatted blocks below the current HHWM before advancing the HHWM. Assuming the LHWM advances now that the blocks are formatted, then this would avoiding leaving the LHWM behind in this direct-path-insert-only use case.
Is doing block dumps and inspecting them the only option to verify the LHWM position?
"...the database reads the bitmap block to obtain the location of the low HWM."
Oracle Concept Guide - Logical Storage Structure
Note: Using Locally Managed Tablespaces (LMT) and Automatic Segment Space Management (ASSM) on Oracle 19c 19.23 Enterprise Edition on Red Hat.
