| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: MASSIVE degradation of insert using sequence ID's via JDBC on 9i?
Holger Baer wrote:
> bugbear wrote:
>> 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.
Yes; agreed. Since other people are doin' OK, there is clearly a monumental mismatch between us and 9i.
Now I just have to find out what...
> 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.
Yep - I'm just heading for that. Does anyone
know if being an Oracle partner gets me access
to metalink; various website recommend the Oracle
trace analyzer, but metalink claims to
be for supported customers.
"OracleMetaLink is FREE for Oracle customers with current product support contracts."
> 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.
Our application is a mixture of user specified queries and "internal" database maniupulation. Our DB layer doesn't know which are which, and thus doesn'y know which ones are liable to repeat themelves.
This may (from what you say) need to change be looked into.
resusing statements would presumably help other DB's too;
We currently support Sybase and Oracle, with HSQLDB
used for unit testing;
http://hsqldb.org/
(note - HSQDB is wonderful for unit tests; you can build a complete new database in RAM, test against it, and tear the DB down in a few milliseconds!)
Customer have already asked
about DB2 and SQL Server.
>> >>>> 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.
Because there are several layers of data structure and code between the calling and the SQL. But that's my problem.
>>
>>
Indeed.
>>> 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/
> You know, I'm starting to get the naggin' feelin that you should
> know much more about programming in Oracle than you actually
> do.
Heh. And Sybase, and DB2, and Java... so much to do, so little time!
> Books by Tom Kyte, Connor McDonald, Steve Feuerstein are
> a highly recommended read, if you're interested.
Oh yeah, I'm interested.
>
> HTH
> Holger
You *have* helped. Thank you.
BugBear Received on Tue Jun 28 2005 - 03:10:24 CDT
 |
 |