Oracle JDBC performance: how to exploit defaultRowPrefetch in a WebSphere environment

A lot of WebSphere Commerce customers are using an Oracle database as backend.

From the performance point of view there are several specific parameters to work with to improve WebSphere Commerce performance on the database layer (DB2 or Oracle)

But in the WebSphere Application Server layer there are only a few, grouped on JDBC settings.

And the most important is probably the “Prepared Statement Cache“: Commerce support suggest to starting multipling x3 the default value.

[Overview of WebSphere Commerce performance and stability configurations]

Prepared Statement Cache 50 Specifies the number of statements that can be cached per connection. 150

Unfortunately this parameter doesn’t work with Oracle datasources created on WAS because you should switch to Oracle Universal Connection Pool and enable the implicit statement cache.

Then there is no way to improve Oracle datasource performance on WAS?

Yes, there is.

Using a specific datasource custom property, “connectionProperties”

[Using Oracle JDBC driver specific properties through a datasource]

you can access to several connection properties (“setConnectionProperties” list) through Oracle Datasource API and improve the overall datasource performance.

One of the most interesting connection properties you can change from the performance point of view is “defaultRowPrefetch”.

To setup it, you just need to create a custom datasource property using WAS console.

Oracle Datasource custom property

Oracle Datasource custom property

To check the performance impact for your environment, you need a load test.

Websphere Commerce and Portal Architect ✔ Motivated IT professional with more than ten years of experience, combining Java and JEE developer skills with systems and IBM products installation knowledge. ✔ Strong experience and skills on planning, architecting and implementing complex commerce and portal solutions based on IBM middleware products. ✔ Reliable with a strong network attitude, experience on leading developer's teams and manage international relationships. Specialties ✔ Pleasant manner, reliable. ✔ Ability to consider issues from different point of views. ✔ End-oriented work capacity and problem-solving attitude. ✔ Ability to work with deadlines and under pressure. ✔ Ability to prioritise tasks and manage people. ✔ Ability to increase the whole team skills. ✔ Ability to generate commercial leads

Tagged with: , ,
Posted in JDBC, oracle, was

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: