update statistics low drop distributions

Hello, Last summer we updated Informix 7 to Informix 10. We installed both versions next to each other and did an im/export of the data by dumps through the "BaaN IV" application. We didn't migrate the data through Informix update. Our database is 80Gb large and runs on an IBM Power 5 with 4 CPU's and 6Gb of Ram running AIX 5.2. Since then we are running update statistics every night, but these still seem to run longer. We run it in the following order: > update statistics low drop distributionsThis takes for about 8 hours! > update statistics medium for table table(column)> ........ for other table/columns This takes 15 till 30 minutes > update statistics high for table table(column)> ........ for other table/columns This takes for about 4 hours We are wondering if the low drop distributions has to run every night. In the documentation we have found it is said that the drop distributions only has to run after a database migration. Anybody an advise?

Hi,- update statistics (low) for <table> drop distributions is important to: update the column 'nrows' in systables and delete all information in sysdistrib table for that 'table' - update statistics medium for table (columns that are components of index but not the FIRST column in index) - update statistics high for table (first column in index) If you know about tables that has almost never 'delete', 'insert' and few 'update' (in index columns), you should do only once a month update statistics for these tables... (in general these are small tables) or historically tables. Don't run update statistics high or medium for columns that not belong to indexes !!! When you run update statistics low, remember: the engine will read all data (all data pages) for that table. If the column is in index, the engine read all index pages for that index. Is cool if you run 'update statistics high' for all columns that belong of catalog tables (tableid < 100) at once a month. Hope this help. Best regards. Roberto Ferronato > To: ids@iiug.org> From: ekuijpers@berkvens.nl> Subject: update statistics low drop distributions [10525]> Date: Thu, 29 Nov 2007 05:18:19 -0500> > Hello, > > Last summer we updated Informix 7 to Informix 10. We installed both versions > next to each other and did an im/export of the data by dumps through the "BaaN > IV" application. We didn't migrate the data through Informix update. Our > database is 80Gb large and runs on an IBM Power 5 with 4 CPU's and 6Gb of Ram > running AIX 5.2. > > Since then we are running update statistics every night, but these still seem > to run longer. We run it in the following order: > > > update statistics low drop distributions > This takes for about 8 hours! > > > update statistics medium for table table(column) > > ........ for other table/columns > This takes 15 till 30 minutes > > > update statistics high for table table(column) > > ........ for other table/columns > This takes for about 4 hours > > We are wondering if the low drop distributions has to run every night. In the > documentation we have found it is said that the drop distributions only has to > run after a database migration. > > Anybody an advise? _________________________________________________________________ Connect to the next generation of MSN Messenger http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=wlmai ltagline

Thanks for your quick reply. Regarding the index columns we are indeed doing what you mentioned: - update statistics medium for table (columns that are components of index but not the FIRST column in index) - update statistics high for table (first column in index) The only thing is that the "update statistics low drop distributions" doesn't mention any table in our situation. And this also runs for 8 hours. Is this necessary?

ERIK KUIJPERS wrote: Hi Erik, No, you do not have to run the global LOW DROP DISTRIBUTIONS. Also, IDS 10 and later releases of 9.30xC2+ and 7.31xD3+ use a different algorithm internally for gathering stats information than earlier releases. If you rearrange your statements to take advantage of that and also use PDQPRIORITY and set MGM memory settings correctly in the ONCONFIG file the stats will actually run faster. One upshot of the change is that it's faster to UPDATE STATISTICS on more columns than it used to be. Read John Miller IIIs white paper on the subject (don't have the link handy but I've posted it to CDI and this forum several times this year for others) it details how to take advantage of the improved algorithms. My dostats utility automatically adjusts the statement column lists for best effect based on the engine version. You could try just using dostats which is contained in the package utils2_ak from the IIUG Software Repository. Also, in addition to the MEDIUMs on non-index leading columns and the HIGHs you should be performing a LOW (without the DROP DISTRIBUTIONS clause) on each fill key column list for each table. This is to maintain the low level stats stored in the systables and sysindices tables. Art S. Kagel > Thanks for your quick reply. > > Regarding the index columns we are indeed doing what you mentioned: > - update statistics medium for table > (columns that are components of index but not the FIRST column in index) > - update statistics high for table > (first column in index) > > The only thing is that the "update statistics low drop distributions" doesn't > mention any table in our situation. And this also runs for 8 hours. Is this > necessary? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

I suggest you run only once a week update stats low drop distributions for all tables !If you have some tables that have a lot of rows inserted or deleted every day, try to run US (update statistics) for that table (or these tables) only (every day). Remember to have a statement: "set isolation to dirty read" in the script of US if you have users updating data when US is running ! BR RFo > To: ids@iiug.org> From: ekuijpers@berkvens.nl> Subject: Re: RE: update statistics low drop distributions [10527]> Date: Thu, 29 Nov 2007 05:49:56> > Thanks for your quick reply. > > Regarding the index columns we are indeed doing what you mentioned: > - update statistics medium for table > (columns that are components of index but not the FIRST column in index) > - update statistics high for table > (first column in index) > > The only thing is that the "update statistics low drop distributions" doesn't > mention any table in our situation. And this also runs for 8 hours. Is this > necessary? _________________________________________________________________ Discover the new Windows Vista http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE

Thanks both for your input. I read the article of John Miller (again) and with your explanation it seems to get clearer to me now. What was confusing is the difference between the US low and the medium/high. Why didn't they call it "Update Statistics" and "Update Distributions"? Low -> Updates Statistics (sysindexes, syscolumns) Medium/High -> Updates Distributions (sysdistrib) Did download the utils2_ak before, but it has to be compiled, and we only have got one production machine. This makes it also difficult to change the current US settings. We have changed this once, and then we had a complete morning suffering slow performance (long running queries because of tablescans, people starting double sessions, etc). Around new-year (vacation) I will test with the dostats utility. For the time being I will try to do the US the following way: Sunday: US low drop distributions US Medium/High Weekdays: US Medium/High Good idea?

ERIKThe US is a whoole activity. When you run US with 'drop distributions', it 'delete' all information about that (user) table from sysdistrib (catalog) table because you know that you will rebuilt it running US medium/high."update distributions" means you'll run US high/medium without 'drop distributions' before... YES: > Sunday: > US low drop distributions > US Medium/High > > Weekdays: > US Medium/High It's a good idea !! R Ferronato > To: ids@iiug.org> From: ekuijpers@berkvens.nl> Subject: RE: update statistics low drop distributions [10551]> Date: Fri, 30 Nov 2007 04:34:53 -0500> > Thanks both for your input. > > I read the article of John Miller (again) and with your explanation it seems > to get clearer to me now. What was confusing is the difference between the US > low and the medium/high. Why didn't they call it "Update Statistics" and > "Update Distributions"? > Low -> Updates Statistics (sysindexes, syscolumns) > Medium/High -> Updates Distributions (sysdistrib) > > Did download the utils2_ak before, but it has to be compiled, and we only have > got one production machine. This makes it also difficult to change the current > US settings. We have changed this once, and then we had a complete morning > suffering slow performance (long running queries because of tablescans, people > starting double sessions, etc). > Around new-year (vacation) I will test with the dostats utility. > > For the time being I will try to do the US the following way: > Sunday: > US low drop distributions > US Medium/High > > Weekdays: > US Medium/High > > Good idea? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ News, entertainment and everything you care about at Live.com. Get it now! http://www.live.com/getstarted.aspx

ERIK KUIJPERS wrote: > Thanks both for your input. > > I read the article of John Miller (again) and with your explanation it seems > to get clearer to me now. What was confusing is the difference between the US > low and the medium/high. Why didn't they call it "Update Statistics" and > "Update Distributions"? > Low -> Updates Statistics (sysindexes, syscolumns) > Medium/High -> Updates Distributions (sysdistrib) > I can concur with that. The existing US from Online 5.xx was equivalent to LOW and that would have been the perfect opportunity to just create new UPDATE DISTRIBUTIONS or GATHER DISTRIBUTIONS and DROP DISTRIBUTIONS statements. But they didn't so we're stuck with what we have. > Did download the utils2_ak before, but it has to be compiled, and we only have > Note that you don't have to compile or even run dostats on the same machine as the server. If the OS versions of your test and production machines are different you can produce a static linked version so it's not dependent on the shared library releases. > got one production machine. This makes it also difficult to change the current > US settings. We have changed this once, and then we had a complete morning > suffering slow performance (long running queries because of tablescans, people > starting double sessions, etc). > Around new-year (vacation) I will test with the dostats utility. > > For the time being I will try to do the US the following way: > Sunday: > US low drop distributions > US Medium/High > > Weekdays: > US Medium/High > Unless your DB is HIGHLY volatile and the relative weights of various key values within the distributions change daily I would think that updating only on Sundays would generally be sufficient. Once you are running dostats, you can run it with the -a -A 7 and -b -B 5 options daily and it will generally only select a very few tables to update each day. Then on the weekly run on Sundays all of the tables not updated since the preceding weekend will be selected by the ' -a -A 7 ' aging options for update then. Art S. Kagel > Good idea? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >