performance tuning question

Hi there,

I'm looking for an advice what configuration parameters I need to look at first to boost Oracle 8i performance for the following simple scenario. I'm doing a batch update of a table containing about 65k records. The table structure is rather simple:

MY_TABLE: ID, COL1, COL2, COL3 all fields are integers except COL3 which is VARCHAR 2000. There is no primary key set on the table during update. The program is Java and its logic is (in pseudocode):

for (each id in update)
{

    if (exists(SELECT * FROM MY_TABLE WHERE ID=id))

        UPDATE MY_TABLE SET COL1=val1, COL2=val2, COL3=val3 WHERE ID=id     else

        INSERT INTO MY_TABLE id,val1,val2,val3 }

The update is going painfully slow and takes about 6 hours, and as the matter of fact it is about 7 times slower than MS SQL or DB2 on both AIX and Win2k (can't really tell about MS SQL on AIX ...). This is not a network problem or other environment related issue since load into empty table using only inserts without selects is pretty fast and takes 10-15 minutes, so this is full table scans that slow things down. I get 20% performance gain if index is created on ID field which doesn't help a lot. So, what do I need to look at first? buffers? shared pool? block size? do I need to supply any optimizer hints in my query so the index is actually used? Just want to get some info before I start poking around...

Thanks for any clues.
Vladimir. Received on Fri May 03 2002 - 00:32:59 CDT