Re: Update Statistics

Hi, Richard > I have a question regarding the necessity of running 'update > statistics' regularly: > > According to the article in Spring 1987 TechNotes, > > 'Because table size affects optimization, you should systematically > update the table information so that RDSQL has current information.' > > Does this mean that, if I have a table of, say, 1 000 000 rows, and > each day 30 000 rows are purged and another 30 000 rows are added, > there is no need to run update statistics as the table size is > relatively constant? Or did this only apply to Turbo, and if so, > what are the other points to consider with Online ? > This information is still true but the impact on the database is different. Under Turbo very limited stats were kept, basically only the row count and the optimiser which was not cost based didn't use the information much and was generally more sensitive to the way the select statement was phrased. In your example above you probably could have got away without running update stats. In online several more stats are held including index tree levels and standard deviation on key values, etc. This is used by the cost based analyser in Online to decide least cost query path. This means that in your example above if you do not run update stats you can quite quickly run into a problem with the analyser choosing inappropriate query strategies based on out of date stats. This is especially true if you are using date based indexes or anything which has a similar creeping value problem. I think you will find that this issue will only get more important over time as more and more stats are included in the system tables. > I ask this because it sometimes takes up to an hour to run this > utility in the field (when running it once a week), during which the > database is locked - I guess this would suggest that we should > balance 'running it more often and locking for less time' against > 'running it less often and locking for more time' ? You would have to test this but I'm not sure that running it more frequently will actually save you anytime. This is because to do the stats update I suspect it scans each table and index calculating the stats each time. It doesnt start from where it finished last time and add in the changes - I'm not sure this would be possible for some stats and doubt it would be worth while anyway. The way I normally suggest that people get round the problem of it taking to much time is not to run a full update stats. Instead you choose groups of preferably related tables that will be involved in joins and searches and update stats on each group at a different time. This cuts down on the length of time the database has to be locked at any one time and also allows you to leave out the static tables which rarely change. These static tables can then have their stats updated ad-hoc whenever there has been significant change. > Does any-one know why it has to lock the entire database? No, I have no idea. I cannot see why it would matter if the stats aren't absolutely perfect because a few inserts and deletes occurred while they were being calculated. It would be upto the DBA to choose a quite time when they could be run - it would be stupid to run them during a mass load for instance. But the DBA has to make this choice anyway. Perhaps someone else could enlighten us? > Thanks in advance, > > Richard Ridley No problem, mate. Cheers - Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------