what is high water mark contention and how to fix it

 High water mark contention is a type of contention in Oracle databases that occurs when multiple sessions try to insert data into a table or partition simultaneously. It happens because the database needs to find an available block to insert the data, and this requires updating the high water mark of the table or partition. If multiple sessions try to do this at the same time, they can conflict with each other and cause contention.

To find high water mark contention, you can use the Oracle wait event "enq: HW - contention" in the V$SESSION_WAIT view. This wait event indicates that a session is waiting for access to the high water mark of a table or partition.

To fix high water mark contention, there are several possible solutions depending on the specific scenario. Here are some general recommendations:

Increase the PCTFREE value of the table or partition. This reserves more space for updates and reduces the need to update the high water mark frequently.

Use partitioning to reduce the size of the table or partition. This can reduce contention by spreading the data across multiple partitions.

Reduce the number of indexes on the table or partition. Indexes require frequent updates to the high water mark, so reducing their number can reduce contention.

Consider using a different database design that reduces the need for frequent updates to the high water mark.

eg:

Suppose you have a table called "sales", which has a large number of rows and is frequently updated by multiple transactions. The table has a primary key called "sales_id". Your application has a process that inserts new rows into this table in batches of 100,000. This process runs every hour.

One day, you notice that the application is taking much longer than usual to insert the rows into the "sales" table. Upon investigation, you find that the process is experiencing high water mark contention.

To diagnose the issue, you can run the following query:

SELECT owner, segment_name, partition_name, blocks, empty_blocks, num_freelist_blocks

FROM dba_segments

WHERE segment_name = 'SALES';

This query will show you the details of the "sales" table, including the number of blocks it is currently occupying, the number of empty blocks, and the number of free list blocks. 

The free list blocks are the blocks that contain available space for new rows.

If the number of free list blocks is low or zero, it means that the table is experiencing high water mark contention. This occurs when Oracle has to search for free space in the table by scanning multiple blocks instead of using the free list blocks. This can slow down the performance of the application.

To fix the issue, you can use one of the following approaches:

Increase the size of the tablespace: You can add more data files to the tablespace or increase the size of the existing data files to provide more space for the "sales" table.

Rebuild the table: You can rebuild the "sales" table to reclaim unused space and reset the high water mark. 

This can be done using the following command:

ALTER TABLE sales MOVE;

Modify the application: You can modify the application to insert rows in smaller batches or less frequently. This can reduce the amount of contention on the "sales" table.

Option 1 (increasing the size of the tablespace) may be more appropriate if the table in question is expected to grow significantly in the future. This may be the case for a table that stores a high volume of data that is constantly being inserted, updated, or deleted. In such a scenario, increasing the tablespace size would provide room for the table to continue growing, without the need for frequent maintenance.

Option 2 (rebuilding the table) may be more appropriate if the table is relatively stable in terms of size and content. For example, if the table stores historical data that is rarely modified or deleted, then rebuilding the table may provide a longer-term solution to the high water mark contention issue.

Note:  if you are using automatic segment space management, then adjusting PCTFREE and PCTUSED values may not be effective. The database manages space allocation for you, so you can't control it as granularly as you can with manual segment space management.

In this case, you may need to consider other options like resizing data files or redistributing data across tablespaces. It's important to note that high water mark contention can also be caused by inefficient SQL queries or poorly optimized applications, so it's always a good idea to analyze the entire system and identify any potential bottlenecks.