• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Optimize Oracle Performance?

 
Ranch Hand
Posts: 340
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 320
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 320
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Author
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
reply
    Bookmark Topic Watch Topic
  • New Topic