Oracle Scratchpad

March 21, 2012

ACS

Filed under: Bugs,Infrastructure,Oracle,Upgrades — Jonathan Lewis @ 6:15 pm GMT Mar 21,2012

You’ve probably heard about adaptive cursor sharing, and possibly you’ve wondered why you haven’t seen it happening very often on your production systems (assuming you’re running a modern version of Oracle). Here’s a little bug/fix that may explain the non-appearance.

MOS Doc ID 9532657.8 Adaptive cursor sharing ignores SELECTs which are not fully fetched.

This bug is confirmed in 11.2.0.1, and fixed in 11.2.0.3. The problem is that the ACS code doesn’t process the statistical information from the cursor unless the cursor reaches “end of fetch” – i.e. if you don’t select all the data in your query, Oracle doesn’t consider the statistics of that execution when deciding whether or not to re-optimise a statement.

It’s quite possible, of course, for an OLTP system, and particularly a web-based system, to execute a number of that allow the user to fetch data one “page” at a time, and stop before fetching all the data – so this bug (or limitation, perhaps) means that some critical statements in your application may never be re-optimized. If this is the case, and you know that you have some such statements that should generate multiple plans, then you could add the hint /*+ bind_aware */ to the SQL.

Upgrade woes: as ever, when a bug is fixed, it’s possible that a few people will suffer from unfortunate side-effects. In the case of this bug, Oracle may start to re-optimize and generated multiple child cursors for SQL statements that (from your perspective) didn’t need the extra work. If you’re very unlucky this may have an undesirable impact on execution performance, and library cache activity.

Thanks to Leonid Roodnitsky for sending me a note about this bug after attending one of my tutorial days last month.

3 Comments »

  1. Interesting !

    Thanks for the update.

    Comment by Amardeep Sidhu — March 22, 2012 @ 8:48 am GMT Mar 22,2012 | Reply

  2. These notes are known as “BugTags”.
    Bugtags are written by support with the intention of providing information to our customers about the bug in the simplest way possible, in some trying to include testcase or abstracts of traces to identify the issue and workarounds if they exists.
    The bugtag note will have the same number as the bug added .8 .
    The fixes in the patchsets are listed in the release notes with links to their bugtag notes.
    Not all bugs have bugtags, though. it is a manual process.
    if you find one bug that it is interesting or feel will be very impacting let me know and I’ll make best effort to have a bugtag on it.
    if ever a bugtag note has any inconsistent, incomplete or improvable information you can add a comment to the bugtag note to have it corrected.

    Comment by Abel — March 23, 2012 @ 2:01 pm GMT Mar 23,2012 | Reply


RSS feed for comments on this post. TrackBack URI

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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,298 other followers