Re: performance tuning question

"Vlad G" <vladgri_at_NO_hotmail_SPAM.com> wrote in message news:<fipA8.4452$M7.1805321_at_twister.socal.rr.com>...
> 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.

Currently you are fetching every row to the client. You should really try to do as much at the server side as possible so 1
convert the java code to pl/sql
2 (can also run in java) (and performance wise a better solution provided correct indexes) implement the algorithm using the following pseudo code
update where exists
insert where not exists
NOW you are making use of Oracle!

--
Regards

Sybrand Bakker
Senior Oracle DBA