Or: “Why we should be happy about Adaptive Cursor Sharing”
Premier Support for 10g R2 ended in July 2010, and Extended Support will end in July 2013, 11gR2 is widely used, and 12c is on the horizon. Maybe it’s time for writing a kind of obituary for my first Oracle love, and to spread a salutary anecdote Tom Kyte told me years ago.
The Story
There is a company of the smaller kind with around one hundred people working there, but very profitable. Their business depends on a busy (near-) real time booking system – the one that needs one of those smaller OLTP database systems most of you will know. As users usually do, they are not aware that this system does exist at all.
One rainy summer Friday, suddenly all booking user masks were dead slow. They had no full time DBA, and the on-site application supporter didn’t find the cause, short of his impression that it must be the database, what else? But it was a short Friday, and the users did not worry too much, just worked up old stuff you never finish during normal business. On Monday, the issue was forgotten.
Some weeks later, on the first Thursday in fall, the same issue occurred. Now it was middle of the week, the business impact was more serious, and management winced. The application supporter was forced to look deeper, and now he was sure: The database! Next day, everything was fine. Having a so-called “one time issue”, they postponed closer and expensive investigation to “next time, if it happens again at all”.
Two days later, they were nearly-frozen again, and in the following weeks they experienced more and more bad days like that. The IT department didn’t find anything, so they started pulling the usual straws of any kind. Random rebooting, hardware pimping, and so on. The most useful measure they did, was to collect information about everything unusual and write it down. The secretary who was told to do so disagreed that it’s useful, and for a joke, she started adding today’s weather on top of the daily recording. To her surprise, and what made her the hero of those days, she always had to start each bad day’s report with bad weather news: “rain”, “thunderstorm”, “snowing”. This was the point when IT started asking for reinforcements, and they asked me to come on-site on the next bad day.
I hadn’t to wait long, three days later I was called to come ASAP and thus, arrived at the company in heavy morning rain. First thing they told me, that they found out that afternoon rain does not impact the performance. Crazy folks, aren’t they?
Looking into the DB, I saw three execution plans of heavily stressed major statements using Full Table Scans. But, I thanked my luck stars that they at least have a 10g database and thus, AWR. So the next glance went to yesterdays execution plans: Index Access all over the place. Mh, at least it was obvious why the SQLs are one day fast and one day slow.
To find out bind variable contents, we hat do set a snare: Record v$sql_bind_capture’s values for three now-known SQL_IDs. A simple scheduler job, a table and a simple PL/SQL script did that, and I left for the day.
On next rainy morning, I was very curious to see the selectivity of the binds used, and – aww. When my user-friends in the company started working, they used bind values which were literally asking for Full Table Scans: Looking up table data of months, 99% of tables. The sunny morning before, obviously they didn’t! But why should rain influence user-typed bind variables? Why are they doing things differently in wet weather? Now I was extremely curious! Asking around among the folks on site, who is interested in such old data, directed to a Controlling Dept. guy, who immediately pleaded guilty. It’s his job to do that, and it does not matter if he has to wait an hour. Asking him for today’s user experience, he said: “Extremely fast!”
My summary at this point: Most users profit by sun, one user profits by rain. Woowoo…
I decided to talk to the Controlling guy in private, talk about hobbies and stuff, and he turned out to be an ambitioned bicyclist, even coming to work by bike day after day. But, he confessed, if it’s rainy or snows, he’s a wimp a little bit. He used his car then, with the benefit to arrive first at work and brew the coffee following his own taste. An ugly thought started tickling in the back of my head: Low selectivity. First in office. Execution plan. Cursor Sharing.
After a cup of coffee-thinking, the problem was obvious:
- At night, all Shared Pool entries were swept out due to nightly statistics generation.
- When the average accounting clerk starts working in the morning, he or she issued very selective bind variables, and thus, defined the index-dominated execution plans for the day.
- The Controlling man had to use them as well, but he was used to long execution times, and did never care nor worry.
- When he did not use his bike, but his car due to rainy weather instead, he arrived early. He used the same dialogues as the other users did, but with entirely different, un-selective bind variable contents.
- So if rainy, execution plans used full-table-scans all days. Due to the high frequency the users soft-parsed this very SQL, the DB always re-used and never re-parsed it.
- These unlucky facts made it a full-table scan day.
- Next day was okay again, due to first item. If not wet…
The Lesson
Now we know, why Oracle depends on humidity. But how can we make it weatherproof? The simple answer is: Use 11g Release 2.
Oracle Database 11g Release 2 has a reliable feature called “Adaptive Cursor Sharing”, which enables the database to adapt its behavior when bind cardinalities change after hard parsing. I often blogged about that, so today I won’t elaborate it further.
Let’s hope that Database 12c further improves this very useful optimizer functionality. Signs are promising.