Systems Engineering and RDBMS

Adaptive Cursor Sharing in 11g

Posted by decipherinfosys on January 18, 2010

A client DBA who had migrated their databases to 11g recently asked about the adaptive cursor sharing feature in Oracle 11g.  We have briefly talked about cursor_sharing before in one of our post.   Here is an excellent article on the topic of Adaptive Cursor Sharing by Oracle Ace Arup Nanda:

http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-sqlplanmanagement.html

After reading that article, you would appreciate this new feature and how it is a boon to application developers.  Two take-aways should be:

a) Basic difference is that in prior releases – based on the cursor_sharing setting, the optimizer would optimize the queries for those bind values that are passed in for the first time (via bind variable peeking).  So, if for the very first execution of the query, you passed in a value which made use of a index access path, then it would use that same plan for all the rest of the values that get passed for that query on subsequent executions regardless of whether the next execution should have used a different execution plan.  This can cause issues in case of heavily skewed data values.

With adaptive cursor sharing, if with the first hard parse it used an index access path because the bind variable value that was passed in was very selective it does not necessarily mean that it will use that same path for the subsequent executions of that query with different bind values.  Upon execution on the first time, the optimizer would evaluate whether different bind variable values could lead to different execution plans or not and if it determines that it can, then it will flag the query.  It will mark that cursor to be bind sensitive (you can see that using the is_bind_sensitive and is_bind_aware columns of v$sql).  So, the optimizer will mark a cursor to be bind sensitive if it thinks that the optimal plan might depend and vary based on the bind variable value.

b) You would need to increase your shared pool accordingly since Oracle might need to create some child cursors for the different plans that it would need to create.

Resources:

  • New Features for Developers and DBAs in Oracle 11g: The series by Arup Nanda – here.
  • Another great article on this topic – here.

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: