Oracle table acting as a queue

Question

We have a very high parallel application in which some processed keys are constantly written to the Oracle 11g table along with the priority of their processing. In this table there is a primary key (ID field) coming from the sequence. There is a UNIQUE constraint for the KEY field.

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

The table above is inserted at a very high rate, say about ten thousand records per minute. We also have about a hundred parallel consumers who constantly combine the above table in search of work. One of these consumers only needs a key for processing at a time, but it is crucial if not two have the same key, which will have more than one consumer at a time. Processing should occur in PRIORITYfollowed by IDorder.

To satisfy this, the consumer ends up calling a function similar to 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;

, . SELECT . , .

, ORACLE , , , PRIORITY, ID. , .

, SQL- , , .

, , , , . , .

, , .

.

Answer 1

(PRIORITY, ID), INDEX FULL SCAN .

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;
/

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 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 |
--------------------------------------------------------------------------------------------