update statistics

Posted in 2012

Summary

Frank (IDS 11.50.FC8) asked whether running a single database-wide UPDATE STATISTICS differs from running it per table plus for procedures/routines/functions. Art Kagel said the two are effectively equivalent, but noted both only refresh basic catalog stats (no distributions) and recommended UPDATE STATISTICS MEDIUM/HIGH per the Performance Guide, John Miller's developerWorks article, his dostats utility, or the built-in AUS scheduler task. John Miller added the only real difference is locking/transaction scope, relevant with ANSI logging or an explicit BEGIN WORK. Frank still reported seeing better results from the single-statement form, so that observation was left unexplained.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

FRANK — Thu, 06 Sep 2012 20:31:04 — source: IIUG Forums & Mailing Lists

Hi, Folks, IDS11.50 FC8 Assume a database has 100 tables: tab1,tab2,....tab100. What are the Major differences between the following two approaches for updating statistics? Approach 1: Just run a "update statistic" for whole database, update statistics; Approach 2: Run update statistics for each single table, plus for routine/function/procedure, update statistics for table tab1; update statistics for table tab2; ........................... update statistics for table tab100; update statistics for procedure; update statistics for routine; update statistics for function; Thanks, Frank --14dae93407a56c139f04c90e5ed6

↪ replying to FRANK

Fernando Nunes — Thu, 06 Sep 2012 20:36:30 — source: IIUG Forums & Mailing Lists

Both of them are completely wrong because they don't follow recommendations. Check performance guide. On Sep 6, 2012 9:31 PM, "FRANK" <yunyaoqu@gmail.com> wrote: > Hi, Folks, > > IDS11.50 FC8 > > Assume a database has 100 tables: tab1,tab2,....tab100. > > What are the Major differences between the following two approaches for > updating statistics? > > Approach 1: Just run a "update statistic" for whole database, > > update statistics;> > Approach 2: Run update statistics for each single table, plus for > routine/function/procedure, > > update statistics for table tab1; > update statistics for table tab2;> > ............................ > > update statistics for table tab100;> > update statistics for procedure;> > update statistics for routine;> > update statistics for function;> > Thanks, > Frank > > --14dae93407a56c139f04c90e5ed6 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00248c711bb9d1dace04c90e71b2

↪ replying to FRANK

Art Kagel — Thu, 06 Sep 2012 20:45:20 — source: IIUG Forums & Mailing Lists

As you have shown it below, there is no difference. However, read the section of the Informix Performance Guide on Update Statistics and John MIller III's article on the subject ( http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller /0203miller.html) in IBM DeveloperWorks. The commands you suggest, either way, will only update the basic stats in the system catalog tables systables, syscolumns, and sysindices/sysindexes they will not build data distributions in sysdistrib and sysfragdist. To do that you must run UPDATE STATISTICS MEDIUM and/or UPDATE STATISTICS HIGH. The best distributions will be build by running HIGH for every column in every table, but that is impractical because it will be time and resource consuming. The sources I mentioned propose a protocol to get data distributions of sufficient quality (for most circumstances) for the optimizer to make accurate decisions about query plans while reducing resource needed and time taken. Alternatively, just get my dostats utility which is the gold standard for creating data distributions for your databases with little or no work on your part. Dostats is used in hundreds of Informix sites around the world and implements the recommendations in the sources above with many additional features. Dostats is included in the package utils2_ak which you can download and use free from the IIUG Software Repository. Also note that as you are running 11.xx, you also have the Auto Update Statistics (AUS) tasks running every night in the database scheduler performing MOST of the recommended protocol. I have reservations about AUS (see my blob post about that at: http://informix-myview.blogspot.com/2011/05/aus-versus-dostats.html), but is will do a better job than the simple commands in your posting. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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 Thu, Sep 6, 2012 at 4:31 PM, FRANK <yunyaoqu@gmail.com> wrote: > Hi, Folks, > > IDS11.50 FC8 > > Assume a database has 100 tables: tab1,tab2,....tab100. > > What are the Major differences between the following two approaches for > updating statistics? > > Approach 1: Just run a "update statistic" for whole database, > > update statistics;> > Approach 2: Run update statistics for each single table, plus for > routine/function/procedure, > > update statistics for table tab1; > update statistics for table tab2;> > ............................ > > update statistics for table tab100;> > update statistics for procedure;> > update statistics for routine;> > update statistics for function;> > Thanks, > Frank > > --14dae93407a56c139f04c90e5ed6 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340903ef43f304c90e904e

↪ replying to Fernando Nunes

FRANK — Thu, 06 Sep 2012 21:05:32 — source: IIUG Forums & Mailing Lists

We believe they are both syntactically and semantically correct, IDS allowed? Not agree? We ask the difference, not the quality of their results. Thanks, Frank On Thu, Sep 6, 2012 at 4:36 PM, Fernando Nunes <domusonline@gmail.com>wrote: > Both of them are completely wrong because they don't follow > recommendations. Check performance guide. > On Sep 6, 2012 9:31 PM, "FRANK" <yunyaoqu@gmail.com> wrote: > > > Hi, Folks, > > > > IDS11.50 FC8 > > > > Assume a database has 100 tables: tab1,tab2,....tab100. > > > > What are the Major differences between the following two approaches for > > updating statistics? > > > > Approach 1: Just run a "update statistic" for whole database, > > > > update statistics;> > > > Approach 2: Run update statistics for each single table, plus for > > routine/function/procedure, > > > > update statistics for table tab1; > > update statistics for table tab2;> > > > ............................ > > > > update statistics for table tab100;> > > > update statistics for procedure;> > > > update statistics for routine;> > > > update statistics for function;> > > > Thanks, > > Frank > > > > --14dae93407a56c139f04c90e5ed6 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --00248c711bb9d1dace04c90e71b2 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340cebddd3ec04c90ed90f

↪ replying to FRANK

Art Kagel — Thu, 06 Sep 2012 21:14:19 — source: IIUG Forums & Mailing Lists

No quantitative or qualitative difference, correct. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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 Thu, Sep 6, 2012 at 5:05 PM, FRANK <yunyaoqu@gmail.com> wrote: > We believe they are both syntactically and semantically correct, IDS > allowed? Not agree? > > We ask the difference, not the quality of their results. > > Thanks, > Frank > > On Thu, Sep 6, 2012 at 4:36 PM, Fernando Nunes <domusonline@gmail.com > >wrote: > > > Both of them are completely wrong because they don't follow > > recommendations. Check performance guide. > > On Sep 6, 2012 9:31 PM, "FRANK" <yunyaoqu@gmail.com> wrote: > > > > > Hi, Folks, > > > > > > IDS11.50 FC8 > > > > > > Assume a database has 100 tables: tab1,tab2,....tab100. > > > > > > What are the Major differences between the following two approaches for > > > updating statistics? > > > > > > Approach 1: Just run a "update statistic" for whole database, > > > > > > update statistics;> > > > > > Approach 2: Run update statistics for each single table, plus for > > > routine/function/procedure, > > > > > > update statistics for table tab1; > > > update statistics for table tab2;> > > > > > ............................ > > > > > > update statistics for table tab100;> > > > > > update statistics for procedure;> > > > > > update statistics for routine;> > > > > > update statistics for function;> > > > > > Thanks, > > > Frank > > > > > > --14dae93407a56c139f04c90e5ed6 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --00248c711bb9d1dace04c90e71b2 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --14dae9340cebddd3ec04c90ed90f > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93411194acad004c90ef91b

↪ replying to FRANK

John Miller iii — Thu, 06 Sep 2012 21:21:28 — source: IIUG Forums & Mailing Lists

To be total accurate there is a difference between the two scenarios. The difference deals with locking and transaction scope. If you are not using ANSI logging mode and do not do a begin work before the update stats command then I would see no difference. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 09/06/2012 02:05:32 PM: > From: "FRANK" <yunyaoqu@gmail.com> > To: ids@iiug.org, > Date: 09/06/2012 02:08 PM > Subject: Re: update statistics [28249] > Sent by: ids-bounces@iiug.org > > We believe they are both syntactically and semantically correct, IDS > allowed? Not agree? > > We ask the difference, not the quality of their results. > > Thanks, > Frank > > On Thu, Sep 6, 2012 at 4:36 PM, Fernando Nunes <domusonline@gmail.com>wrote: > > > Both of them are completely wrong because they don't follow > > recommendations. Check performance guide. > > On Sep 6, 2012 9:31 PM, "FRANK" <yunyaoqu@gmail.com> wrote: > > > > > Hi, Folks, > > > > > > IDS11.50 FC8 > > > > > > Assume a database has 100 tables: tab1,tab2,....tab100. > > > > > > What are the Major differences between the following two approaches for > > > updating statistics? > > > > > > Approach 1: Just run a "update statistic" for whole database, > > > > > > update statistics;> > > > > > Approach 2: Run update statistics for each single table, plus for > > > routine/function/procedure, > > > > > > update statistics for table tab1; > > > update statistics for table tab2;> > > > > > ............................ > > > > > > update statistics for table tab100;> > > > > > update statistics for procedure;> > > > > > update statistics for routine;> > > > > > update statistics for function;> > > > > > Thanks, > > > Frank > > > > > > --14dae93407a56c139f04c90e5ed6 > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --00248c711bb9d1dace04c90e71b2 > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --14dae9340cebddd3ec04c90ed90f > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to John Miller iii

FRANK — Thu, 06 Sep 2012 22:00:51 — source: IIUG Forums & Mailing Lists

Thanks, John and Art! The original question came from one of our tests. We found the two approaches might be Different ( or yielded different statistics data). The reason is , for the same set of jobs, approach one( one update statistics statement for whole database) seems giving a better or more complete descriptive statistical data, which made jobs run faster( compared to the second approach) ! Anyway, just for curiosity . Thanks again Frank On Thu, Sep 6, 2012 at 5:21 PM, John Miller iii <miller3@us.ibm.com> wrote: > To be total accurate there is a difference between the two scenarios. > > The difference deals with locking and transaction scope. > > If you are not using ANSI logging mode and do not do a begin work > before the update stats command then I would see no difference. > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 09/06/2012 02:05:32 PM: > > > From: "FRANK" <yunyaoqu@gmail.com> > > To: ids@iiug.org, > > Date: 09/06/2012 02:08 PM > > Subject: Re: update statistics [28249] > > Sent by: ids-bounces@iiug.org > > > > We believe they are both syntactically and semantically correct, IDS > > allowed? Not agree? > > > > We ask the difference, not the quality of their results. > > > > Thanks, > > Frank > > > > On Thu, Sep 6, 2012 at 4:36 PM, Fernando Nunes > <domusonline@gmail.com>wrote: > > > > > Both of them are completely wrong because they don't follow > > > recommendations. Check performance guide. > > > On Sep 6, 2012 9:31 PM, "FRANK" <yunyaoqu@gmail.com> wrote: > > > > > > > Hi, Folks, > > > > > > > > IDS11.50 FC8 > > > > > > > > Assume a database has 100 tables: tab1,tab2,....tab100. > > > > > > > > What are the Major differences between the following two approaches > for > > > > updating statistics? > > > > > > > > Approach 1: Just run a "update statistic" for whole database, > > > > > > > > update statistics;> > > > > > > > Approach 2: Run update statistics for each single table, plus for > > > > routine/function/procedure, > > > > > > > > update statistics for table tab1; > > > > update statistics for table tab2;> > > > > > > > ............................ > > > > > > > > update statistics for table tab100;> > > > > > > > update statistics for procedure;> > > > > > > > update statistics for routine;> > > > > > > > update statistics for function;> > > > > > > > Thanks, > > > > Frank > > > > > > > > --14dae93407a56c139f04c90e5ed6 > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --00248c711bb9d1dace04c90e71b2 > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --14dae9340cebddd3ec04c90ed90f > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93407a5b216ca04c90f9fb9

Glossary

UPDATE STATISTICS