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

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Mon, 27 Jun 2005 22:14:49 +0200
Message-ID: <d9pmnq$at2$1@news.BelWue.DE>

bugbear wrote:
> Noons wrote:
>

>> bugbear apparently said,on my timestamp of 27/06/2005 7:48 PM:
>>
>>> When we run locally (i.e. application and database
>>> on the same machine) performance is "OK" for both
>>> 9 and 10, but when we run client-server against 9i, we get
>>> a factor of 15 speed degradation. We can only manage
>>> 5 insertions per second with both client and server being
>>> 2.5 GHz Intel boxes.
>>
>>
>>
>> Have you tried using array inserts?  This is where your
>> JDBC bind variables are vectors of column values.
>> A vector dimension of 10 is usually enough for a
>> major improvement.

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

Or you're killing 9i. Since you have a test that according to you emulates the behaviour of your application pretty well, and since the same network stack manages 400 inserts per sec with 10g, I think we can proceed now to check on the run time differences.

First off all, I hope you know how to generate and process a event 10046 trace, because that is the very next thing you've got to do. Search the NG or register at www.hotsos.com and get from the library an article on how to activate extended trace, or check out the link Nuno suggested.

What you want to know is what Oracle is doing or rather not doing by waiting for something.

Secondly, after a short review of your code: If your application is written like the example, then you're way to much parsing. <quote>
Statement statement = connection.createStatement();

                 ResultSet results = statement.executeQuery("SELECT testtableseq.nextval FROM DUAL");
                 results.next();
                 int sequenceValue = results.getInt(1);
                 results.close();
                 statement.close();

                 PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO 
testtable(testk, testname) VALUES (?, ?)");
                 preparedStatement.setInt(1, sequenceValue);
                 preparedStatement.setString(2, "test" + i);
                 preparedStatement.executeUpdate();
                 preparedStatement.close();

                 connection.commit();

</quote>

You *never* reused your prepared statement. You just used shareable SQL. (Thats a start, at least).See http://asktom.oracle.com/pls/ask/f?p=4950:8:17810148408514716576::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2588723819082 for an example on how to do this properly.

>

>>> The code simply gets the next id in the sequence
>>> (SELECT table_sequence.nextval INTO id FROM DUAL;)
>>> and uses it in the insert statement.
>>> The 2 statements are in the same transaction.
>>
>>
>>
>> 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!)
> but we'll look into it. In the stand alone
> code, it gave some useful improvements.
> But we'd still like 9i to perform a little
> more like 10g.

You made me curious. How can stripping out on unnecessary call make an application more complicated? Wrap the lot in a stored procedure and return the ID to your app.

>

>>
>>> (the table has the "obvious" constraints,
>>> key column is unique, primary etc)
>>
>>
>>
>> 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 ;-)
>

Funny thing. You're the first to complain that a new version is faster than the old one :-)
It might be just that 10g is more adapted to improperly (read: not how Oracle want's things to be done to be as efficient as possible) coded applications.

>>
>>> Has anyone else seen this - a factor of 15 degradation
>>> is "unfortunate". Diagnoses (or cures) gratefully accepted!
>>
>>
>>
>> Other than the above and checking for the obvious things
>> like speed of net connection, I can't think of any.
>> Perhaps turn trace on?  Check out Julian's site
>> for the details:
>> http://www.juliandyke.com/
>>

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

You know, I'm starting to get the naggin' feelin that you should know much more about programming in Oracle than you actually do. Books by Tom Kyte, Connor McDonald, Steve Feuerstein are a highly recommended read, if you're interested.

HTH
Holger Received on Mon Jun 27 2005 - 15:14:49 CDT