How to induce log file sync waits

(With my tongue causing a minor gash on the inside of my cheek ….)

I’m working on an application that seems to suffer constant long waits on log file parallel write and, consequently, log file sync. I thought that the 54 ms log file parallel writes where just typical of an early 90s disk configuration, with each file type on a discrete drive, all attached to the same controller and it was timely, given a blog post I read recently.

But no, let’s not just slow up the I/O, let’s make sure we generate lots of it! I noticed that these two procedures were some of the most frequently executed.

PROCEDURE RL_addLock (
        lockEntity IN d000m.lock_entity_no%TYPE,
        lockEntityKey IN d000m.lock_entity_key%TYPE,
        lockProfileId IN d000m.lock_profile_id%TYPE,
        lockSessionNo IN d000m.lock_session_no%TYPE,
        lockOperatorId IN d000m.lock_operator_id%TYPE,
        lockTaskId IN d000m.lock_task_id%TYPE,
        lockDate IN d000m.lock_date%TYPE,
        lockTime IN d000m.lock_time%TYPE
        )
IS
        PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        INSERT INTO d000m
        VALUES (lockEntity,
                lockEntityKey,
                lockProfileId,
                lockSessionNo,
                lockOperatorId,
                lockTaskId,
                lockDate,
                lockTime
        );
        COMMIT;
EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
        BEGIN
                ROLLBACK;
        END;
END;
/

PROCEDURE RL_delLock (
        lockEntity IN d000m.lock_entity_no%TYPE,
        lockEntityKey IN d000m.lock_entity_key%TYPE,
        lockProfileId IN d000m.lock_profile_id%TYPE,
        lockSessionNo IN d000m.lock_session_no%TYPE
        )
IS
        PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
        DELETE FROM d000m
         WHERE lock_entity_no = lockEntity
           AND lock_entity_key = lockEntityKey
           AND lock_profile_id = lockProfileId
           AND lock_session_no = lockSessionNo;
        COMMIT;
EXCEPTION
        WHEN NO_DATA_FOUND THEN
        BEGIN
                ROLLBACK;
        END;
END;
/

Excellent. Yet another developer implementing their own locking architecture, presumably to make their application database independent. Yes, it is common, but having just moaned about this kind of thing in the footnote to a previous blog post …

“Why am I still seeing locking problems? I think it’s
because I often have to support Third-Party “Database-independent”
(yuck!) applications, some of which insist on implementing their own
locking mechanisms. Sigh.”
…this one sent me over the edge.