Re: performance tuning question

besides that,
when you create an unique index on the id field, You could try reversing the logic:
begin
  insert into yourtable
exception
  when dup_val_on_index
  then
    update yourtable
end;

The problem is in the select(1) from yourtable where id = .... Without an index you will hit every time a Full Table Scan, so the index on ID is necessary.

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<0qpA8.11463$xv1.421_at_rwcrnsc51.ops.asp.att.net>...
> 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 - 12:45:12 CEST