Matt,

Tom is right...

Here's my experience with JDBC and some rambling thrown in.

1) If you can afford it, buy all the above books. I have them all and they're excellent.
2) Search this site for the word "Menon". He's shared a lot of JAVA code w.r.t. performance. There's also a discussion regarding connection pooling and the ability to reuse SQL statements. Connection pooling makes it harder, but not impossible.

Do not buy any JDBC specific books. They're a waste of money and time. In fact, one of them </code> http://www.oreilly.com/catalog/jorajdbc/

gives the wrong information regarding performance.  Oreilly books are usually very good, but the two they have on JDBC are pretty bad.  I hestitate to even give this link:

http://www.onjava.com/pub/a/onjava/2001/12/19/oraclejdbc.html <code>
but #3 is an example of just WRONG information. This author must have done all his testing on a single CPU Oracle box using one connection to the database. Arghhh....

We had the unfortunate experience of following his advice on a multi-threaded application hitting a 12 processors Oracle box, and needless to say we got hammered. Almost 90% of Oracle's time was spent thrashing the shared pooled reparsing each unique SQL, because we read that Statement performed better.

It took a while to convert the application to use PreparedStatement. Better yet, go with Tom's advice and do it on the database side.

Finally, whatever you do benchmark your JAVA application with real volume in a multi-threaded environment. A small dataset is good to test functionality, but doesn't tell you squat about how your application will scale. I'm learning that we pay to huge price when delay scalabiility testing and we end up letting the clients do it.

HTH,
-Peter

I think that I am with you guys on this one.

I'm about 2/3 of the way through "Effective Oracle By Design", I have "Expert one on one Oracle", and Cary Millsap's Optimizing Oracle Performance is in transit from Amazon, (and I've read Steve Adams and James Morle and ..... and thay (the books)are on my bookshelf).

I have also trawled this site to cover what I think comprises the following receommendations from Tom:

* open cursors in your constructors
* close your cursors in your destructors
* ONLY USE BINDS - PreparedStatement or CallableStatement objects
* Don't call SQL directly, wrap your insert, select, update, delet, merge in PL/SQL. This will allow you to take advantage of some of teh performance smarts in PL/SQL as well as know exactly where to find all your SQL for tuning - when you have found it, fix it, recompile your package and your change is implemented simply without any 'major' application re-builds.

Finally, bear in mind that there are no rules of thumb - all 'rules' *may* be broken/ bent/ disregarded in particular circumstances.

This existing application currently uses the Statement construct and therefore no binds. The application itself processes file data that arrive from users. The application processes all of these whilst connected to the DB as a single user. At the moment then, my understanding is that this application is effectively a single user system. It is an interesting conversation to try and convince a change of development approach in this environment - even when you take the best practice argument. ie: When it is easier to write efficient code, why continue with the existing inefficient methods.

I expect that the app is running in hardware that is much meatier that it needs to be.

I'm intersted to hear any other comments regarding J2EE development on Oracle.

Oh, and a decent book reference, of the same calibre as those listed at the beginning of this review would be nice!

Regards all,

Mat.

So I assume then, that if I am not going down the PL/SQL path, that Java woudl close the cursor explicitly when the cursor has been re-used and DB processing has completed for the particular "object".

Looking forward to Cary's book too. I have just been bitten, by yours though. It's amazing how quickly one can plough through a well written technical book, if it is well written.

I agree - more time parsing and more CPU used than necessary. Less CPU time to do productive work.

In a STATSPACK I expect that I would be seeing lots of hard parses and a high parse/cpu ratio. Any others that I can look out for?

I don't suppose Cary comes up with a way for me to be able to say "Hey, Change this and we will see this measurable benefit".

Thanks again.

Matt,

With a pure JAVA approach, you have to do a lot more work. Use connection pooling because opening and closing connections to the database are expensive. Connection pooling makes reusing PreparedStatement more of a challenge, but not impossible. You have to always explicitly close your connection or you'll end up with cursor leaks. The performance won't be as great as having it in the database.

The only argument I've seen put forth for doing it in all JAVA is that it's easier to port the application to a different database (highly arguable). I think this argument is put forth by people (application developers) who are intimidated with database development. Ask a person who's comfortable and knowledgeable about database development (like Tom Kyte), and he'll say do it the database. Ask a person who's done all Java, and he would say do it in Java. Unlike the JAVA guy, Tom gives very convincing arguments for why you should be doing it in the database.

Here's an example statspack of an OLTP multi-threaded application using way too many statements.

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
1 9.2.0.3.0 NO

Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 21 27-Sep-03 00:57:19 307 .3
End Snap: 22 27-Sep-03 01:20:04 304 .3
Elapsed: 22.75 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 10,240M Std Block Size: 16K
Shared Pool Size: 304M Log Buffer: 1,024K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 265,769.56 92,828.93
Logical reads: 26,635.20 9,303.24
Block changes: 1,996.81 697.45
Physical reads: 49.65 17.34
Physical writes: 20.66 7.21
User calls: 1,508.87 527.02
Parses: 282.46 98.66
Hard parses: 19.60 6.85
Sorts: 215.00 75.10
Logons: 0.02 0.01
Executes: 409.13 142.90
Transactions: 2.86

% Blocks changed per Read: 7.50 Recursive Call %: 18.56
Rollback per transaction %: 0.00 Rows per Sort: 0.81

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.82 In-memory Sort %: 100.00
Library Hit %: 94.67 Soft Parse %: 93.06
Execute to Parse %: 30.96 Latch Hit %: 99.87
Parse CPU to Parse Elapsd %: % Non-Parse CPU:

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 90.74 94.71
% SQL with executions>1: 55.27 60.22
% Memory for SQL w/exec>1: 48.75 47.99

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free 164,859 6,038 85.81
db file sequential read 64,529 812 11.54
db file parallel write 1,859 67 .95
log file sync 3,878 63 .90
log file parallel write 4,153 29 .41
-------------------------------------------------------------

A couple of things I can point out. If you've read Tom's OOO Expert, you'll notice that the Hard Parse and Parse counts are through the roof. My top wait is "latch free" of which I'm spending 85.6% of my time doing. Yep...I would say we have a reparshing too many SQL statements issue. We did have cursor_sharing=force on this database, and it helped a lot. Unfortunately, due to an Oracle bug which crashes the database we had to set the cursor_sharing=exact until the bug is fixed.

-Peter

Would the following be e reason to replace Prepared Stements with Statements and concatenated strings parsed into SQL?

"Use connection pooling because opening and closing connections to the database are expensive. Connection pooling makes reusing PreparedStatement more of a challenge, but not impossible. You have to always explicitly close your connection or you'll end up with cursor leaks. The performance won't be as great as having it in the database. "

I'm told that something like this happened on this system. What is the work around - there must have been an easier solution?

I understand and appreciate the need for binds.

The previous poster hinted at a known consideration when using CallableStatements. I beleive that something like this caused the developers of the app I am refering to re-write their binds to use Statements (no binds). I'm told there was some cursor leak.

At the moment this is all rumour. I believe that they must have hit a problem, but that if the re-write was their solution, then it was definately the wrong one.

I would like to understand any "cursor leak" type problems that could be encountered and what I expect would be a relatively simple workaround if I came across this in the future.

Ta,

Matt,

Cursor leaks is because the developer did not call close on the Statement or PreparedStatement. If you use connection pooling and you call close() on the connection, but you don't explicit close the PreparedStatement or Statement then you'll have a cursor leak. The connection pool doesn't really close the physical connection, so all those unclosed Statements or PreparedStatment will eventually come back and haunt you.

As Tom says,

use Statements
* for DDL.
* in a OLAP application where the SQL is excuted once.

use PreparedStatements every where else.

HTH,
-Peter

if i see JAVA based application running against the database
often there is a configuration like this :

One RAC ( two nodes ) 4G MeM ...
8..16 Application servers ( due to number of seessions )
every 2G MEM ...

I believe if you can use more PL/SQL to reduce Network, and JAVA session memory the number of Application Server could shrink.

But when all SQL is executed by JAVA directly, you need huge resources to to this.

A problem is not solved with object mapping tools like TopLink, i think PL/SQL Usage is not possible.

When i (small)talk with JAVE Experts they do not like to put their attention on database stuff. So the modelling part is perfect, but the perfoamnce part and resources part is under water.

So i am not an enemy of JAVA but if performance is the point you need look over the JAVA universum.

Orca