Re: performance tuning question

"D.Y." <dyou98_at_aol.com> wrote in message news:f369a0eb.0205030751.1b58d9d2_at_posting.google.com...
> Inserts are relatively fast so that rules out archive, redo, CPU, and I/O
> problems, even though I don't think they would've made things this bad.
> But 10-15 minutes to insert 65K records is still slow by most standards.
>
> There are a couple of possibilities,
> 1, Locking: Other sessions could be updating the same table without
committing
> each update. This only affects updates not inserts.

This is the only one session updating this table.

> 2, Cardinality: If you only have a few distinct ID values then each update
> will touch a large number of records. That would explain why creating an
> index on this column only gave you 20% performance gain. If that's the
case
> wouldn't your last update for a certain ID value overwrite all the
previous
> ones?
>

This is not the case, there shouldn't be duplicates on ID, as the matter of fact ID becomes PK after update completes.

> Please post your solution. I'd be interested to know.
>

No solution so far, seems like my index is not being used due to some reason, this is the only logical explanation i can find.

Thanks,
Vlad.

> "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.
Received on Sat May 04 2002 - 02:53:51 CDT