| Testing SPM [message #645145] |
Fri, 27 November 2015 08:28  |
orausern
Messages: 826
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 #645147 is a reply to message #645146] |
Fri, 27 November 2015 09:02  |
orausern
Messages: 826
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 |
orausern
Messages: 826
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 #645160 is a reply to message #645155] |
Sat, 28 November 2015 00:35 |
orausern
Messages: 826
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 |
John Watson
Messages: 8964
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 |
orausern
Messages: 826
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 #645184 is a reply to message #645183] |
Sat, 28 November 2015 04:43 |
orausern
Messages: 826
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.
...but just exploring -greedily - what a feature can give.