Thursday, August 30, 2012

Cursor Sharing, Versioning and Optimizer Adaptive Cursor Sharing


As I review AWR and Statspack reports from the http://www.statspackanalyzer.com website, I often see excessive versioning. Versioning is when the optimizer for one reason or another decides you need to have multiple versions of the same SQL with different plans in the SQL area of the shared pool. Versioning is a good thing when used correctly, however, when it is used poorly it can result in large numbers of single use SQL statements clogging up the Shared Pool and causing excessive recursion and CPU usage. Figure 1 shows an example of versioning.


Figure 1: Example of Excessive Versioning Oracle11g 11.1.0.7

As you can see in the example in Figure 1 we can get thousands of identical statements loaded into the shared pool as a result of versioning, and this was just a third of the full report section. In some versions of Oracle (10.2 and 11.2) there are bugs that can result in excessive versioning. In Oracle11g a new method of cursor sharing using more advanced bind variable peeking called Optimizer Adaptive Cursor Sharing was introduced. However, none of the advances in 11g seem to have corrected this issue with versioning.

Once you determine that your database is experiencing excessive versioning there are three ways I know of to mitigate the situation. The first of course is to set CURSOR_SHARING to EXACT (the default) and eliminate cursor sharing (although this may be ineffective in some cases).  Another method to reduce versioning is to set CURSOR_SHARING to FORCE and set “_SQLEXEC_PROGRESSION_COST” to either zero (0) or its maximum value on your system. The progression cost is used to determine the cost of SQL statements that will be reviewed for possible bind variable issues. Note that setting the progression cost to 0 also turns off the V$SESSION_LONGOPS  view.

In Oracle11g the Optimizer Adaptive Cursor Sharing may also cause versioning. If you want to turn off the feature there are actually several undocumented parameters that you may have to “unset”:

Cursor_sharing=false
“_optimizer_extended_cursor_sharing_rel’=none
“optimizer_extended_cursor_sharing”=none
“_optimizer_adaptive_cursor_sharing”=false

Let me stress that you shouldn’t mess with undocumented parameters without taking to Oracle support. If you are having versioning issues, contact Oracle support and file and SR, then ask about these parameters and if they will help in your release and in your situation.

Thursday, August 16, 2012

A Bolt from the Blue


In a move announced by press release today at 8am EST catching industry analysts flatfooted, IBM leaped to the front lines of the solid state storage devices wars. In a single bold stroke they can lay the claim of having the fastest, most advanced solid state storage technology for their engineered systems. IBM has agreed to purchase TMS: lock, stock and RamSan.
Press Release

I went to bed last night wearing the red shirts of TMS. However, having been a fan of Star Trek since it first made its TV debut, I should have known that red shirts are usually gone by the first commercial. Rather than meet death as any brave red-shirted Star Trek security would, I have instead been converted to a blue shirt in a wholly painless and so far, enjoyable, process. TMS will be a wholly owned subsidiary of IBM at closing later this year with the ranks and privileges that entails. I guess I may have to resolve even to like AIX…
J.
In a process that has taken several weeks and been shrouded in such  secrecy as would make Star Trek security mavens proud, IBM and TMS have worked together to make the union of the two companies as smooth as is possible. There are many plans afoot to integrate RamSan technologies into the IBM technology stack. So Exadata beware, there is a new Sherriff in town and he’s wearing blue!

So watch this blog as TMS becomes a part of the IBM universe for announcements and the joys of becoming a part of an iconic undisputed leader in the computer world.