Doing it better ... (Part 1)

I never thought something like this could be worth a blog entry, but as Tom Kyte can do this, I can also! ;-)

I had the pleasure to assist in a little project as a DBA.
The pleasure was mine, as the target (in response-time) was outstanding (in my current working circumstances) and the setup was done to achieve this in a more or less professional manner (which much more outstanding ...), so we had testcases, a kind of change-management, really load-tests, totally cooperative designers/developers. Great!

The goal of the whole thing was to 'lock' and 'free' available resources out of a pool (according to some additional 'quality'). The example I'm writing is about the process 'provide the next free ressource out of the given pool'. It should come back in about 10ms.

Here the Iterations we came through:

1) primary code:

SELECT resource into v_resource
FROM RESOURCE_POOL
WHERE v_time>FREED_TIME
AND STATUS='FREE'
AND POOL=v_pool
AND ROWNUM=1
FOR UPDATE;
UPDATE RESOURCE_POOL set
     STATUS='INUSE',
     FREED_TIME=null,
     START_TIME=current_timestamp,
WHERE RESOURCE=v_resource;

with this comment beside these statements:

-- FOR UPDATE statement ensures that the selected row is locked until the transition is completed --
--    open 2 different sqlplus commands, issue the command and watch the last command. it waits until update is completed (or commit) on the first session
-- FOR UPDATE NOWAIT would report an error if the row cant be locked immediately --
-- FOR UPDATE SKIP LOCKED pauses other db queries - this could be the perf bottleneck ...

BRRR! Even with an index in RESOURCE_POOL.RESOURCE it's not really optimal. And, the hard fact is, it's to slow.

2) after some iterations we came to:

UPDATE RESOURCE_POOL set
            STATUS='INUSE',
            FREED_TIME=null,
            SESSION_START_TIME=current_timestamp,
WHERE v_time>FREED_TIME
           AND STATUS='FREE'
           AND RESOURCE_POOL=v_pool
           AND ROWNUM=1
           AND v_time>FREED_TIME
RETURNING RESOURCE  INTO v_resource;

As we are counting miliseconds, this WAS an advantage. And for me personally, it's more beautiful, even this is not really a tuning-goal.

Beside some tests on the physical layout (we came to the conclusion to use partitions by resource pools and local indexes on them) this saved us enough time so we could stop tuning.
Maybe I also have to tell all the data is small enough to fit into the buffer_pool.
And for the Records, it's 10.2.0.4 EE on HP-UX (Itanium), Failover ServiceGuard.