We have a table that acts as a command queue for order processing. Interactive client applications insert commands like "create order", "modify address", "calculate total price", "delete order" together with the correspondig data into this table. One single processing agent selects the entries, does the processing and deletes the entry.
The table is basically empty after all processing is done and may contain about 10 or 15 entries on peak hours. The processing of one single command may take a maximum of 500msec.
Every day, there are about 30'000 commands to execute which corresponds to 30'000 inserts, selects and deletes on this table.
The problem is that the table access suddenly becomes very slow! This means that a select may take about 3sec vs. 50ms normally. This of course delays the order processing heavily. Please note again that the table is nearly empty and I see no reason why Oracle should take that much time to find the records.
To solve the problem we either do an export-drop-import of the table or a drop-create. Then it works as expected, maybe for a month or so.
One last curious thing: On the server we had (8.1) before the actual one (9i), the same phenomenon appeared, but on two different tables that act as queues as well.