Oracle Scratchpad

April 21, 2011

Cursor_sharing

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 6:36 pm BST Apr 21,2011

For those looking to the next upgrade – here’s an early warning from Oracle:

ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting (Doc ID 1169017.1)

“We recommend that customers discontinue setting cursor_sharing = SIMILAR due to the many problematic situations customers have experienced using it. The ability to set this will be removed in version 12 of the Oracle Database (the settings of EXACT and FORCE will remain available). Instead, we recommend the use of Adaptive Cursor Sharing in 11g.”

The rest of the note contains some interesting information about the behaviour and side effects of this option – which may also help you debug some library cache issues if you’re currently running with this value set in 11g.

3 Comments »

  1. That´s a great news. We’ve been having this issue in a CT site after upgrading to 11g R1.
    Our workaround is: flush a single sql (http://kerryosborne.oracle-guy.com/2008/12/flush-a-single-sql-statement-take-2/).

    Btw, i guess this problem is related as well to Oracle (default Job) collect histogram in for example: pk columns.

    Thanks for the input.

    Comment by Thiago Maciel — April 22, 2011 @ 3:50 pm BST Apr 22,2011 | Reply

  2. Heck, I’ve seen enough troubles with it even in 10g.

    Comment by Mary Elizabeth McNeely — April 27, 2011 @ 12:34 am BST Apr 27,2011 | Reply

    • Mary Elizabeth,

      Alas that’s a common complaint – for every version that’s had it.

      As it says in “philosophy 1″ histograms and bind variables don’t go together – and cursor_sharing != exact combined with method_opt => “auto” gives you the worst possible combination of the pair.

      Comment by Jonathan Lewis — April 27, 2011 @ 4:37 pm BST Apr 27,2011 | 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,101 other followers