Waits, Enqueues, and Latches - Expert Oracle RAC Performance Diagnostics and Tuning - page 589

Less data in each block would mean more blocks to be read from disk and more data transfers when blocks are
transferred over the interconnect.

When the itL area cannot be extended, either because there is no additional space in the block or the max preset value
of 255 is reached, the process waits for an existing itL to be freed. this wait time is reflected as tX enqueue waits.

INITRANS should be selectively increased for segments that have high itL waits when the V$SEGMENT_STATISTIC
view is queried.

TM—DML (Table)
Every time a session wants to lock a table, a TM enqueue is requested. If a session deletes a row in the parent table (DEPT),
and a referential constraint (foreign key) is created without an index on the child table (EMP), or if the session is updating
the column(s) that the foreign key references to, a share lock (level 4) is taken on the child table. If another session tries to
make changes to the child table, the requesting session will have to wait (the enqueue request is in row exclusive mode).
If an index is created on the child table's foreign-key column, then no share lock is required on the child table.

ST—Space Management Transaction
The ST enqueue is held every time the session is allocating/de-allocating extents. Operations such as coalescing, drop/
truncate segments, and disk sorting are candidates that require Oracle to create an ST enqueue lock. If the session gets a
timeout when requesting the ST enqueue, “ ORA-1575 timeout waiting for space management ” is returned.

HW—Segment High Water Mark
High water marks are markers placed by Oracle on the storage segments to indicate the highest point in the segment
that has been used. When a table is created for the first time, and there are no rows, the high water mark is in the
beginning of the segment. As more and more rows get inserted, the high water mark will be moved up to the highest
used segment. However, as illustrated in Figure 17-2 , when rows are deleted the high water mark will not be moved
down until such time that the table is rebuilt, truncated, or shrunk (reorganized).

HWM

Empty Table

HWM

HWM

Table after
Data Insert

HWM HWM

Table after
Data Delete

X X X X X X

X X X X X X

Figure 17-2. Depiction of high water mark (HWM)

This enqueue is used by the space management layer of the Oracle kernel. High water mark enqueues are locks
placed on segments while this occurs it acquires free spaces /extent the segment to insert rows into the table.