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

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Tue, 28 Jun 2005 11:22:28 +0200
Message-ID: <d9r4sl$4mm$1@news.BelWue.DE>

bugbear wrote:
> Holger Baer wrote:
>

>> bugbear wrote:

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

For a start, you can just generate the traces with level 8 and try Nial Litchfields Simple Profiler (an adaption of the trace analyzer), which gives you a nice interface, too. See http://www.niall.litchfield.dial.pipex.com/index.html under the Scripts section.

>

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

Ahhhh, the joy of abstraction :-)

>
> This may (from what you say) need to change be looked
> into.
>

It induces lots of soft parses, if the same session is likely to execute the same statement more than once.

> resusing statements would presumably help other DB's too;
> We currently support Sybase and Oracle, with HSQLDB
> used for unit testing;
> http://hsqldb.org/

Sorry, not much experience outside O.
>
> (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.

I'm allmost sympathetic ;-)

>

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

>
>
> Because there are several layers of data structure and code
> between the calling and the SQL. But that's my problem.

It's probably my own stupidity, but still from what I understand you'd traverse those layers only once, right?

>

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

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

I know what you're talking about.

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

I'm really glad about that. 'twas late last night (guess why ;-) )and I might have appeared less helpful than was intended.

Cheers
Holger Received on Tue Jun 28 2005 - 04:22:28 CDT