Java app running SQL and parsing

[fraze]

I've recently been tracing a part of our app that runs Java in the application tier and executes SQL from the app tier from preparedstatements rather than calling PL/SQL inside the database.

That argument aside (its a third party app) I'm seeing heavy parsing e.g.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse   160238     16.26      17.36          0         45          0           0
Execute 160238    126.94     164.81       6572     108965     461410       37702
Fetch   126732     50.28     151.42      11262    1575953       7730      132429
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   447208    193.48     333.59      17834    1684963     469140      170131

I've been told that the app processes its records on by one for "safety reasons" (whatever that means!), but I'd expect the code to parse its SQL once and then run against the record set like you would do in PL/SQL, thus reducing soft parses and so latching etc.

Does anyone know if this is possible in when the SQL is coming from Java. I know nothing about Java really, but my thoughts are this is a technique that could be applied in Java, rather than a constraint of the language?

My interest is in the database side of things where I'm seeing the beginnings of a latching issue that I don't want to come and bite us later if the application has to scale up.

Oracle 9.2 on Solaris

Thanks,

[tamilselvan]

It seems to be that the SQL does not use bind variables.
Why don't you post the actual SQL?

[hrishy]

Hi

Parse=Execute hmm..

are they using prepared statements in jdbc ?

regards
Hrishy