Home » RDBMS Server » Performance Tuning » Testing SPM (Oracle 11.2.0.4 on Linux)
Testing SPM [message #645145] Fri, 27 November 2015 08:28 Go to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
Hi Experts,

I have Oracle 11.2.0.4 on Linux. For managing sql plans , we have used SPM , the new Oracle feature, for a few individual queries. Now we would like to enable SPM at the database level for all queries. So that that all the queries will use 'accepted plans' and we avoid surprises due to bad plans specially in Production. I mean we want to set up automated capture of all baselines to use SPM for all of our OLTP queries and we also have some long running batch type of jobs all of which will be impacted by this.

In this regard, I wanted to ask that how do we test the impact of making this change before turning it on , in Production? Are there any experiences on it or best practice about it? Is it fine to turn SPM on for all queries? Are there any known issues in this regard or any bad experiences etc.?

I will be thankful for your opinion/help in this regard.

Thanks,
OrauserN
Re: Testing SPM [message #645146 is a reply to message #645145] Fri, 27 November 2015 08:48 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
What have your benchmark tests shown?
Re: Testing SPM [message #645147 is a reply to message #645146] Fri, 27 November 2015 09:02 Go to previous messageGo to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
Hi BlackSwan,

We have not yet started - I mean it is not yet enabled. But we do have the 'normal' (without SPM) baselines for several functionalities. Now we want to go ahead and test this at DB level.

Thanks,
OrauserN
Re: Testing SPM [message #645150 is a reply to message #645147] Fri, 27 November 2015 09:53 Go to previous messageGo to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
Also note that in particular I am worried about the following scenario:

If in middle of a large batch job, which modifies a huge amount of data, there is a need to change the execution plan what would happen? - with default Oracle behaviour this is not an issue. We calculate stats in between the runs of the large jobs and that would help Oracle to get to better plans. but with SPM, oracle will go on using 'accepted' plans only and will not go for a better plan , until the 'baseline evolution' happens right? In that case we will have a new problem due to SPM that oracle will go on using same plan till the next day or whenever the baseline evolution happens? Also I dont' know when the evolution of baselines happen. Is someone aware?

Please share if someone has implemented SPM at database level and what are the pros and cons of doing so.
Thanks,
OrauserN
Re: Testing SPM [message #645155 is a reply to message #645150] Fri, 27 November 2015 19:25 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+read+consistency
Re: Testing SPM [message #645160 is a reply to message #645155] Sat, 28 November 2015 00:35 Go to previous messageGo to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
I don't understand the cryptic reference to google...I am not an expert on SPM, though I did have some hands on to use it quite beneficially in a few cases of a few sql that went for bad plans...I am looking for some help here. But thanks anyway.
Re: Testing SPM [message #645179 is a reply to message #645150] Sat, 28 November 2015 04:27 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
Quote:
If in middle of a large batch job, which modifies a huge amount of data, there is a need to change the execution plan what would happen? - with default Oracle behaviour this is not an issue. We calculate stats in between the runs of the large jobs and that would help Oracle to get to better plans. but with SPM, oracle will go on using 'accepted' plans only and will not go for a better plan , until the 'baseline evolution' happens right? In that case we will have a new problem due to SPM that oracle will go on using same plan till the next day or whenever the baseline evolution happens? Also I dont' know when the evolution of baselines happen. Is someone aware?
Your description of how SPM works is correct. I do not see the problem: SPM is intended to stabilize the system. Any changes are help back until they have been verified, which you have to do manually in your release. I usually automate the process to evolve within whatever time frame you want, but I can't give you the code for that (unless you pay for the intellectual property, of course).


Quote:
Is it fine to turn SPM on for all queries? Are there any known issues in this regard or any bad experiences etc.?
You will find that the SQL Management Base needs a lot room in your SYSAUX tablespace. You must monitor this, and set a space budget with dbms_spm.configure that will let it take several GB. There have been performance issues when the number of stored plans gets high (hundreds of thousands, perhaps) but they should all be fixed by now.
Re: Testing SPM [message #645180 is a reply to message #645179] Sat, 28 November 2015 04:35 Go to previous messageGo to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
Thanks a lot John!

Regarding the first point, can you give an overview of what is involved? - We have several huge jobs that run that change substantial amount of data. What we do now is to just ask the production DBAs to update the stats in middle of a huge batch job and that's it ...from that point on, oracle has more details and it should take care by itself with updated stats. Now with SPM, what would we need to do - we don't have a way to know what all sql (and their sql ids) are fired by batch jobs and so on.

[Updated on: Sat, 28 November 2015 04:37]

Report message to a moderator

Re: Testing SPM [message #645181 is a reply to message #645180] Sat, 28 November 2015 04:38 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
I've already told you what is involved: I automate the process of evolving all new plans.
Re: Testing SPM [message #645183 is a reply to message #645181] Sat, 28 November 2015 04:40 Go to previous messageGo to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
Ah...I see now...sorry I am was late in getting your point and Thanks a lot!!

[Updated on: Sat, 28 November 2015 04:41]

Report message to a moderator

Re: Testing SPM [message #645184 is a reply to message #645183] Sat, 28 November 2015 04:43 Go to previous messageGo to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
And...I got one more doubt...what if we have to deal with an existing sql...a sql that is Not new sql but a batch query that is existing one but now it needs a change in plan due to change in data volume. What would be ,in concept, the way to deal with it? -if you don't mind sharing.

[Updated on: Sat, 28 November 2015 04:44]

Report message to a moderator

Re: Testing SPM [message #645242 is a reply to message #645145] Tue, 01 December 2015 05:20 Go to previous messageGo to next message
LNossov
Messages: 317
Registered: July 2011
Location: Germany
Senior Member
I guess, you try to avoid sql tuning. Perhaps it is best option Wink.
Re: Testing SPM [message #645251 is a reply to message #645242] Tue, 01 December 2015 09:18 Go to previous message
orausern
Messages: 819
Registered: December 2005
Senior Member
I couldn't say so Laughing ...but just exploring -greedily - what a feature can give.
Previous Topic: Making Oracle generate and use different execution plans for any sql
Next Topic: OPT_PARAM - WORK_AREA_POLICY
Goto Forum:
  


Current Time: Mon Oct 22 04:51:01 CDT 2018