Efficient Oracle Database Usage By Java and J2EE Applications

In case you haven't guessed, I like producing presentations. In the next two weeks or so, I intend to produce a new presentation on the the "Efficient Use of Oracle By J2EE and Java Applications". To whet the appetite this will include:-

1. Connection management best practices:-

• Connection pool usage
• Setting the connection pool min and max settings to be the same to avoid connection storms
• Oracle 11g server side connection pooling
• Using the 11g to throttle connection rates

2. Efficient cursor management

• The statement Vs preparedStatement API.
• Avoiding hard parsing
• Avoiding soft parsing
• Parameters that affect parsing: session_cached_cursors,
• cursor_space_for_time and cursor_sharing

3. Minimizing network round trips and leveraging bulk operations

• The JDBC fetch size
• Statement batching in JDBC
• The Oracle array interface
• Leveraging Oracle bulk operations
• Intelligent Oracle statement feature usage, specifically usage of: merge
• statements, in line views, in line PL/SQL functions, sub query factoring.
• The client side result cache
• Programming and design styles for minimizing application 'Chatter'
• Caching

4. Design and coding according to the putting the processing close to resource that requires it, specifically:-

• Do not be precious about Java or the database; it is not necessarily a good
• thing to do everything in the database.
• Do not re-invent database functionality in Java

5. Best practices for logger buffer usage

• JDBC auto commit.
• Batch and asynchronous commits as introduced in Oracle 10g.

Watch this space . . .

J2EE Batch Processing Design and Tuning presentation here !!!

As promised here is my presentation on J2EE batch processing. There may be material on the Internet and blogosphere that covers some of the individual areas I touch on in greater detail. However as a piece of material that aggregates this in one place, I don't think its bad.

| Get your Presentation Pack

Presentation On J2EE Batch Processing and Tuning

I have spent much of my time recently on a project focusing on the volume and performance testing of J2EE (Websphere app server) batch processes using a Oracle 10g database for persistence. I will shortly be posting a presentation which will cover some salient points that emerged from this work via slideshare.net.

I wrote the presentation for several reasons:-

1. To capture key lessons learned on the project from a technical point of view.

2. To capture information on batch process design considerations as there is not much information in the public domain.

3. To produce some "joined up" information on tuning J2EE software using Oracle, mainly because most J2EE material stops short of the database at the JDBC level and most database tuning material stops short of application server at JDBC but from the other direction. There is material "out there" but not in one place presented in a manner that covers the whole software stack.

In this day and age most databases are 'fronted' by an application server and we have long since moved on from all the business logic residing within the database and the database being the main focal point of integration. Very few applications are islands and the 'glue' used to join applications up is almost as important as the applications themselves, therefore a more all encompassing approach needs to be taken when tuning and performance monitoring the software stack.

These are the main points I will cover:-

1. Batch process architecture and design considerations.

2. The batch process design used on my project

3. Available monitoring and tuning tools for tuning J2EE architectures using Oracle.

4. Tuning findings

5. Conclusions

As everyone always likes to skip to the end of a book before reading the rest, I will touch on some of the conclusions from this work, namely:-

1. Why Martin Fowler's first law on distributed Object architectures is well founded.

2. Dangers of swamping a J2EE application server with too many threads.

3. Avoiding 'Chatty' designs.

4. Although row by row processing is a concern, it is not always the end of the world in terms of performance and only a genuine concern if it prevents performance and scalability targets from being reached.

The Basics That Undermine RAC Scalability And Availability

I'm writing this post as one of the things I hope to get into in the near future is Oracle RAC. There are two reasons for adopting Oracle RAC:-

1. Scalability

Cluster inter-connect traffic and performance is a major factor here. As such Infiband is being touted as the up and coming inter-connect fabric. Also if you google Joel Goodman Oracle, you will most probably find references to presentations and papers on how uncached sequences without noorder specified can degrade performance via the pinging of blocks across the inter-connect. Other than this, the same factors that influence performance and scalability for a single instance application also apply to RAC. Specifically, these are the "usual suspects" that the Oracle Real World performance include in most of the material they present at Oracle Open World, namely:-

1. Good schema design
2. Efficient connection management
3. Efficient cursor management

Therefore, my first question is this, how many RAC sites test their applications in single instance environments for scalability around these three factors. Also, how many sites test the scalability of their clusters as they add nodes. Interestingly according to Julian Dyke’s book on 10g RAC and Linux, Oracle actually turn cache fusion off in their RAC TCP bench marks.

A further recommendation is that your application has some intelligence when connecting to database services, such that the workload directed at specific nodes, leads to a minimal amount of cluster inter-connect traffic. Without any evidence to back it up, there are probably applications out there which use tables to implement things best described as application level semaphores or tables which occupy very few blocks with flag like columns, which if placed on a RAC cluster will slay the inter-connect.

2. High availability

It is recommended that there is adequate capacity in the cluster to soak up the workload created when one or more nodes fails. Technical issues aside, a cluster is not a panacea for high availability and this can be undermined by several factors:-

• Poor testing of changes applied to a cluster.
• Lack of a realistic test cluster.
• A lack of tight change management procedures and processes.

In short, as I alluded to all of this, it begged the question, how many RAC sites carry out all of this work when implementing RAC, there is no way of knowing suffice it to say that the answer is probably less than 100%.