Java app running SQL and parsing
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.
Code:
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,
