Analyze Tables

[THoey]

I could have tagged this along to thread186-149792 , but thought it might be easier for someone else to search on if I didn't.

The other day, I was using Oracle 8.1.7's Analyze tool (off the tools menu) to refresh the statistics on the couple of databases that I am responsible for. I also looked at a couple of other databases that I am not responsible for, but get approached on when ever there is a problem. I noticed that those other databases, one being my company's main production database, had not had analyze run on it since I did it in May. I sent an email to the powers that be and suggested that they get with their "DBA" to get analyze run soon and to have it scheduled to run every so often.

The DBA responded back that "if there were no problems (slowness, etc), that they should be careful and probably not run it now". Seems like the "if it ain't broke, don't fix it" strategy. I always thought that this should be run frequently (like, monthly), especially on a database with over 500K records added daily.

What is your take on this? Terry M. Hoey

 

[SpiritOfLennon]

Certainly if the statistics are being used, I run it as frequently as possible. Let's face it the more up to date the statistics are the faster the database should run and time is after all money ( although nobody has ever shown me the equation ). A lax attitude like this tends to imply that the DBA is reactive rather than proactive. SOL
Yeah people they won't understand,
Girlfriends they don't understand,
In spaceships they don't even understand,
and me I aint ever gonna understand

 

[sem]

As far as I know, analyzing is not a goal but rather a mean. If you insert 500K records during a day and then at night some batch process deletes them (moves to some other destination) how often shall you gather statistics? Sometimes it's suitable to gather statistics on such tables only once at the end of some ordinary day and then it will slow processing a bit at morning, when the amount of data is small and the responce is good enough, and significantly speeds it up at evening, when running within conditions similar to those it was gathered. There is no need to analyze them more often, because their lifecycle is 1 day and gathering statistics consumes some resources.

If your queries run perfectly - your statistics is good enough. False implyies anything, not only false.

But in general the strategy must be more flexible than "once a month" or "untill it works". It must take into acount the "nature" of data being evaluated and in general its periodicity depends on the data lifetime.

 

[THoey]

Currently, this specific DB has no "archival" being applied to it. They want me to set that up (only keep the last 13 months worth of data), but I haven't made it a high priority for several reasons I won't go into here. But, they usually only deal with the last 30 days of data, only occaisionally going back any further.

I agree, the nature of the data should be taken into account, but since this is ever growing without new statistics being changed, I don't believe we are using the system to the best of its abilities.

But... It is currently "fast enough", so they are not going to run it. Terry M. Hoey

 

[sem]

Sometimes new statistics adds nothing to the already existing. Oracle supports a limited set of acces methods/join operations. If it already choses the best one there is no need to disturb it. And also if your database grows up uniformly, starting from some point execution plans probably remain the same. The rule 2>10 is about the same as 2K>10K and 2M>10M. If your database is 3 years old a 1 year old statistics is likely to be actual, though less precise than 1 month old. So there is no general answer on how often it should be done. Not "instead of" but rather "in addition to" the real execution plans should be analyzed.

 

[THoey]

Cool. Thanks for your ideas on it. I was just curious... Terry M. Hoey

 

[carp]

At least you'll have a starting point if somebody comes to you complaining about degrading performance!

 

[rcurva]

I had the same encounter with Oracle consultants. In terms of optimization, they will ask you your acceptable performance first before doing any tuning inside, be it an index or just analyzing the tables. For example, will you be comfortable enough with a 2-hour run? Does it degrade your Service agreement? It looks like your set of users utilizing that DB in your site are quite contented with the performance, so no need to touch anything.

Performance tuning is mostly giving satisfaction to the DB users, right?

But if you see something that needs to be corrected, like their backup strategy not properly implemented, that's the time to be proactive.