Oracle table acting like a queue

Question

We have a very high concurrent application in which some keys to be processed are constantly written into an Oracle 11g table together with their processing priority. There is a primary key (ID field) on that table coming from a sequence. There is a UNIQUE constraint on the KEY field.

ID    KEY        PRIORITY
-------------------------
1     ABC           0
2     XYZ           5
3     AZW           0
...
100   CNS           7

The table above get inserted into at very high rates, say around ten thousands records per minute. We also have about one hundred parallel consumers which are continuously pooling the above table looking for work. One such consumer only needs a key to process at a time but there is crucial not two have the same key going to more than one consumer at a time. The processing should happen in PRIORITY followed by ID order.

To satisfy this the consumer ends up by invoking a function like the one below:

FUNCTION select_key RETURN VARCHAR2
IS
   v_key VARCHAR2(64) := NULL;

   CURSOR keys IS
   SELECT key
     FROM my_table
    ORDER BY priority, id
      FOR UPDATE SKIP LOCKED;
BEGIN   
    OPEN keys
    LOOP
        FETCH keys INTO v_key;
        EXIT WHEN keys%NOTFOUND;
        DELETE FROM my_table WHERE key = v_key;
        EXIT WHEN SQL%ROWCOUNT > 0;
    END LOOP;
    CLOSE keys;

    RETURN v_key;
END;

Because this table gets inserted into and deleted from as such a high rate the stats on that table quickly became stale. The execution plan of the above SELECT shows a full table scan. This causes the key selection process to take longer and longer with a significant performance degradation over time.

In addition to this because the ORACLE locks at data block level rather than at record level we experienced processing not really happening in the PRIORITY followed by ID order. This is not really a big issue for us but still something we would prefer to avoid.

Another bigger issue with this approach is that you run a full SQL each time against a table that can easily get a few tens of thousands records just to get one key.

The first idea that came to my mind was to use a real queue for this and have my concurrent consumers served from that. However I ended up in having all kind of issues with synchronizing my table and the queue feed that in the end I gave up this idea.

Any suggestion about how should I better approach this would be much appreciated.

Thank you in advance.

Answer 1

Create an index on (PRIORITY, ID) and then the query can use an INDEX FULL SCAN to read the data in order instead of scanning the whole table.

Sample table and data

drop table my_table;

create table my_table
(
    key varchar2(100) not null,
    id number not null,
    priority number not null,
    constraint my_table_pk primary key (key)
);

insert into my_table
select level, level, level
from dual connect by level <= 100000;

begin
    dbms_stats.gather_table_stats(user, 'MY_TABLE');
end;
/

Normal explain plan with FULL TABLE SCAN

explain plan for
select key
from my_table
order by priority, id;

select * from table(dbms_xplan.display);


Plan hash value: 3656711297

---------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |   100K|  1562K|       |   637   (1)| 00:00:01 |
|   1 |  SORT ORDER BY     |          |   100K|  1562K|  2760K|   637   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| MY_TABLE |   100K|  1562K|       |   103   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Create index for better index access plan

The cost does not look better at first. But the real version should be much faster because it will stop processing quickly.

create index my_table_idx on my_table(priority, id);

explain plan for
select key
from my_table
order by priority, id;

select * from table(dbms_xplan.display);

Plan hash value: 2209255802

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   100K|  1562K|   577   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_TABLE     |   100K|  1562K|   577   (1)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | MY_TABLE_IDX |   100K|       |   292   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------