Monday Feb 11, 2013

How do adaptive cursor sharing and SQL Plan Management interact?

We've received a lot of questions about how adaptive cursor sharing (ACS) and SQL plan management (SPM) interact.  We discussed this briefly in one of the original SPM posts, but in this post, we'll explain the concepts of how the two features interact, and show an example. 

The simplest way to reason about the interaction is to remember that they are responsible for two different tasks.  ACS controls whether or not a child cursor is shared on a particular execution.  For each execution of the query, ACS considers the current bind values and decides if an existing child cursor can be shared or if the optimizer should be given the chance to find a better plan for the current bind values.  SPM controls which plans the optimizer may choose.  If a child cursor is bind-aware, the decision to share or not is made irrespective of whether the query is controlled by SPM.  But once the query and its current bind values are sent to the optimizer for optimization, SPM constrains the optimizer's choice of plans, without regard to whether this query is being optimized due to ACS.

[Read More]

Tuesday Feb 05, 2013

Upcoming events : RMOUG 2013

I'm really looking forward to the  Rocky Mountain Oracle User Groups Training days next week, at the Colorado Convention Center, as it will bring several new challenges for me.  I am going to be involved in three very different sessions at this years conference:

[Read More]
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« February 2013 »
SunMonTueWedThuFriSat
     
1
2
3
4
6
7
8
9
10
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
  
       
Today