Re: Performance problem hard to solve

"Hole" <h0leforfun_at_gmail.com> wrote in message news:1179760765.288270.64480_at_r3g2000prh.googlegroups.com...
> Hi all,
>
> first of all, thanks to anyone on this NG for your support and replies
> to my past and future posts.
>
> Now, the problem:
>
> I work with Oracle 10g XE, Java 1.4.2 on Win XP (but the problem is
> the same with a Unix machine and Oracle 10g).
>
> I need to perform some InsertOrUpdate statements and we thought to the
> following solution:
>
> try to insert, catch the SQL exception if the row already exists then
> perform the update statement.
>
> This solution presents excessive performance problem. Moreover, with
> the current application design and architecture, this solution seems
> the one applicable.
>
> With some profiling tools, we've noticed that the bottleneck is on DB.
> Java code takes the 10% of overall execution timing...It seems the 90%
> is for the violation on DB, when I try to insert a row that already
> exists, and the consequent SQLException.
>
> With a java profiler tool we can't monitor the DB performance.
>
> Anyone can suggest any (free or community edition) performance
> monitoring tools for Oracle 10g XE?
>
> And...anyone can suggest a better solution than "Try to insert, catch
> the exception if the row already exists then update"?
>
>
> Thanks in advance!
>
>

Performance depends on the probability
of getting duplicates and the number of indexes on the table. However, you may find that

    attempt to update
    if no row updated insert

is more efficient. The cost of a failed insert is quite high.

(That's assuming that you can't do the array-based merge suggested elsewhere).

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html