High Water Mark active in Oracle

Question: How do I know if the 'High Water Mark' is active in Oracle?
In Oracle, the High Water Mark (HWM) represents the boundary between used and unused space within a segment (such as a table or index). It indicates the highest point at which data was written in the segment. Understanding the HWM's activity can help you assess space utilization and potentially optimize database performance. To determine whether the HWM is active, follow these steps:

1. Identify the target segment: Choose the table or index for which you want to analyze the HWM.
2. Obtain segment information: Query the DBA_SEGMENTS view to obtain information about the target segment. Replace 'SCHEMA_NAME' and 'TABLE_NAME' with the appropriate schema and table names:
   

   SELECT segment_name, segment_type, bytes, blocks, extents
   FROM DBA_SEGMENTS
   WHERE owner = 'SCHEMA_NAME' AND segment_name = 'TABLE_NAME';
   

   
   This query returns the size of the segment, the number of blocks, and the number of extents allocated to it.
3. Calculate space utilization: Compare the size of the segment (in bytes) to the actual amount of data stored within it. You can use the DBA_TABLES view to find the size of the data stored in a table:
   

   SELECT table_name, num_rows, avg_row_len
   FROM DBA_TABLES
   WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME';
   

   
   Multiply the num_rows by the avg_row_len to estimate the size of the data stored in the table. If the segment size is significantly larger than the actual data size, the HWM might be active, indicating potential space fragmentation.
4. Analyze the segment's free space: Use the DBMS_SPACE package to obtain detailed information about the segment's free space and the HWM:
   

   DECLARE
     total_blocks NUMBER;
     total_bytes NUMBER;
     unused_blocks NUMBER;
     unused_bytes NUMBER;
   BEGIN
     DBMS_SPACE.UNUSED_SPACE(
       segment_owner => 'SCHEMA_NAME',
       segment_name => 'TABLE_NAME',
       segment_type => 'TABLE',
       total_blocks => total_blocks,
       total_bytes => total_bytes,
       unused_blocks => unused_blocks,
       unused_bytes => unused_bytes
     );
     DBMS_OUTPUT.PUT_LINE('Total Blocks: ' || total_blocks);
     DBMS_OUTPUT.PUT_LINE('Total Bytes: ' || total_bytes);
     DBMS_OUTPUT.PUT_LINE('Unused Blocks: ' || unused_blocks);
     DBMS_OUTPUT.PUT_LINE('Unused Bytes: ' || unused_bytes);
   END;
   /
   

   This PL/SQL block returns the total and unused space in the segment. If there is a significant amount of unused space, the HWM is likely active, and you may consider reorganizing the segment to reclaim this space.

To recap, in order to determine if the HWM is active in Oracle, compare the segment size to the actual data size, and analyze the segment's free space using the DBA_SEGMENTS, DBA_TABLES, and DBMS_SPACE tools. If there is significant unused space and fragmentation, the HWM is likely active, and you may need to perform segment reorganization to optimize space utilization and improve performance.

The APPEND hint works within statements performing DML insert operations from another table, that is, using a subquery from within an INSERT SQL statement. This is appropriate for when you need to copy a large volume of rows between tables. By bypassing the Oracle database buffer cache blocks and appending the data directly to the segment above the high-water mark, you save significant overhead. This is a popular method for inserting rows into a table very quickly. When you specify one of these hints, Oracle will perform a direct-path insert. In a direct-path insert, the data is appended at the end of a table, rather than using free space that is found within current allocated blocks for that table.
The APPEND and APPEND_VALUES hints, when used, automatically convert a conventional insert operation into a direct-path insert operation. In addition, if you are using parallel operations during an insert, the default mode of operation is to use the direct-path mode. If you want to bypass performing direct-path operations, you can use the NOAPPEND hint. Keep in mind that if you are running with either of these hints, there is a risk of contention if you have multiple application processes inserting rows into the same table. If two append operations are inserting rows at the same time, performance will suffer: since the insert append operation appends the data above the high water mark for a segment, only one operation should be done at one time. However, if you have partitioned objects, you can still run several concurrent append operations, as long as each insert operates on separate partitions for a given table.