Re: performance tuning question

The way you do the update is quite ineffective. A few guesses:

1. I don't see why you definitely should drop the PK on ID and create a temporary index on ID. Doing so invalidates the statistics on the table unless you analyze the table and new index right away. The new index is not used by the CBO because there's no stats available for CBO to decide if it's useful.
2. I would go for the following anonymous PL/SQL block doing either update if data exists or insert if it's not:

    begin

     update my_table set col1 = :newcol1, col2 = :newcol2, col3 = :newcol3
     where id = :newid;
     if sql%rowcount = 0 then
       insert into my_table values(:newid, :newcol1, :newcol2, :newcol3);
     end if;
    -- To further speed up the process, do not commit after each row. Ideally

• you should commit once all data is processed, but you may choose to
• commit periodically (say, after each 1000 rows). end;

    This will eliminate additional query verifying existence of the row - update     will just update nothing if there's no matching id, and this fact will be reflected     in sql%rowcount being 0.
    Also, to speed up the update, do not drop the PK index, or, if you indeed     have to (though I don't see why this might be needed), create a temporary     index and ANALYZE the table before starting the update - this will collect     statistics on table and your temporary index and will probably hint the CBO     to use the index. To analyze the table, issue the following SQL once the     temporary index is created:

    ANALYZE TABLE my_table COMPUTE STATISTICS

    which will compute statistics for the table and all of its indexes.     You will need to repeat this SQL once the temporary index is dropped and     PK is recreated.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


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

>

>

>

>