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

bugbear wrote:
>
> I would imagine so; but see my other post. While
> my test looks a lot like a (very!) badly coded bulk insert,
> our actual application is a heavily user interactive
> beasty. The test is intended to emulate the kind
> of thing our app does, in a simplified context.

Ah OK: you are trying to test-emulate a heavy OLTP load. Keep in mind Oracle does some global optimizations. So if you fire off more than one session doing the same, you may well find the total is equal to the sum of the parts instead of degrading. Things like commit piggybacking and others kick-in.

But the thing still sounds very abnormal in 9i, taking into consideration your comments here:

> To give some numbers, our actual application
> is getting 5 or 6 inserts per second.
>
> The simplified harness is managing around 50 a second against
> a remote 9i, which sounds sort of OK until you hear that a
> remote 10g installed on the same machine gives us 400 a second.
>
> Somewhere, 9i is killing us.

Most definitely. One thought: JDBC versions. I've found that JDBC is highly sensitive on the driver itself. Try using the 10g client against the 9i server and vicky-the-versa to eliminate a possible stuffed driver? 9i and 9ir2 were notorious for differences like that.

> > Like Holger suggested: use
> > INSERT INTO TABNAME (...,table_sequence.nextval,...)
> > It won't be ground-breaking, but it helps.
>
> That change will complicate our application code (don't ask!)

Java and me asking? You're dreaming! :) Seen enough to last me a life time of nasty surprises...

> > Which means there is a unique index behind the
> > PK column(s), so don't expect speed miracles:
> > every new row is checked for uniqueness via the
> > index.
>
> Agreed; but (banging on a bit) 10g is managing ;-)

You're right, of course. Make triple sure the table, constraints and indexes have been created in both versions using the same script. There are some subtle differences that can create performance problems.

> Thanks for all the advice (and a not-forgotten dinner)
>

Thought it was you, given the handle and domain. Pleasure. Make sure you visit again: the gara^H^H^H^Hworkshop is coming together now that the new house is FINALLY a reality. You'd love it here, place is turning into galoot heaven. Door is always open, you know that. Received on Mon Jun 27 2005 - 20:13:56 CDT