Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

11g adaptive cursor sharing tips

Oracle Database Tips by Donald BurlesonFebruary 19, 2015; Updated November 17, 2015

 

Question:  I understand that Oracle11g now has an improved method for cursor_sharing, so that Oracle will compare execution plans, and only use a changed plan when the histogram indicates a difference that makes a difference.  What are the internal details of this 11g adaptive cursor sharing?

Answer:  Oracle has improved cursor_sharing several times over the years.  Remember, 11g adaptive cursor sharing is only deployed in rare cases where a skewed column distribution (as noted by the histogram), indicates that a different execution plan would be faster.  For example, a query with a popular bind variable value would be best served with a full table scan while an unpopular bind variable value would benefit from an index access plan.

Also see my bind_aware hint tips.

Disabling adaptive cursor sharing

Remember, the need for adaptive cursor sharing can be a rare occurrence in many systems.  In many OLTP databases there exists one, and only one optimal SQL execution plan for a query, regardless of the value of a bind variable. 

For these types of databases, the overhead of adaptive cursor sharing may be bypassed by disabling the adaptive cursor sharing feature. 

According to MOSC Bug 11657468.8, adaptive cursor sharing can be disabled by setting hidden parameters:

Turn off adaptive cursor sharing:

alter system set "cursor_sharing"=exact scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
alter system set "_optimizer_extended_cursor_sharing"=none scope= both;
alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both;

In my experience at BC, about 80% of shops have uniformly distributed data. Large tables remain large, and the distribution of values within a column remain unchanged.

Note:  For testing purposes, you may want to turn-off optimizer bind variable peeking and this can be done in several ways::

  • Re-set the _optim_peek_user_binds hidden parameter.

  • Re-analyze using dbms_stats with the argument:  method_opt=> 'for all columns size 1';

Effectively using adaptive cursor sharing


On the other hand, we have roughly 20% of databases that experience highly volatile data loads, where tables are small on one day and huge the next, or cases where the is a "difference that makes a difference". In these databases, huge changes in the tables data (usually associated with high DML) changes the distribution of data values, necessitating a re-analysis of column histograms.

 

Histograms are critical to the CBO decision to choose an index vs. a full scan, and also for determining the optimal table join order.

 

Cursor_sharing=similar and adaptive cursor sharing

 

The original mechanism (cursor_sharing=similar) had significant issues in Oracle9i.  Robert Freeman notes:

"Oracle8i introduced a feature called cursor sharing which provided the ability of the optimizer to convert literals within SQL statements into bind variables in certain situations. As a result, SQL statements that are alike with the exception of literal values, can share a given cursor.

This has the impact of reducing the overall time to parse the SQL statement and, perhaps most importantly, reducing fragmentation of the shared SQL area of the shared pool. Unfortunately, one of the end results of cursor sharing and the use of bind variables is that the optimizer has a difficult time determining the selectivity of the data in the columns associated with the bind variable. This can lead to sub-optimal execution plans. You use the parameter cursor_sharing=force to enable cursor sharing.

Oracle9i now adds modifications to cursor sharing. If you set cursor_sharing=similar, the optimizer will be able to analyze the distribution of the data in the columns (using the analyzed statistics of the table, columns, associated indexes and any histograms that you may have generated, and determine if the parsed execution plan will be optimal. If the plan does appear to be optimal then the parsed SQL statement will be used."

Note:  The cursor_sharing=similar option has been deprecated in Oracle 11g and will be removed in version 12 per MOSC Note 1169017.1

Dr. Tim Hall notes that the 11g adaptive cursor sharing uses bind variable peeking in a different way:

"DBAs are always encouraging developers to use bind variables, but when bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans.

This is because the optimizer peaks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.

Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values.

If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration."

Dr. Hall also notes that the v$sql keeps this adaptive information:

"If we look at the V$SQL view entry for this query, we can see the IS_BIND_SENSITIVE column is marked as 'Y', so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as 'N', so Oracle as not acted on this yet." 

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM   v$sql
WHERE  sql_text = 'SELECT MAX(id) 
FROM acs_test_tab WHERE record_type = :l_record_type';

9bmm6cmwa8saf            0 Y N

Adaptive cursor sharing queries


Here are some sample queries for 11g adaptive cursor sharing:

   select

    hash_value,

   sql_id,

   child_number,

   range_id,

   low,

   high,

   predicate

from

   v$sql_cs_selectivity;

 

select

   hash_value,

   sql_id,

   child_number,

   bucket_id,

   count

from

   v$sql_cs_histogram;

 

select

  sql_id,

  hash_value,

  plan_hash_value,

  is_bind_sensitive,

  is_bind_aware,

  sql_text

from

   v$sql;

 

select

   hash_value,

   sql_id,

   child_number,

   bind_set_hash_value,

   peeked,

   executions,

   rows_processed,

   buffer_gets

   cpu_time

from

   v$sql_cs_statistics;

See my relates notes and articles on Oracle cursor sharing and bind variable peeking:

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational