Re: performance tuning question

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:ab5jrj$kji$1_at_babylon.agtel.net...
> 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_at_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.
>
>

I agree that this is not the most efficient algorithm, it really is a compromise between efficiency, the need to handle duplicates on PK in some tables (there's more than 250 tables in the update), complicated data update algorithm that can introduce those duplicates that deals with self-referencing tables, a database design that I'd question for corectness and my lack of control over all those issues. Probably it can be improved but I'm quite happy that it works the way it is....

I got it improved today a lot by not doing a select but rather doing an update followed by an insert if 0 rows were updated, all in Java. The improvement is on order of 30 times, not sure why it is so fast now, this was the only change I made. Have to also check with out DBA if he made any changes to Oracle configuration. EXPLAIN PLAN showed index range scan for my select query but nevertheless it was slow. Thanks everybody for your help.

Vladimir. Received on Mon May 06 2002 - 23:19:13 CDT