Re: MASSIVE degradation of insert using sequence ID's via JDBC on 9i?

bugbear wrote:
> Holger Baer wrote:
>> bugbear wrote:
>>
>>> Dear all;
>>> we are using an Oracle sequence to generate primary
>>> keys in our DB application. This is done in the usual
>>
>>
>> What's usual to you isn't that obvious to most people
>> in this group. So please be more verbose.
>
> OK. This code is run repeatedly just so we can time it sensibly.
> Our app. is not (normally) doing "bulk inserts"
>
> In order to keep this message half-readable, I've appended
> our test harness/code to the end of this post.
>
>> You're doing this to yourself. Why the two network roundtrips when
>> one would be sufficient?
>>
>> insert into table values (table_sequence.nextval, ....)
>>
>> if you need the value of the sequence look into the returning
>> clause.
>
> Thanks for the tip; I think it's independent of the primary
> degradation issue, since even the existing code runs "o.k." (12 X as
> fast
> as 9i) under 10g but that concept is certainly good to know.
>
> Thank you.
>
> Oh, and (DAMN! :-() whilst tidying the code for presenting
> here, it now runs slowly locally (i.e. client and server
> on the same machine, running 9i)

So are you saying now both Oracle versions are equally slow? Then I guess your problem has resolved - both are equally fast - err - slow... :-)

It can have to do with any of these

• different sequence number caching
• different disk/io config of both instances
• different sizes for caches in mem
• other applications accidentally running at the same time (debugger? the other instance?)

just to name a few.

Kind regards

    robert Received on Mon Jun 27 2005 - 11:49:08 CDT