Thanks for the question, Jeff.
Asked: September 18, 2016 - 12:02 pm UTC
Last updated: September 18, 2016 - 7:19 pm UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
I'm in a large organization, and we have many environments (Dev, Sys Int, User Acc and Prod). Many times, our code is moved from one environment to the next, and somehow the execution times skyrocket. (Example, 21 seconds to over an hour). We sometimes get error messages like "Unable to Extend Temp Segment" which do not appear to be the cause of the problem. Most of the time, the plan has changed as we move to the next environment. Once the plan is restored by the DBA's, performance is back to normal. Is there any way we can assure that these plans stay in place as we move from one environment to the other? Bear in mind, we as developers have very limited access, so we need to rely on the DBA's. If there's something I can do, or something I can ask the DBA's to do to overcome this problem, that would be a big help.
Thanks so much.
Jeff
and Connor said...
It sounds to me that your databases are perhaps different sizes ? (and hence they would different statistics)...This naturally leads to different plan - which is a *good* thing because a plan against a small env could be totally wrong for large one.
You could copy the statistics across environments to get more consistent plans, but this does not mandate consistent performance if the data volumes are different.
If your databases are consistently sized, then you could use sql plan management to ensure that plans across all environments are kept in sync.
A good white paper on that topic is here
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf