Re: performance tuning question

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. 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?

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

"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 Fri May 03 2002 - 10:51:12 CDT