Upscaling Your Database Application Performance: Bind Variables

Recently I’ve had several encounters with issues that I would consider to be part of basic scalable application design. These aren’t design issues that are new. Most of them have been addressed many times over the past years, however, they are issues that continually seem to appear. My guess is that it’s not the same people making the same mistakes, but rather new people making the same mistakes. Each of my next few posts will be taking one of these application design issues and addressing it.

Bind Variables
I think Tom Kyte’s statement from Expert One-on-One Oracle says it best:

If I were to write a book about how to build nonscalable Oracle applications, then “Don’t use Bind Variables” would be the first and last chapters.

If Tom wrote that in his book which was published in 2001, why do I see OLTP applications today that do not use bind variables?

Let’s next consider the three categories of application coding from Designing Applications For Performance And Scalability. I’d like to focus on the 24 concurrent session numbers from Picture 2 and Picture 3.

Picture 2

Picture 3

By using the repeating execute only logic the optimal performance is achieved – around a 45x improvement over using literal values (2100 vs. 48).

Below is a slide from the OOW2006 Real World Performance Session I.

Performance Basics

For this workload, the optimal implementation scales almost 2x on the same hardware compared to the implementation with hard parsing and cursor_sharing=force. My point here is that even though one can set cursor_sharing=force, it still does not provide the same scalability that implementing the optimal logic at the application level can provide.

The topic of Good Cursor Usage and Management is also discussed in the Oracle Database Performance Tuning Guide. The Performance Tuning Guide is not just for DBAs, it contains many suggested programming techniques for scalable applications.

I could go into numerous examples but I think the point I’m trying to make here is quite clear. As an application developer there is a great opportunity to “build in” (or leave out!) scalability into an application. If you think you can code however you choose and then turn it over to the DBA to “tune the database” at a later time, you are quite naive. I think this proverb says it best: “An ounce of prevention is worth a pound of cure“. When it comes to designing your next application, seriously consider the limitations of scalability you build into it. Using bind variables is my OLTP scalable programming rule #1.

One comment

  1. Pingback: Upscaling Your Database Application Performance: Cursor Management | Structured Data

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s