Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Liping.

Asked: August 07, 2017 - 3:52 pm UTC

Last updated: September 13, 2017 - 1:23 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

I got user reported a SQL had been run slow about a month. so I checked the AWR snapshots and generated a baseline from AWR snapshot because the good plan was recorded 20 days ago. The related SQL ran faster the next day but the baseline didn't be used. I need to know if the optimizer accidentally generated a good plan right after I generated the baseline without the impact of the baseline at all.
I found the SQL_TEXT has been cut to the first 30 characters in the baseline I generated but the SQL_ID is the same.
I fix the SQL_TEXT by re-generate the new baseline from the cursor cache and found the new baseline only be used once the day after the new baseline was generated and later on the baseline hasn't be executed but the SQL plan stay the same and run good.
Do you think I can remove the baseline and let the optimizer handle by itself in this case? Thanks.

and Connor said...

Take a look at this video I did a while back on identifying sql plan baselines with queries. This should help you detect the usage.


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Helpful information

Liping Gao, August 08, 2017 - 8:43 am UTC

Thanks Connor for the very useful information. However, I just found the baseline has been used this morning and one new baseline has been automatically captured even I had the optimizer_capture_sql_plan_baselines turned off which I need further explain. This video helped to find the plan directive usage for the new or current explain plan but not the history usage. How can I check back the baseline or the plan directive usage history especially my original baseline from AWR had the sql_text to be cut to only 30 characters.
Connor McDonald
September 13, 2017 - 1:23 am UTC

Sorry, that was my fault for not reading the question carefully enough.

For historical information, I think you might be out of luck. I go this from the Optimizer PM:

"There's nothing in the SPM framework to do this. The only thing we have is a "last modifed" date in the SQL plan baselines but that's not going to be any use in many circumstances of course. You could look to see if there are multiple SQL plan baselines and when they were created and last modified. It might tell you something, but it seems a bit tenuous."

Sql plan baselines not sql plan directives

Liping Gao, August 08, 2017 - 6:23 pm UTC

Also, I would point out my question is about sql plan baseline not sql plan directives. I need to know if the baseline impacted the optimizer in any way in history even it was not used and if there is any disadvantage for optimizer to keep a baseline for a query.
Connor McDonald
August 16, 2017 - 11:55 am UTC

I asked the Optimizer product manager, and got this:

"There's nothing in the SPM framework to do this. The only thing we have is a "last modifed" date in the SQL plan baselines but that's not going to be any use in many circumstances of course. You could look to see if there are multiple SQL plan baselines and when they were created and last modified. It might tell you something, but it seems a bit tenuous."

Baseline to be removed

Liping Gao, August 16, 2017 - 2:57 pm UTC

Thanks Connor for your time on this topic. Since there is no way to check back the history of the baseline ever to be used, I would remove the baseline for the Query to reduce the maintenance overhead.. Do you think there is any risk to remove the baseline for the Query? If the baseline didn't be used and didn't impact the optimizer, How can the optimizer generated a good plan after the query went bad for a month and the only thing I did is loading a baseline from AWR the day before.
Connor McDonald
August 17, 2017 - 1:13 am UTC

If you change absolutely no statistics, then a plan can still change.

For example, if you do:

select * from T where date_col > sysdate

then as sysdate advances, the costing of this query may change. In particular, if (say) the high value for date_col (ie, from the optimizer statistics) is 01-SEP-2017, then as sysdate advances past Sep 1st, the estimate rows will get lower and lower each time the query is parsed, which of course could lead to a different plan.

And in 12c, there's a *whole* lot of reasons why a plan can change from execution to execution. There's a nice whitepaper on it here

https://www.doag.org/formes/pubfiles/5055437/docs/Konferenz/2013/vortraege/Oracle%20Datenbank/2013-DB-Maria_Colgan-Oracle_Optimizer__What_s_new_in_Oracle_Database_12c_-Manuskript.pdf

and we improved the control over the "variability" of plans a lot in 12.2


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.