Re: Insert 1000s of row/sec, index performance problem

br_at_bitberry.com wrote:
>
> 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.

I'd guess you probably have a lot of contention for the "high end" of your index....Some things you could try

1. use multiple free lists on the table to avoid any contention there
2. if appropriate, look at reversing the keys in your index to help distribute the load

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse