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 queries 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 generate 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.
Update (Dec 2022)
According to the most recent version of MOS Note 9532657.8 (last date Aug or Oct 2022) the fix for bug 9532657 has been superceded by the fix described in Note 33627879.8
It doesn’t seem likely that anyone would still need a patch for the original (which is reported as fixed in 11.2.0.3 and in the base release for 12.1.0.1) but the replacement bug number has 19.11 listed under “Versions confirmed as being effected” and versions below 23.1 listed as “Range of versions believed to be affected”. The headline description of the bug is “No ACS for DML unless BIND_AWARE hint is added.”
Oracle Scratchpad 
Interesting !
Thanks for the update.
Comment by Amardeep Sidhu — March 22, 2012 @ 8:48 am GMT Mar 22,2012 |
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 |
Abel,
Thank you for the explanation – I’ll keep your offer in mind for the future.
Comment by Jonathan Lewis — March 31, 2012 @ 6:51 am BST Mar 31,2012 |