Issue

When transactions excessively concentrate on the same partition or block of a specific table (e.g., ORDERS), the following phenomena may occur.

• Increase in spin_spin_waiter_list

• Increase in spin-related events in v$system_event

• Accumulation of TX Locks in v$lock

• Intensive access to specific index blocks observed in v$hot_blocks and v$index_stat

Although this combination may not cause overall system performance degradation, partial and complex performance issues causing delays in accessing specific tables can occur.

Cause

The reproduction scenario (which can frequently occur in actual operations) is as follows.

Scenario Conditions

• Table: ORDERS

• PK Index: ORDERS_PK

• INSERTs only use ID values within a specific range (e.g., repeating IDs), causing concentrated access to the same index block

• Frequent UPDATEs with multiple sessions accessing the same row simultaneously

• Partitions are separated by RANGE, but the load is concentrated on Partition P2025

Actual Reproduction Method

Repeat INSERT/UPDATE to induce HOT BLOCK occurrence.

For example, configure 20 sessions to perform INSERT or UPDATE on the same range as shown below.

DECLARE

  v_id NUMBER := MOD(DBMS_RANDOM.RANDOM, 1000) + 10000;  -- Repeats from 10,000 to 11,000

BEGIN

  FOR i IN 1..100000 LOOP

    UPDATE ORDERS

      SET STATUS='PROC'

    WHERE ORDER_ID = v_id;



    INSERT INTO ORDERS (ORDER_ID, USER_ID, STATUS)

    VALUES (v_id, 'USER1', 'NEW');

  END LOOP;

END;

Because the ID values repeat within a narrow range, writes concentrate on the same index block, inducing HOT BLOCK.

SQL Inspection Results After Issue Occurrence (Including Actual Example Values)

Confirmation of Spin Wait Increase

SELECT event, total_waits, time_waited

FROM v$system_event WHERE event LIKE 'spin%';



EVENT          TOTAL_WAITS    TIME_WAITED

spin_spin_waiter_list   182,334      892,123

spin_enqueue_wait         23,921       32,993

spin_tbsess_wait           8,412        5,021

The spin_spin_waiter_list value is abnormally high, indicating a very high likelihood of HOT BLOCK occurrence.

Check Accumulated TX Lock Sessions

SELECT sid, lock_type, lock_mode, object_id

  FROM v$lock

 WHERE lock_type = 'TX'

 ORDER BY lock_mode DESC;



Sid      lock_type    lock_mode    object_id

------------------------------------------------------

1203      TX           6           84521

1188      TX           6           84521

1191      TX           4           84521

1207      TX           4           84521

• OBJECT_ID 84521 refers to the ORDERS table.
• Transaction locks are concentrated on the same table or partition.

HOT BLOCK Query

SELECT object_name, file_no, block_no, access_cnt

  FROM v$hot_blocks

 WHERE object_name = 'ORDERS'

 ORDER BY access_cnt DESC;

OBJECT_NAME          FILE_NO  BLOCK_NO           ACCESS_CNT

---------------------------------------------------------------------

ORDERS                  12         289312          932112

ORDERS                  12         289313          855193

It is confirmed that 80-90% of total access is concentrated on two blocks (289312~289313).

Index Queue Check

SELECT index_name, leaf_blocks, hot_leaf_blocks, hot_ratio

  FROM v$index_stat

 WHERE table_name='ORDERS';



INDEX_NAME           LEAF_BLOCKS           HOT_LEAF_BLOCKS   HOT_RATIO

----------------     ---------------       ----------------      --------------

ORDERS_PK                 1200                          2                      0.87

• Out of 1,200 leaf blocks, 87% of traffic is concentrated on 2 blocks.
• HOT BLOCK phenomenon has been reproduced.

Detailed Check of Problem Sessions

SELECT sid, program, sql_id, status, event

  FROM v$session

 WHERE event LIKE 'spin%';

SID      PROGRAM         SQL_ID          STATUS   EVENT

-----    -------------   ----------      -------  -----------------------

1203     JDBC Thin       3a21b99f       ACTIVE   spin_spin_waiter_list

1188     JDBC Thin       3a21b99f       ACTIVE   spin_enqueue_wait

1207     tbsvr_w01       1fa991aa       ACTIVE   spin_spin_waiter_list

Cause

In summary, the following causes are considered to act in combination.

• Writes concentrate on a specific leaf block of the ORDERS_PK index.

• Multiple sessions perform INSERT/UPDATE on the same partition or block.

• Index blocks are not distributed, increasing block-level latch contention.

• Spin lock retries increase accordingly.

• As a result, some sessions wait on TX locks.

• Some sessions wait on spin_spin_waiter_list events.

Solutions

1) Emergency Measures

Kill the problem sessions.

ALTER SYSTEM KILL SESSION '1203';

Rebuild the problematic index.

ALTER INDEX ORDERS_PK REBUILD;

Leaf blocks are redistributed, alleviating the HOT BLOCK.

2) Fundamental Solutions

1. If the PK is sequential, consider HASH partitioning or REVERSE index.

2. Randomize ORDER_ID or increase sequence cache.

3. Separate partitions (e.g., P2025 → P2025_1, P2025_2, etc.).

4. Tune spin-related parameters in TB_HOME/config/tibero.tip.

   • SPIN_COUNT
   • SPIN_WAIT_TIME