Re: performance tuning question

You need to use bind variables. Read the manual on that. Also just select 1 not * .
That will help a lot.
Jim
"Vlad G" <vladgri_at_NO_hotmail_SPAM.com> wrote in message news:gbpA8.4450$M7.1797776_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.
>
>
Received on Fri May 03 2002 - 07:41:16 CEST