Home » SQL & PL/SQL » SQL & PL/SQL » Execution plan alters with DDL change (Oracle 11gR2)
Execution plan alters with DDL change [message #673552] Fri, 23 November 2018 08:57 Go to next message
srinivas.k2005
Messages: 395
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 Go to previous messageGo to next message
John Watson
Messages: 8274
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 #673555 is a reply to message #673552] Fri, 23 November 2018 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 26708
Registered: January 2009
Location: SoCal
Senior Member
srinivas.k2005 wrote on Fri, 23 November 2018 06:57
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.

If you are frequently & regularly adding new columns to table,
I question the validity & correctness of the basic application "design".

Run the problematic SELECT immediately after adding the column.
Re: Execution plan alters with DDL change [message #673556 is a reply to message #673555] Fri, 23 November 2018 09:28 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2953
Registered: January 2010
Location: Connecticut, USA
Senior Member
cookiemonster wrote on Fri, 23 November 2018 10:28
If 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 #673559 is a reply to message #673557] Fri, 23 November 2018 11:23 Go to previous messageGo to next message
srinivas.k2005
Messages: 395
Registered: August 2006
Senior Member
Thanks for your reply, let me try that and will reply.
Re: Execution plan alters with DDL change [message #673594 is a reply to message #673556] Mon, 26 November 2018 06:22 Go to previous messageGo to next message
srinivas.k2005
Messages: 395
Registered: August 2006
Senior Member
Hi,

Thanks for your reply, yes there is an application which gets very frequent column additions and whenever we add column and deploy that day it runs for more than 1hr which runs in 15mins earlier and gradually it comes back to 15 mins. And also found that we use the new column in select Clause and not in where clause.

As you mentioned if it does the flush cache, it makes sense why it takes so much time.Looks like there is no solution Smile

Thanks,
SRK
Re: Execution plan alters with DDL change [message #673598 is a reply to message #673557] Mon, 26 November 2018 07:22 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Solomon Yakobson wrote on Fri, 23 November 2018 16:02
cookiemonster wrote on Fri, 23 November 2018 10:28
If 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 #673601 is a reply to message #673598] Mon, 26 November 2018 07:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2953
Registered: January 2010
Location: Connecticut, USA
Senior Member
Don't bet on it. For example, if optimizer decides to use parallel it bumps up dynamic sampling level and adaptive can add to it too. I've seen parse time of several minutes with execution time of 20 seconds Smile.

SY.
Re: Execution plan alters with DDL change [message #673602 is a reply to message #673601] Mon, 26 November 2018 07:38 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Solomon Yakobson wrote on Mon, 26 November 2018 13:33
I've seen parse time of several minutes with execution time of 20 seconds Smile.

SY.
Ouch
Re: Execution plan alters with DDL change [message #673605 is a reply to message #673602] Mon, 26 November 2018 08:31 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2953
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 Go to previous messageGo to next message
Bill B
Messages: 1955
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 Go to previous messageGo to next message
srinivas.k2005
Messages: 395
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 Go to previous message
EdStevens
Messages: 1206
Registered: September 2013
Senior Member
srinivas.k2005 wrote on Tue, 18 December 2018 04:21
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
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.
Previous Topic: Error using API
Next Topic: Multiple Row Record Insert Into One Field And Email
Goto Forum:
  


Current Time: Tue Jun 02 13:59:24 CDT 2020