Home » RDBMS Server » Performance Tuning » Are Stale STATS Any Good (v10.2.0.4 SunOS 5.10 x86_64)
icon4.gif  Are Stale STATS Any Good [message #527408] Tue, 18 October 2011 01:29 Go to next message
drovnik
Messages: 34
Registered: January 2011
Location: Gaborone, Botswana
Member

I have recently been instructed by a 3rd Party Application Vendor explicitly not to gather statistics on certain tables within the database. Apparently gather stats works against the applications optimal operating level; the how part was not answered.

Personally I think it might be design flaw in the application but I cannot seem to find reason not to gather stats and if there is actually any benefit derived from not doing so. If there is anybody out there who might have a reason why this is please shed some light on it .
Re: Are Stale STATS Any Good [message #528675 is a reply to message #527408] Wed, 26 October 2011 06:30 Go to previous messageGo to next message
Kevin Meade
Messages: 1967
Registered: December 1999
Location: Connecticut USA
Senior Member
This is simple:

1) the vendor has already tuned the app to what they consider overall best performance and if you collect stats, you could mess up the query plans they have settled on.

2) the vendor needs to reduce SUPPORT CALLS to them that result from performance issues so they have chosen to give a consistent and predicatable performance for their app instead of best possible performance.

The above are typical when you buy into a third party app. It has its merits and detractors as you might guess. Besides, the idea of stats is to get a good plan. If you have good plan already, then why mess with stats. The presumption of collection of new stats is that there is something better to be gained (ie. a better plan based on new data distributions). If the data distributions don't changes, stale stats are still correct stats.

I am not suggesting you should or should not consider collecting stats. If you are having a performance issue then certainly it must be addressed somehow. I am only describing why a third party vendor would not want you to change stats.

Kevin
Re: Are Stale STATS Any Good [message #528785 is a reply to message #527408] Thu, 27 October 2011 02:31 Go to previous message
drovnik
Messages: 34
Registered: January 2011
Location: Gaborone, Botswana
Member

Consistent and predictable performance is not achieved in this particular scenario ; there are multiple database tables that are high volume inserts, updates & deletes; as many as 2 to 3 million rows will be affected by DML within a space of 24Hrs with peaks between 6PM and 10PM at night. No batch jobs during this time just usage by external customer increases and even during this I have found that the optimizer 9/10 times yields better results . Its at these times that I was told to restore previous stats to what benefit no one can say hence my post since I thought I might be missing something. Anyway thanks a lot.
Previous Topic: Proving OTT undo reads
Next Topic: Tuning my first query
Goto Forum:
  


Current Time: Sun Dec 21 23:47:00 CST 2014

Total time taken to generate the page: 0.11011 seconds