Doing it better ... (Part 2)

Based on my Doing it better ... (Part 1), several days after the first approach and a lot of testcases (for other parts of the application) the performance of our so well tuned statement decreased dramatically.
What happened? The index on RESOURCE_POOL.FREED_TIME became soomething unbalanced. Not the kind you might argue 'B* trees can not be unbalanced per definition', but more of the kind 'based on the code there often remain some blocks nearly unused, which makes the selectivity of the index apear less and less usable. In fact, the statment is not really nice to the CBO, as an AND ROWNUM=1 is hard to estimate.

This brings me to one of the highlights in this project: Based on good arguments and a test-case we could argue to make a slightly change in the code and the logic. To understand the change some words must be said to the logic: the part v_time>FREED_TIME was used to introduce a kind of 'cold down period' where a resource was not used but neverthleless NOT reused again. This period was ever planned to be several minutes (about 5 was the asumption for the project, but it could also be 2 or 15, but it was agreed NEVER to be less than 1 or more than 20 minutes).

This led us to these changes:
we could change the table to make FREED_TIME of type NUMBER and changed the statement to:

UPDATE RESOURCE_POOL SET
           STATUS='INUSE',
        FREED_TIME=NULL,
        SESSION_START_TIME=:B7
WHERE
     FREED_TIME NOT IN
       (select (to_char(sysdate, 'MI')-(rownum - 1)) - 60 * FLOOR((to_char(sysdate, 'MI')-(rownum - 1))/60) safe_minutes
        from dual
        connect by level <= :MINUTES_GRACE)
 AND STATUS='FREE'
 AND ADDRESS_POOL=:B1
 AND ROWNUM=1 
RETURNING IP_ADDR INTO :O0 

and the statement which 'frees' the resource:

UPDATE RESOURCE_POOL SET
...
STATUS='FREE' FREED_TIME=to_char(sysdate, 'MI')
...

This leads to a logic, where every time the status is freed the current minute is stored in FREED_TIME. There are only 60 different values which come again and again and again. (so The selectivity is quite stable.
The select ... from dual ... connect by ... is a simple way to have a eaily changable list of 'the last x minutes' which is aware the break every hour; the more or less strange formula is a kind of MOD().