Execution plan alters with DDL change [message #673552] |
Fri, 23 November 2018 08:57 |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
Hi,
I have a query which pulls data from 5 tables and populate to target table. At the first it takes 10 mins to populate the data but later it will run for 2 mins as the execution plan is generated. In case i add a new column to one of the table the query again takes 10 mins and causes issues.
I know in 12cR2 we have some dynamic plan generation, but in 11gr2 is there a way we can control that if i add a new column to table and i am not using that in my select query the execution plan to be same as before. Can i pin the execution plan before DDL changes.
Any suggestions
Thanks,
SRK
|
|
|
Re: Execution plan alters with DDL change [message #673553 is a reply to message #673552] |
Fri, 23 November 2018 09:14 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I would not think that this is anything to do with execution plans. More likely that the slow runs are doing physical reads to populate the buffer cache. Easy to test: run ALTER SYSTEM FLUSH BUFFER CACHE and see what run times you get then.
|
|
|
|
Re: Execution plan alters with DDL change [message #673556 is a reply to message #673555] |
Fri, 23 November 2018 09:28 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If the new column isn't referred to then it'll have zero impact on the execution plan.
As John says - the culprit is probably the buffer cache.
When ever a query needs a particular bit of data it'll look to see if it's in the buffer cache (which resides in RAM), if it isn't then it'll go to disk, find the datablock with that bit of data and copy the datablock to the buffer cache. It does this because reading from the buffer cache is significantly faster than reading from disk.
Data stays in the cache until it's forced out by newer data (when there's no space in the cache left the least recently used blocks will be flushed back to disk), or something else happens to force a flush of that data from the cache. DB restart will flush the entire cache, as will ALTER SYSTEM FLUSH BUFFER CACHE.
It wouldn't surprise me in the slightest if altering a table to add a column forced a flush of all data for that table from the cache back to disk.
That all said, as BS implied - why are you worrying about the temporary effects to query time of an alter table? Are you planning on routinely adding columns to production tables?
|
|
|
Re: Execution plan alters with DDL change [message #673557 is a reply to message #673556] |
Fri, 23 November 2018 10:02 |
Solomon Yakobson
Messages: 3284 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
cookiemonster wrote on Fri, 23 November 2018 10:28If the new column isn't referred to then it'll have zero impact on the execution plan.
Any DDL on the table will invalidate SQL plans involving the table and there will be hard parse even if statement didn't change.
SY.
|
|
|
|
|
Re: Execution plan alters with DDL change [message #673598 is a reply to message #673557] |
Mon, 26 November 2018 07:22 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Solomon Yakobson wrote on Fri, 23 November 2018 16:02cookiemonster wrote on Fri, 23 November 2018 10:28If the new column isn't referred to then it'll have zero impact on the execution plan.
Any DDL on the table will invalidate SQL plans involving the table and there will be hard parse even if statement didn't change.
SY.
Well ok - then you may get a different plan at that point.
But if the stats haven't changed and the new column isn't relevant to the query then odds are you'll get the same plan you had before.
And time the hard-parse itself takes shouldn't be noticeable.
|
|
|
|
|
Re: Execution plan alters with DDL change [message #673605 is a reply to message #673602] |
Mon, 26 November 2018 08:31 |
Solomon Yakobson
Messages: 3284 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
That's one of the reasons Oracle removed optimizer_adaptive_features (with TRUE as default) in 12.2 and replaced it with 2 parameters optimizer_adaptive_plans (with TRUE as default) and optimizer_adaptive_statistics (with FALSE as default).
SY.
|
|
|
Re: Execution plan alters with DDL change [message #673608 is a reply to message #673605] |
Mon, 26 November 2018 11:26 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
A design that keeps altering a permanent table is just a horrible design. If you are using this table as a load table look at external tables to bring in the data. What is your justification to keep altering the table layout?
|
|
|
Re: Execution plan alters with DDL change [message #673924 is a reply to message #673608] |
Tue, 18 December 2018 04:21 |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
Hi Bill,
It is a UI related table, as per requirement if the client says they need new fields on UI, we will add those columns to the base table and this base table data is populated to further Different schema tables and we need to add the columns there also. Hence the execution plan gets changed and cause a lot of issues
Thanks
SRK
|
|
|
Re: Execution plan alters with DDL change [message #673977 is a reply to message #673924] |
Thu, 20 December 2018 07:06 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
srinivas.k2005 wrote on Tue, 18 December 2018 04:21Hi Bill,
It is a UI related table, as per requirement if the client says they need new fields on UI, we will add those columns to the base table and this base table data is populated to further Different schema tables and we need to add the columns there also. Hence the execution plan gets changed and cause a lot of issues
Thanks
SRK
That ("UI related table, as per requirement if the client says they need new fields on UI, " ) doesn't justify constantly adding columns to a table. Sounds to me like both the design of UI and the data model are both deeply flawed, having a design with no flexibility or anticipation of future needs.
|
|
|