Home » SQL & PL/SQL » SQL & PL/SQL » Oracle:Performance problem in interminent execution (Sun solaris:-Oracle 10G)
Oracle:Performance problem in interminent execution [message #432847] Thu, 26 November 2009 11:56 Go to next message
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 #432848 is a reply to message #432847] Thu, 26 November 2009 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is well known that the optimizer changes its behaviour if the day is odd or even.

Good readings:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky

Regards
Michel
Re: Oracle:Performance problem in interminent execution [message #432869 is a reply to message #432847] Thu, 26 November 2009 22:51 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Read up on bind variable peeking as it is one possible reason for this behavior. Below is the first page I found on Google for "bind variable peeking in Oracle", but there are lots others if this one does not suit you.

Stabilize Oracle 10G's Bind Peeking Behaviour by Cutting Histograms

You should read several articles on this topic before you attempt to "fix" anything. It only makes sense to understand what you are doing before you attemp to do it.

Good luck, Kevin

[Updated on: Thu, 26 November 2009 23:03]

Report message to a moderator

Re: Oracle:Performance problem in interminent execution [message #432924 is a reply to message #432869] Fri, 27 November 2009 05:53 Go to previous messageGo to next message
mail_2_anton
Messages: 3
Registered: November 2009
Junior Member
Kevin,

Thanks for your reply.

If Bind varible peeking is the cause then Can the excution plan changes in alternate day only. How can we confirm the cause.

Thanks in advance.

Regards
Anton
Re: Oracle:Performance problem in interminent execution [message #432925 is a reply to message #432848] Fri, 27 November 2009 05:55 Go to previous messageGo to next message
mail_2_anton
Messages: 3
Registered: November 2009
Junior Member
Michel,

Thanks for your reply.

Can you please elaborate this further.

Why the behavier is changing when it is odd and even.

Thanks

Anton
Re: Oracle:Performance problem in interminent execution [message #432926 is a reply to message #432848] Fri, 27 November 2009 06:04 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Michel Cadot wrote on Thu, 26 November 2009 18:04
It is well known that the optimizer changes its behaviour if the day is odd or even.

Bwaahahahah! Coffee covered monitor. Cheers Laughing
Re: Oracle:Performance problem in interminent execution [message #432930 is a reply to message #432925] Fri, 27 November 2009 06:45 Go to previous message
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.
Previous Topic: Problem with Packages (merged 3)
Next Topic: rows between 2 times
Goto Forum:
  


Current Time: Sat Dec 03 18:03:48 CST 2016

Total time taken to generate the page: 0.07534 seconds