Re: performance tuning question

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<NSSA8.113916$CH.14721_at_rwcrnsc52.ops.asp.att.net>...
> How about bind variables and the other suggestions people made? I think the
> one where you do the insert and if it fails due to duplicate then do the
> update. Probably isn't using the index because you haven't analyzed it.
> Probably best thing is to wrap it into a stored procedure and use the array
> interface to do a bulk of them at a time.
> What is the explain plan on the select?
> Jim
> "Vlad G" <vladgri_at_NO_hotmail_SPAM.com> wrote in message
> news:eNLA8.6972$M7.2923477_at_twister.socal.rr.com...
> > I tried to replace select * with select 1, got some marginal improvement,
 if
> > any at all. I don't think this is the problem..
> >
> >
> > "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.
> > > >
> > > >
> > >
> > >
> >
> >

You should do this internally in Oracle not across a JDBC link. You don't mention where the data is coming from. On my W2K test machine, 1 processor, 863 mghtz, single 30 gig disk it only requires about a minute to create and populate a 310K record table and updated each of the rows in 1.10 minutes. If it is taking several minutes then your system is severly out of tune or out of whack. JAVA should be used for calculations and procedural logic, not doing SQL. Use Oracle to do the SQL using PL/SQL procedures. Use bulk binds, use large array sizes.

Mike Received on Mon May 06 2002 - 18:30:37 CEST