How to debug PL/SQL procedure performance issues in OceanBase

Environment

• OceanBase community edition 4.2.1 (MySQL Mode)
• Table transactions has 1M+ rows, partitioned by id.

I'm encountering performance issues with a stored procedure in OceanBase. The procedure executes significantly slower than equivalent SQL run directly.

Procedure

CREATE PROCEDURE process_transactions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE trans_id BIGINT;
    DECLARE trans_amount DECIMAL(20,2);
    DECLARE cur CURSOR FOR 
        SELECT id, amount FROM transactions WHERE status = 'pending';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO trans_id, trans_amount;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Business logic: Update balance & log
        UPDATE accounts SET balance = balance + trans_amount 
        WHERE account_id = (SELECT account_id FROM transactions WHERE id = trans_id);
        
        INSERT INTO transaction_log(trans_id, amount, exec_time)
        VALUES (trans_id, trans_amount, NOW());
        
        UPDATE transactions SET status = 'processed' WHERE id = trans_id;
    END LOOP;
    CLOSE cur;
END;

Expected ~10K rows in <5s, but currently takes >60s with high CPU load.

A few things I’ve noticed:

• Cursor operations are pretty slow — much slower than just running a SELECT.
• No sign of parallel execution — looped DML doesn’t seem optimized like in Oracle or MySQL.
• Might be locking issues — getting a lot of lock wait timeouts when there’s concurrency.

I’ve tried several optimizations, but none have resolved the issue. I added indexes on transactions(status, id), batched updates using LIMIT 1000 in a cursor, and even disabled triggers—performance remains slow.

How to debug PL/SQL procedure performance issues in OceanBase？

Consider adding index on transactions(status, id, amount) to reduce SELECTion time required.
This should minimize COMPLETE ROW READS.