Re: performance tuning question

For quickest performance, create a view against your table, with an "instead of" insert trigger which has the insert/update logic. Then use sqlldr to load against the view. Java performance will never approach that of sqlldr.

-Peter

Vlad G wrote:
>
> 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 - 09:13:16 CDT