Insert 1000s of row/sec, index performance problem

Greetings.

I am working on optimizing an application that has very high performance requirements. The application bottlenecks have been eliminated, and now we have a few left in the database.

The application works in real-time (which is a must) and does the following per request:

• Insert one row in table A
• Insert one or more rows in table B
• Update one row in table C

All tables only have a primary key and a few referencial constraints, no additional indexes.

Table A is partitioned on a date range, currently one partition per month. Grows rapidly. Data is never updated, only appended. Arrives almost sequential.

Table B is partitioned on a number range (account number). Grows rapidly. Data is never updated, only appended.

Table C is partitioned on the same number range as C. Since this table has many updates I use a buffer_pool_keep on it. Each row contains 3 numbers, but there are 25 million rows. It does not grow much. We are considering using a hash cluster here.

The application is a server written in Pro*C, using hostarrays etc. It runs as multiple processes, and since table B and C are partitioned on an account number range, we have implemented a mechanism in the server insuring that the same server process always handles the same account number range, which gives us the benefit that only one server works on each partition (no battling for disk access).

This all works very nicely. For a while. If we squeeze 750 requests through per second, we run stable for about 2 hours. Then the inserts start taking too long, and stats show that Oracle performs as much as 40 reads per insert! It's the index that's the problem ofcourse, my guess is that it becomes too deep.

What we have considered is write the new data to a temporary table and collect data for maybe 10 minutes. After that, the server will write to another temporary table and another process will move the data from the full temporary table to the "real" table using direct load (INSERT ... SELECT using the /*+ APPEND */ hint).

Do you have experience with a system like this? How do you achieve such a high number of inserts per second? Does the proposed solution sound reasonable or do you have any other ideas?

Looking forward to your comments.

Best regards,
Brian

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Sep 19 2000 - 06:44:21 CDT