Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

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


Is this answer out of date? If it is, please let us know via a Comment
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.