Oracle:Performance problem in interminent execution [message #432847] |
Thu, 26 November 2009 11:56  |
mail_2_anton
Messages: 3 Registered: November 2009
|
Junior Member |
|
|
Hi,
Let us have 2 tables a1,a2
While performing the DML operation the optimizer sometimes chooses a1 for full scan and it runs so quickly and the next day optimizer choose a2 for full scan and access a2 by index rowid. This execution is running for hours.
Kindly let me know what might be the root cause.
thanks
Anton
|
|
|
|
|
|
|
|
Re: Oracle:Performance problem in interminent execution [message #432930 is a reply to message #432925] |
Fri, 27 November 2009 06:45  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:Why the behavier is changing when it is odd and even.
@Michel was joking.
If the problem is caused by bind variable peeking, then the plan used on that particular day will be the plan derived for the first time the query is run in that day.
The quick and dirty solution to this is to set up a dbms_job (or dbms_scheduler) task that will run he query in such a fashion as to generate the quick plan, and have this job run before any of your users start work.
|
|
|