Oracle-L: RE: Insert performance

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Mon, 22 Sep 2003 19:54:49 -0800
Message-ID: <F001.005D0BCD.20030922195449@fatcity.com>

Rick,
I haven't tried this myself but you could consider a reverse key index (depending on your version).

That way multiple inserts won't go to the same block.

However, (from Perf Tuning 101) you will incur much more IO than a normal index if you do range scans, so you'd need to consider how your index is accessed (probably not doing range scans on a primary key sequence).

Obviously this will need testing to see how well it goes for you.

HTH,
Bruce Reardon
-----Original Message-----
Sent: Tuesday, 23 September 2003 1:45 PM

Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key?

I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes "buffer busy waits".

I have increased initrans, but am not sure where to go from here

Any ideas would be appreciated,

Thanks,

Rick Stephenson
Oracle Database Administrator
Ovid Technologies, Inc.
rick_stephenson_at_ovid.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Reardon, Bruce (CALBBAY)
INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Sep 22 2003 - 22:54:49 CDT