asked on

Oracle:sql query - retaining explain plan

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.

ASKER CERTIFIED SOLUTION

ajexpert

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Milleniumaire

You could always add hints to the query to try to force it to run in a specific way, but hints are just suggestions and Oracle doesn't always follow them.

Alternatively, you could determine the root cause of the problem and fix that rather than trying to work around it! That would be my preferred approach.

If you are correct and it is the gathering of stats that is causing the problem can't you re-scheduled this to a later/earlier time. This will at least prove whether the problem is due to stats gathering. It may be that the issue is due to the volumes of various tables used by the query being different at the weekend and the gathering of stats at these times is resulting in the issue.

LavanyaBabu2011

ASKER

thanks for the inputs but im particularly interested on the approach of retaining old explain plan.
any siggestions plz..

SOLUTION

Milleniumaire

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

LavanyaBabu2011

ASKER

Ok..will try out that.

thanks for the inputs.

LavanyaBabu2011

ASKER

thanks