Re: performance tuning question

From: D.Y. <dyou98_at_aol.com>
Date: 5 May 2002 00:15:04 -0700
Message-ID: <f369a0eb.0205042315.11cdcbf@posting.google.com>

"Vlad G" <vladgri_at_NO_hotmail_SPAM.com> wrote in message news:<jsMA8.7168$M7.2966446_at_twister.socal.rr.com>...
> "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.
>

If only one session is updating this table and ID is a very selective column, you may have some old statistics on the table as someone else suggested. There are a few things you can try: With table populated run this in sqlplus,  analyze table my_table compute statistics; If that doesn't work, change your SQL statements to

 select /*+ index(my_table <index_name>) */ .... (use your temporary index here)
 update /*+ index(my_table <index_name>) */ ...
If you want to save some typing, select /* +rule */ ... and update /* +rule */ 

will achieve the same results. But this could be deprecated in future releases.

> 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 Sun May 05 2002 - 02:15:04 CDT