Update Statistics

I've seen the recommendation to do update statistics on ids on this list over the last couple months in certain situations. Sadly I know little about administering IDS but I've muddle through so far. I was wondering if someone could do a quick tutorial about update statistics covering things like when to use it and why to use it and how often to use it. Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu> ========================== SLES 10 SP2 & IDS 10.0 HC9 " I would love to change the world, but they won't give me the source code." -- Unknown

UPDATE STATISTICS is a fundamental step for good database performance.Informix query optimizer is not the most feature reach one, but it does a great job. What I mean is that there are some ways to do queries that Informix does not (yet) use. But the optimizer tends to choose the best available plan. BUT, for doing that it's essential that it knows about the data. And that is done through "good" update statistics. The most recent article that I know of, about this subject is http://www.ibm.com/developerworks/data/library/techarticle/dm-0803changappa/inde x.html You should also take a look at the previous "bible": http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller /0203miller.html but this one is obviously a bit outdated (all new stuff in Informix 11.x is not covered). And as usual you MUST take a look at the manual, specifically the Syntax Guide and the Performance Guide. You can use some tools to help you. Automatic update statistics was introduced in Informix 11 and you can take advantage of that using Open Admin Tool (OAT). Besides that Art Kagel has nice tools (dostats). You'll need to compile them. If you prefer the simplicity of shell script I can provide some (I should put them in the IIUG software repository, but time is short - and bad excuses are common - :( ) Regards. On Wed, Mar 3, 2010 at 1:45 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote: > I've seen the recommendation to do update statistics on ids on this list > over > the last couple months in certain situations. Sadly I know little about > administering IDS but I've muddle through so far. I was wondering if > someone > could do a quick tutorial about update statistics covering things like when > to > use it and why to use it and how often to use it. > > Jonathon Wyza > CX & CBORD System Administrator > CX Programmer/Analyst > Administrative Computing > Bethel College > (574)-257-3381 > AIM: Iamwyza > jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu> > ========================== > SLES 10 SP2 & IDS 10.0 HC9 > > " I would love to change the world, but they won't give me the source > code." > -- Unknown > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6d99efabae2850480e69b26

Interesting. The older article talks about memory size for update statistics to use. It also talks about having memory sizes of 50 mb. This seems oddly low, do you normally use higher memory amounts in modern systems? It also talks about not running update statistics "high" on large tables. What is classified as a large table? (ex I think our largest table has something on the order of 6 million rows). Do you based the frequency of when you run update stats on the time it's going to take or the amount of changes made to tables? Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu ========================== SLES 10 SP2 & IDS 10.0 HC9 " I would love to change the world, but they won't give me the source code." -- Unknown -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Wednesday, March 03, 2010 9:51 AM To: ids@iiug.org Subject: Re: Update Statistics [19171] UPDATE STATISTICS is a fundamental step for good database performance.Informix query optimizer is not the most feature reach one, but it does a great job. What I mean is that there are some ways to do queries that Informix does not (yet) use. But the optimizer tends to choose the best available plan. BUT, for doing that it's essential that it knows about the data. And that is done through "good" update statistics. The most recent article that I know of, about this subject is http://www.ibm.com/developerworks/data/library/techarticle/dm-0803changappa/inde x.html You should also take a look at the previous "bible": http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller /0203miller.html but this one is obviously a bit outdated (all new stuff in Informix 11.x is not covered). And as usual you MUST take a look at the manual, specifically the Syntax Guide and the Performance Guide. You can use some tools to help you. Automatic update statistics was introduced in Informix 11 and you can take advantage of that using Open Admin Tool (OAT). Besides that Art Kagel has nice tools (dostats). You'll need to compile them. If you prefer the simplicity of shell script I can provide some (I should put them in the IIUG software repository, but time is short - and bad excuses are common - :( ) Regards. On Wed, Mar 3, 2010 at 1:45 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote: > I've seen the recommendation to do update statistics on ids on this list > over > the last couple months in certain situations. Sadly I know little about > administering IDS but I've muddle through so far. I was wondering if > someone > could do a quick tutorial about update statistics covering things like when > to > use it and why to use it and how often to use it. > > Jonathon Wyza > CX & CBORD System Administrator > CX Programmer/Analyst > Administrative Computing > Bethel College > (574)-257-3381 > AIM: Iamwyza > jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu> > ========================== > SLES 10 SP2 & IDS 10.0 HC9 > > " I would love to change the world, but they won't give me the source > code." > -- Unknown > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6d99efabae2850480e69b26 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Take the suggestions to read the Performance Guide and the two white papers someone already posted, but here's a quick and dirty tutorial: - The optimizer needs statistical data on the contents of tables to decide on the best query path. Which table to select first, which to join to next, etc. whether to use an index (and if so which one) or sequential scan or to scan a table into a hash table to speed searches or even whether to create a temporary index. - At the base level, UPDATE STATISTICS LOW provides each table's row count, the second smallest and second largest value for each column in the table, and the size and depth of each index's B+Tree. This is sufficient to allow the optimizer to choose between indexes and to perform gross table ordering. This usually results in reasonable performance for most simple queries involving fewer than 4 tables. The optimizer, however, doesn't have enough information to change the query plan depending on specific filter values included in the query. It's a "one size fits all" strategy. At this level, the optimizer tends to choose an index key matching an ORDER BY clause because it can't determine if the sort would be faster because it cannot correctly estimate the number of rows to be returned. - UPDATE STATISTICS MEDIUM provides data distributions for those columns against which it is run using statistical sampling. By default only a small number of rows are sampled. This mainly improves the table ordering in more complex queries but does not normally permit the optimizer to correctly choose one index over another or to choose an index for filter or join efficiency rather than return order efficiency. The optimizer's estimate of the number of rows returned is better than under LOW only, but not perfect. The new SAMPLING option can improve the quality of MEDIUM statistics by increasing the sampling size. Run time is significantly longer than for LOW. - UPDATE STATISTICS HIGH provides exact data distribution information for those columns against which it is run (at least as of the moment it was started) as it reads and quantifies every row in the table. This can take much longer than either LOW or MEDIUM statistics but results in the optimizer making better decisions. The optimizer's estimate of the number of rows to be returned is rarely off by more than a few. It is normally only necessary to create HIGH statistics for the lead columns of each index key and for commonly joined non-indexed columns. Additionally it can be helpful to generate HIGH statistics for columns used in table and index fragmentation expressions and if you have two or more compound indexes that begin with the same sublist of columns it is good to also generate HIGH statistics for the first column that is different in each index. - UPDATE STATISTICS FOR PROCEDURE/FUNCTION/ROUTINE recompiles SPL stored procedures thereby creating a new query plan for the procedure. This is required after changes to tables that are accessed by the procedure by ALTERing the table to add, drop, or modify columns or after running UPDATE STATISTICS on those tables. The engine will automatically recompile the procedure the first time it is executed after the table has changed (the change marks the procedure's query plan invalid) but that will add to the procedure's runtime, so it is good to recompile it manually immediately after modifying a table that the procedure references. - Index builds in 11.50 automatically calculate LOW statistics for you (earlier engines did not do that). - The IDS Automatic Update Statistics feature (or AUS) uses the IDS task scheduler to run a set of stored procedures and Java User Defined Functions (UDFs) to perform this task for you periodically and automatically. - How often to run update stats? Tough question, YMMV but in general when there are significant changes to the number of rows in a table or in the relative percentages of rows in a table with a particular range of values then you need to recalculate the statistical distributions. AUS and dostats (see below) figure this out for you. Finally, as noted by another poster, get my dostats utility, which is in the package utils2_ak which you can download from the IIUG Software Repository for free. It implements the protocol of commands as recommended in the Performance Guide and with the recommendations made by John Miller in his white paper. Dostats provides many features that AUS does not include. Dostats can even schedule update statistics runs using the IDS task scheduler feature just as AUS does, so it can be used as an AUS replacement if you like its features better. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 3, 2010 at 8:45 AM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote: > I've seen the recommendation to do update statistics on ids on this list > over > the last couple months in certain situations. Sadly I know little about > administering IDS but I've muddle through so far. I was wondering if > someone > could do a quick tutorial about update statistics covering things like when > to > use it and why to use it and how often to use it. > > Jonathon Wyza > CX & CBORD System Administrator > CX Programmer/Analyst > Administrative Computing > Bethel College > (574)-257-3381 > AIM: Iamwyza > jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu> > ========================== > SLES 10 SP2 & IDS 10.0 HC9 > > " I would love to change the world, but they won't give me the source > code." > -- Unknown > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce00a84f889f30480e894fb

OK, older versions of Informix (7.xx, 9.xx) depended on the user's environment variable DBUPSPACE which controlled the amount of memory that IDS would allow a single session to use for in-memory sorting if parallel query was not enabled (setting PDQPRIORITY > 0 or running SET PDQPRIORITY within the session). DBUPSPACE defaulted to 15MB with a maximum of 50MB. If you were running under PDQPRIORITY, then the amount of memory available was governed by the DSS memory configuration parameters in the ONCONFIG file. These include DS_TOTAL_MEMORY (total memory for all DSS or parallel queries), DS_MAX_QUERIES (the number of active DSS or parallel queries that can run, and PDQPRIORITY (the percentage of server resources that the session wants access to). The resulting amount of memory available to the parallel session for in-memory sorting and other purposes (known in the documentation as a 'quanta') is essentially limited only by the system memory and the DBA's configuration. In 11.10 IBM introduced the new ONCONFIG parameter DS_NONPDQ_QUERY_MEM which now overrides DBUPSPACE for nonparallel queries (ie PDQPRIORITY=0). That can be set to anything from zero to the size of the quanta determined by the interaction of DS_TOTAL_MEMORY and DS_MAX_QUERIES. Unfortunately it defaults to 128KB not 15MB. One has to make certain to configure DS_NONPDQ_QUERY_MEM up to more reasonable levels for real-world applications. In truth, neither DBUPSPACE nor DS_NONPDQ_QUERY_MEM are a consideration for real index builds or for UPDATE STATISTICS processing which should always be carried out using PDQPRIORITY set as high as one can without impacting other running tasks or production queries. Also note the importance, mentioned in passing, in John's article, of the parallel sort environment variables PSORT_NPROCS and PSORT_DBTEMP. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 3, 2010 at 10:14 AM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote: > Interesting. The older article talks about memory size for update > statistics > to use. It also talks about having memory sizes of 50 mb. This seems oddly > low, do you normally use higher memory amounts in modern systems? It also > talks about not running update statistics "high" on large tables. What is > classified as a large table? (ex I think our largest table has something on > the order of 6 million rows). Do you based the frequency of when you run > update stats on the time it's going to take or the amount of changes made > to > tables? > > Jonathon Wyza > CX & CBORD System Administrator > CX Programmer/Analyst > Administrative Computing > Bethel College > (574)-257-3381 > AIM: Iamwyza > jonathon.wyza@bethelcollege.edu > ========================== > SLES 10 SP2 & IDS 10.0 HC9 > > " I would love to change the world, but they won't give me the source > code." > -- Unknown > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando > Nunes > Sent: Wednesday, March 03, 2010 9:51 AM > To: ids@iiug.org > Subject: Re: Update Statistics [19171] > > UPDATE STATISTICS is a fundamental step for good database performance.> Informix query optimizer is not the most feature reach one, but it does a > great job. What I mean is that there are some ways to do queries that > Informix does not (yet) use. But the optimizer tends to choose the best > available plan. BUT, for doing that it's essential that it knows about the > data. And that is done through "good" update statistics. > > The most recent article that I know of, about this subject is > > > > http://www.ibm.com/developerworks/data/library/techarticle/dm-0803changappa/inde x.html > > You should also take a look at the previous "bible": > > > > http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller /0203miller.html > > but this one is obviously a bit outdated (all new stuff in Informix 11.x is > not covered). > And as usual you MUST take a look at the manual, specifically the Syntax > Guide and the Performance Guide. > > You can use some tools to help you. Automatic update statistics was > introduced in Informix 11 and you can take advantage of that using Open > Admin Tool (OAT). Besides that Art Kagel has nice tools (dostats). You'll > need to compile them. > If you prefer the simplicity of shell script I can provide some (I should > put them in the IIUG software repository, but time is short - and bad > excuses are common - :( ) > > Regards. > > On Wed, Mar 3, 2010 at 1:45 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu > >wrote: > > > I've seen the recommendation to do update statistics on ids on this list > > over > > the last couple months in certain situations. Sadly I know little about > > administering IDS but I've muddle through so far. I was wondering if > > someone > > could do a quick tutorial about update statistics covering things like > when > > to > > use it and why to use it and how often to use it. > > > > Jonathon Wyza > > CX & CBORD System Administrator > > CX Programmer/Analyst > > Administrative Computing > > Bethel College > > (574)-257-3381 > > AIM: Iamwyza > > jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu> > > ========================== > > SLES 10 SP2 & IDS 10.0 HC9 > > > > " I would love to change the world, but they won't give me the source > > code." > > -- Unknown > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --0016e6d99efabae2850480e69b26 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517402aaefb3c950480ea6bfe

While I do agree that 50MB is only a moderate size, this was mainly a security item. How much memory do you want a user to grab without requiring any access control. You can go above 50MB, but that require= s PDQPRIORITY, which is governed by the onconfig parameters. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) = From: "Wyza, Jonathon" <wyzaj@bethelcollege.edu> = = To: ids@iiug.org = = Date: 03/03/2010 07:14 AM = = Subject: RE: Update Statistics [19172] = = Sent by: ids-bounces@iiug.org = = Interesting. The older article talks about memory size for update statistics to use. It also talks about having memory sizes of 50 mb. This seems od= dly low, do you normally use higher memory amounts in modern systems? It al= so talks about not running update statistics "high" on large tables. What = is classified as a large table? (ex I think our largest table has somethin= g on the order of 6 million rows). Do you based the frequency of when you ru= n update stats on the time it's going to take or the amount of changes ma= de to tables? Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D SLES 10 SP2 & IDS 10.0 HC9 " I would love to change the world, but they won't give me the source code." -- Unknown -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Wednesday, March 03, 2010 9:51 AM To: ids@iiug.org Subject: Re: Update Statistics [19171] UPDATE STATISTICS is a fundamental step for good database performance.Informix query optimizer is not the most feature reach one, but it does= a great job. What I mean is that there are some ways to do queries that Informix does not (yet) use. But the optimizer tends to choose the best= available plan. BUT, for doing that it's essential that it knows about = the data. And that is done through "good" update statistics. The most recent article that I know of, about this subject is http://www.ibm.com/developerworks/data/library/techarticle/dm-0803chang= appa/index.html You should also take a look at the previous "bible": http://www.ibm.com/developerworks/data/zones/informix/library/techartic= le/miller/0203miller.html but this one is obviously a bit outdated (all new stuff in Informix 11.= x is not covered). And as usual you MUST take a look at the manual, specifically the Synta= x Guide and the Performance Guide. You can use some tools to help you. Automatic update statistics was introduced in Informix 11 and you can take advantage of that using Open= Admin Tool (OAT). Besides that Art Kagel has nice tools (dostats). You'= ll need to compile them. If you prefer the simplicity of shell script I can provide some (I shou= ld put them in the IIUG software repository, but time is short - and bad excuses are common - :( ) Regards. On Wed, Mar 3, 2010 at 1:45 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote: > I've seen the recommendation to do update statistics on ids on this l= ist > over > the last couple months in certain situations. Sadly I know little abo= ut > administering IDS but I've muddle through so far. I was wondering if > someone > could do a quick tutorial about update statistics covering things lik= e when > to > use it and why to use it and how often to use it. > > Jonathon Wyza > CX & CBORD System Administrator > CX Programmer/Analyst > Administrative Computing > Bethel College > (574)-257-3381 > AIM: Iamwyza > jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.ed= u> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D > SLES 10 SP2 & IDS 10.0 HC9 > > " I would love to change the world, but they won't give me the source= > code." > -- Unknown > > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e6d99efabae2850480e69b26 ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =

Hi, I see you have already got good information and sugessions. you can also check http://www-01.ibm.com/support/docview.wss?uid=swg21137764 Hope it helps! Regards Vikas ******************************************************************************* I've seen the recommendation to do update statistics on ids on this list over the last couple months in certain situations. Sadly I know little about administering IDS but I've muddle through so far. I was wondering if someone could do a quick tutorial about update statistics covering things like when to use it and why to use it and how often to use it. Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu> ========================== SLES 10 SP2 & IDS 10.0 HC9 " I would love to change the world, but they won't give me the source code." -- Unknown