Oracle table access slows down

[Chris Haelg]

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.

 

[Marco Kalter]

Is the select statement also slow when you execute it from SQL*Plus against the same database? Or is it only slow from the application?

If it slow in both situations, then maybe you simply need to perform an "analyze table compute statistics" command from time to time to gather the statistics for the cost based optimizer.

 

[Chris Haelg]

It is slow in all applications, be they based on DOA or ODBC and in SQL*Plus too. The "analyze table compute statistics" does not help.
Chris

 

[Johan]

Hello Chris,

There could be 3 seasons for your problem:

1-
Is there an index on the table? If there is, try the following actions:
a - Rebuild the index.
b - Drop the index and create the index again.
c - Do a "analyze index compute statistics"
The index could be fragmented.
If there is no index, adding one can help.

2-
The Tablespace could be fragmented.
Try the following action:
"alter tablespace xxxxx coalesce"

3-
When a table is filled with data and the the data is deleted on a normal way, Oracle thinks that the table holds still the same amount of data.
If there are 30.000 records when you start and 3 records when you are 8 hours later, Oracle is still seeing 30.000 records.
This is called the high water mark.

When you select data from the almost empty table, Oracle tries to search through 30.000 records.

Only a "truncate table" clears the high water mark.

Success Johan

 

[Jens Fudickar]

That't not complete correct.

An "ALTER TABLE MOVE" also help. But you have to rebuild the indexes afterwords.

Greetings
Jens

 

[al0]

Originally posted by Jens Fudickar:
That't not complete correct.

An "ALTER TABLE MOVE" also help. But you have to rebuild the indexes afterwords.

Greetings
Jens

And it is inaacurate in one more way - the high ware mark is relevant only to full table scnas, it doesn't matter if table is accessed over index.