Adaptive Cursor Sharing VS SPM [message #600352] |
Tue, 05 November 2013 03:14 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/9c2c5464937c21cabe9c54339a090539?s=64&d=mm&r=g) |
VIP2013
Messages: 91 Registered: June 2013
|
Member |
|
|
Hi, we are using Release 11.2.0.3.0 of oracle. And have cursor_sharing default setup as 'exact'.
In case of 'sql queries' using bind variable, which suffers performnace issue(unstabilized plan) due to different bind values at runtime addition with skewed columns. In these cases 'adaptive cursor sharing', will monitor major variation in selectivity/cost of multiple available plan(path of execution) and automatically switch to optimum plan during run time query execution.
So my question is , considering 'up to date stats for all our database objects' what is the requirement of sql plan baseline(spm)? At which situation ,ACS(adaptive cursor sharing) won't able to stabilize the query plan, so that we have to go for baseline or SPM?
|
|
|
Re: Adaptive Cursor Sharing VS SPM [message #600353 is a reply to message #600352] |
Tue, 05 November 2013 03:19 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I think perhaps you are mis-understanding the purpose of these two facilities, also the point at which they are used.
ACS is not about stabilizing pans: it is about selecting an appropriate plan during the soft parse.
SPM restricts the optimizer's choice to a set of one or more plan during the hard parse.
|
|
|
Re: Adaptive Cursor Sharing VS SPM [message #600360 is a reply to message #600353] |
Tue, 05 November 2013 06:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/9c2c5464937c21cabe9c54339a090539?s=64&d=mm&r=g) |
VIP2013
Messages: 91 Registered: June 2013
|
Member |
|
|
Thanks John.
yes, i think i was wrongly interpreting things, let me know, if below is correct
Adaptive cursor sharing:
1.If we are using bind variable in queries, and this query is having different plans(p1,p2,p3) which suits for different value of bind variable(b1,b2,b3) but killing performance when used for other bind values(when p1 used for b2), in these scenario, adaptive cursor sharing will choose the best plan(p1 for b1 and p2 for b2 ... etc) at runtime during soft parsing itself. (Here optimizer is able to get the best plan automatically).
sql plan baseline:
2.In case i am having updated stats, but still my queries not performing well due to some wrong optimizer calculation influence by slight more/less selectivity estimation, and i want to freeze the path of execution for the query, then i will go for sql plan baseline.(Here optimizer not able to get the best plan out of the given stats, so we are forcing the plan, one kind of 'HINTS').
Let me know, if my understanding is correct?
|
|
|
|