OraFAQ Forum: Performance Tuning » Are Stale STATS Any Good

Home » RDBMS Server » Performance Tuning » Are Stale STATS Any Good (v10.2.0.4 SunOS 5.10 x86_64)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Are Stale STATS Any Good [message #527408]

Tue, 18 October 2011 01:29

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 .

Report message to a moderator

Re: Are Stale STATS Any Good [message #528675 is a reply to message #527408]

Wed, 26 October 2011 06:30

Kevin Meade
Messages: 2103
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

Report message to a moderator

Re: Are Stale STATS Any Good [message #528785 is a reply to message #527408]

Thu, 27 October 2011 02:31

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.

Report message to a moderator

Previous Topic:	Proving OTT undo reads
Next Topic:	Tuning my first query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 09:29:48 CDT 2024