This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Optimize Oracle Performance? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Optimize Oracle Performance?" Watch "Optimize Oracle Performance?" New topic
Author

Optimize Oracle Performance?

Sandeep Chhabra
Ranch Hand

Joined: Aug 28, 2005
Posts: 340
Hi Author and others,

I have a very small question regarding optimizing the performance of Oracle using JDBC. Also what are the best practices to connect to an Oracle Database.
I often face slow connections,
Kindly guide

Thanks


Regards<br />Sandy<br />[SCJP 5.0 - 75%]<br />[SCWCD 1.4 - 85%]<br />------------------<br />Tiger, Tiger burning bright,<br />Like a geek who works all night,<br />What new-fangled bit or byte,<br />Could ease the hacker's weary plight?
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Connection pooling, connection pooling, connection pooling.

Also, if connection times are slow, then often the network is slow in general and it becomes even more important to use Statement.setFetchSize() (or Oracle's non-standard OracleConnection.setDefaultRowPrefetch() ). By default, the Oracle driver will retrive ResultSets in 10-row chunks; usually, raising the fetch size improves performance significantly (at the cost of using more memory for buffering). A size of 100 or 250 is usually better, but it depends a lot on your average row size. If your row size is really tiny, a bigger fetch size may be better.
Rajan Chinna
Ranch Hand

Joined: Jul 01, 2004
Posts: 320
I do notice performance issue especially when we had load test and to me MS SQL server seems to be bit faster than Oracle.
Rajan Chinna
Ranch Hand

Joined: Jul 01, 2004
Posts: 320
I have a simple SQL like <select a,b,c from table where field=value>.

Is it good idea to have this in stored procedure rather than declaring it in java level in terms of performance & maintainability wise?
R. M. Menon
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
Hi Author and others,

I have a very small question regarding optimizing the performance of Oracle using JDBC. Also what are the best practices to connect to an Oracle Database.
I often face slow connections,
Kindly guide

Thanks


For connecting, almost always you want to use connection pooling as Stu puts elegantly.
For using JDBC performance wise there are many many best practices. Some that pop to the mind immediately are:
1. Knowing and exploiting your database features to the maximum extent (independent of whether you use JDBC or not and regardless of whether you use Oracle, DB2, MySQL or whatever) This is the part most Java developers miss out on typically and this is very important IMHO.
2. Using batch processing (update batching) or fetch size when required.
3. Using bind variables unless you know why you are not using them(i.e. use PreparedStatement and CallableStatements)

among others.

Menon
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Optimize Oracle Performance?
 
Similar Threads
10 g App Server versus Weblogic versus Websphere
Oracle 9i Application Server Performance information
XMLtype datatype
Any performance Issue b/w oci8 and thin cliient ?
Oracle performance