Discovering severe library cache-related contention can lead to the painful realization that bind variables should be used where they have not been employed. Application developers will be very unhappy, as it can require a significant amount of rework. DBAs dread bringing this to the developer's attention because, once again, they look like the bad guys.

One of my students told me about a situation involving a vendor application used for university student registration. When students logged on to the system and began registering for courses, immediate and intense library cache latch contention manifested. It was obvious and painful! So the DBAs approached the application vendor and presented their case. Surprisingly to them, the vendor replied that the cost of converting the application to using bind variables was simply too expensive. The vendor effectively said, "No way!" Not giving up, the DBAs went back to work and studied the situation closely. To their amazement, they discovered only three SQL statements were repeatedly run the most often. They figured if just the three SQL statements used bind variables, that would be enough to solve the problem. They met with the vendor again, presented their case, and the vendor agreed to make the change. After the bind variables were used, library cache latch contention dropped from sight!

What I like so much about this story is that the DBAs understood the problem and took their analysis to the next level. After demanding that the vendor make the change and being turned down, most DBAs would just start complaining and whining. The problem might never be resolved, forcing a system with much more CPU power to be purchased.