jump to navigation

Adaptive/Intelligent Cursor Sharing in 11g – turning-off May 22, 2009

Posted by sendtoshailesh in Uncategorized.
trackback

Here is how the cursor sharing works at a high level with bind variables, especially in conjunction with Histograms (prior to oracle 11g):

  • Optimizer peeks value for the bind on initial parse
  • Initial value of the bind determines the plan
  • Same plan is used/shared regardless of future bind values
  • One plan not always appropriate and optimal for all bind values and results in following problems or side-effects:

– Performance problems
– Instability issues
– Unhappy end users

Adaptive Cursor Sharing address this problem. it shares execution plans ONLY when bind values are “equivalent” – means if it doesn’t result in performance degradation. This is the default behavior in Oracle11g.

More information on adaptive cursor sharing can be found at: http://optimizermagic.blogspot.com/2009/04/update-on-adaptive-cursor-sharing.html – The intention of this post is not to talk about this feature – but how to turn-off this default behavior. I can’t really think of a reason why anyone would like to turn this feature off, but it is possible, if at all we need it for some reason. This can be achieved by using NO_BIND_AWARE hint.

I believe this hint is meant to be an alternative for using the hidden parameter – _optim_peek_user_binds=false which is used to turn-off bind peeking.

Here is the example:

The default behavior:

SQL> variable lv_id number;

SQL> exec :lv_id :=100;

PL/SQL procedure successfully completed.

SQL> Select count(*) from sh.sales where prod_id = :lv_id;

COUNT(*)
———-
0

—-if the bind values are peeked, we would see records in V$SQL_CS_STATISTICS:

SQL> Select address, child_number, peeked, executions from v$sql_cs_statistics;

ADDRESS CHILD_NUMBER P EXECUTIONS
——– ———— – ———-
34C54884 0 Y 1

Now, let’s turn off bind peeking.
======================

SQL> alter system flush shared_pool;

System altered.

SQL> exec :lv_id :=1000;

PL/SQL procedure successfully completed.

SQL> Select /*+ NO_BIND_AWARE */ count(*) from sh.sales where prod_id = :lv_id;

COUNT(*)
———-
0
—-With the above hint, the sql is not bind aware, therefore we don’t see any records in v$SQL_CS_STATISTICS view.

SQL> Select address, child_number, peeked, executions from v$sql_cs_statistics;

no rows selected

SQL>

I guess, this is a better and elegant way to turn-off bind-peeking at the SQL level instead of turning-off bind-peeking at the instance level using the hidden parameter – _optim_peek_user_binds. Again, this parameter is typically used to prevent the side/ill-effects of bind-peeking and the same is now (with 11g) obviated with the introduction of Adaptive Cursor Sharing feature in 11g.

BTW, this hint is available in 11.1.0.7

Advertisements

Comments»

No comments yet — be the first.

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

%d bloggers like this: