Re: Various Questions

>From: uunet!enuucp.eas.asu.edu!anasaz!qip.naomi (Naomi Walker) >Subject: Various Questions >Date: Fri, 10 Jul 92 14:39:09 MST >X-Informix-List-Id: <list.1309> > >A few questions for anyone that might know: >1) Update Statistics > > a) What sort of regularity would you suggest for updating statistics > on a production system, with lots of activity, with at least 22 hours > a day of active time? It depends, as always... It depends on whether the changes that you make over the 22-hour period make a significant difference to the statistics. If you have a 1 million row table, but you change 10,000 rows, add 10,000 rows and delete 10,000 rows every 24 hours, and you do not normally alter the extreme values in the indexed columns, then you don't need to worry about running UPDATE STATISTICS on the table, because the details don't change all that much. On the other hand, if you change all the rows in a 10,000 row table during the day, then it would (probably) be worth running UPDATE STATISTICS on it at least daily, possibly more often. This would be doubly true of a table which is cleared down (emptied) each night which had UPDATE STATISTICS run on it in its empty state. You would need to do an UPDATE STATISTICS on it after it had accumulated some data, because this would make a big difference to the way the optimizer views the table. There is a lot of difference between having 0 rows and 1 row in the table, and between 1 and 100 rows, and between 100 and 10,000 rows, and between 10,000 and 1,000,000 rows, but not a lot between 1,000,000 and 1,01,000 rows. This means that if the table changes radically, UPDATE STATISTICS is important, whereas if it only changes a little, UPDATE STATISTICS is not critical, but little is proportional to the amount of data in the table. > Last time I updated stats, it took 45 minutes, so its probably not > reasonable to run this on a regular basis. No, but it may be sensible to schedule: UPDATE STATISTICS FOR TABLE Xyz for every table but the biggest 1 (2-10, or whatever) at least daily, especially if they're volatile. Save the biggest tables for your overnight, 2-hour pause, or schedule them 1 each day of the week. Be inventive: devise a schedule to suit the volatility of the table. And yes, big tables are difficult to handle, period. Any system. You need to think carefully before fiddling with a 3 GB table. Would you treat a 3 kB table the same as a 3 MB table? Probably not. Why treat a 3 MB table the same as a 3 GB table, because the order of magnitude difference is the same. > b) Is it safe to abort an update statistics? There is no obvious reason why aborting update statistics should be dangerous. That said, I haven't tried it on major databases. Historically? On SE, it was always pretty quick; all it did was open the index file of each table, read a value from the header information, and copy that into Systables.Nrows. With OnLine, I am not aware of any problems with UPDATE STATISTICS, but that may simply be ignorance. > c) Why is it necessary to update statistics, when isql table status > shows the correct number of rows? If you understand the information UPDATE STATISTICS gathers, you will understand whether it is necessary. It collects data about the number of rows in the table, and also collects information about the degree of uniqueness of each index, and about the (second) minimum and maximum values in the indexed column (where the index is on a single column). Someone will add a comment (in due course, no doubt) about the other statistical items it collects if I've forgotten any, but those are the main ones. So, if Systables.Nrows is accurate, it is still possible for the information about the ranges and uniqueness of the indexes to be inaccurate. But if they are only slightly inaccurate, it doesn't matter. >2) Installing 4.1 Online on a Pyramid 9845 > > a) We are currently running 4.0 Online on our Pyramid 9845, with Osx5.0. > We have been told that 4.1 cannot be installed unless we upgrade the > the operating system to Osx5.1. Can someone please explain to me > the details of why this is true. Because the 4.10 product was ported to Osx5.1, we cannot guarantee that the code is backwards compatible with Osx5.0. Now, if Pyramid can guarantee that the changes they made between the releases were all backwards compatible (so no matter how we used the system, we couldn't see the difference within the program), then it would work OK. But because we didn't port to Osx5.0, we cannot guarantee this. You can try it if you like, but it cannot be supported. >3) Fast Indexing Can't help. 4) Create Index and Locks > > What would you suggest as the procedure for adding keys to tables > that have over a million rows? The obvious answer is to do the design right in the first place. Having said that, (a) it isn't always possible, (b) requirements change, and (c) the normal method of loading an empty table is to drop all indexes, load the data, and replace the indexes afterwards, which leads back to adding a key to the fully-loaded table. One technique to consider is taking the database offline, changing its mode to unlogged, building the index, changing the status again, doing the archive necessary to ensure that you can recover, and then bringing the system back up. This cuts down on the amount of junk (beg its pardon, information) that has to be written to the logical logs. Otherwise, you must have enough space in your logs for all the junk (beg...) that gets written while the index is built. This sounds unlikely to be acceptable in your environment, but it is the way I would probably try to do it. It would be quicker than the "forever minus a week or so" that you got by cross-loading the table with all indexes in place. Sorry there aren't any cut-and-dried answers to these questions, Yours sincerely, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>