troubleshooting Question

Oracle:sql query - retaining explain plan

Avatar of LavanyaBabu2011
LavanyaBabu2011 asked on
Oracle Database
6 Comments2 Solutions694 ViewsLast Modified:
In our production database, we run jobs to extract feeds daily at offline hours. It usually completes in around 30 to 50 minutes on a normal day. But on weekends it takes 2+ hours and it has been the way it is performing for the last few months.

When checked i can see te explain plan for the week day is different from to that of week end.
why the explain plan changes only on weekend - i think It is because we run the stats gathering jobs over the weekend .

Now my question is, can i store the explain plan of that sql query executed on weekdays and reuse it on weekends?
I am aware of usage of stored outlines for this but have no approval for implementing the same on production for now. But is there any other simple way to acheive this...Please help.

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros