Home » RDBMS Server » Performance Tuning » Adaptive Cursor Sharing VS SPM
Adaptive Cursor Sharing VS SPM [message #600352] Tue, 05 November 2013 03:14 Go to next message
VIP2013
Messages: 84
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 messageGo to next message
John Watson
Messages: 4400
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 messageGo to next message
VIP2013
Messages: 84
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?
Re: Adaptive Cursor Sharing VS SPM [message #600629 is a reply to message #600360] Sat, 09 November 2013 13:45 Go to previous message
VIP2013
Messages: 84
Registered: June 2013
Member
Thanks John. I got the difference between the two concept. Thanks for your help.
Previous Topic: Which SQL is faster/better
Next Topic: forms 6i too slow when database have approximately 1 million records
Goto Forum:
  


Current Time: Mon Jul 28 19:36:19 CDT 2014

Total time taken to generate the page: 0.08531 seconds