Adaptive Cursor Sharing

Oracle 11g introduces Adaptive Cursor Sharing.  This feature is designed to not sharing the cursor blindly but generate multiple plans per SQL statement with bind variables if the benefit of multiple execution plans outweights the parse and memory usage overhead.  Adaptive Cursor Sharing benefits are:
  • Automatically detects when different executions would benefit from different execution plans
  • Limits the number of generated hild cursors to a minimum
Adaptive Cursor Sharing Views
  • V$SQL:  2 new columns have been added.  IS_BIND_SENSITIVE:  indicates if a cursor is bind-sensitive.  IS_BIND_AWARE:  indicates if a cusor is bind-aware
  • V$SQL_C_HISTOGRAM:  shows the distribution of the execution count across the execution history histogram
  • V$SQL_CS_SELECTIVITY:  shows the selectivity cubes stored for every predicate containing a bind variable and whose selectivity is use in the cursor sharing checks
  • V$_SQL_CS_STATISTICS:  Statistics of a cursor use different bind sets

No comments:

Post a Comment