about update statistics performance ?

Hi , I have somw questions about update statistics from AnswerOnline 11.5 as following : Here from IDS11.5 : Example of UPDATE STATISTICS HIGH statements for the first column in each index that differs: For example, suppose you have the following indexes on table t1: CREATE INDEX ix_1 ON t1 (a, b, c, d, x1) ... CREATE INDEX ix_2 ON t1 (a, b, e, f, x2) ... CREATE INDEX ix_3 ON t1 (f, x3, x4, x5) ... Step 3 executes UPDATE STATISTICS HIGH on column a and column f. Then run UPDATE STATISTICS HIGH on columns c and e. UPDATE STATISTICS HIGH FOR TABLE t1(c); UPDATE STATISTICS HIGH FOR TABLE t1(e); In addition, you can run UPDATE STATISTICS HIGH on column b, although this is usually not necessary. Suppose you have indexes that begin with the same subset From table above, I generated three commands. 1. UPDATE STATISTICS HIGH FOR TABLE t1 (a,b,c,d,x1); UPDATE STATISTICS HIGH FOR TABLE t1 (a,b,e,f,x2); UPDATE STATISTICS MEDIUM FOR TABLE t1 (f,x3,x4,x5); 2. UPDATE STATISTICS HIGH FOR TABLE t1 (a,b); UPDATE STATISTICS HIGH FOR TABLE t1 (c); UPDATE STATISTICS HIGH FOR TABLE t1 (e); UPDATE STATISTICS HIGH FOR TABLE t1 (f); UPDATE STATISTICS MEDIUM FOR TABLE t1(x3,x4,x5); UPDATE STATISTICS MEDIUM FOR TABLE t1(x2); UPDATE STATISTICS MEDIUM FOR TABLE t1(d,x1); 3. UPDATE STATISTICS HIGH FOR TABLE t1 (a); UPDATE STATISTICS HIGH FOR TABLE t1 (c); UPDATE STATISTICS HIGH FOR TABLE t1 (e); UPDATE STATISTICS HIGH FOR TABLE t1 (f); UPDATE STATISTICS MEDIUM FOR TABLE t1(d); UPDATE STATISTICS MEDIUM FOR TABLE t1(x1); UPDATE STATISTICS MEDIUM FOR TABLE t1(x2); UPDATE STATISTICS MEDIUM FOR TABLE t1(x3); UPDATE STATISTICS MEDIUM FOR TABLE t1(x4); UPDATE STATISTICS MEDIUM FOR TABLE t1(x5); Questions : 1. Is it correct 1-3 ? 2. What is the differences between 1-3 in term of data distribution ? 3. Any other recommend ? Thank You , Varaporn :-))

VARAPORN VERAVITHAYA wrote: > 3. Any other recommend ? > > You might use Art Kagel's dostats program. This gives you this command sequence: UPDATE STATISTICS LOW FOR TABLE "t1" (a, b, c, d, x1); UPDATE STATISTICS LOW FOR TABLE "t1" (a, b, e, f, x2); UPDATE STATISTICS LOW FOR TABLE "t1" (f, x3, x4, x5); UPDATE STATISTICS HIGH FOR TABLE "t1" (a, c, e, f) DISTRIBUTIONS ONLY; UPDATE STATISTICS MEDIUM FOR TABLE "t1" (b, d, x1, x2, x3, x4, x5);

Hi, Pls check: http://www-01.ibm.com/support/docview.wss?uid=swg21137764 http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller /0203miller.html#section3 Regards Vikas *************************************************************************** Hi , I have somw questions about update statistics from AnswerOnline 11.5 as following : Here from IDS11.5 : Example of UPDATE STATISTICS HIGH statements for the first column in each index that differs: For example, suppose you have the following indexes on table t1: CREATE INDEX ix_1 ON t1 (a, b, c, d, x1) ... CREATE INDEX ix_2 ON t1 (a, b, e, f, x2) ... CREATE INDEX ix_3 ON t1 (f, x3, x4, x5) ... Step 3 executes UPDATE STATISTICS HIGH on column a and column f. Then run UPDATE STATISTICS HIGH on columns c and e. UPDATE STATISTICS HIGH FOR TABLE t1(c); UPDATE STATISTICS HIGH FOR TABLE t1(e); In addition, you can run UPDATE STATISTICS HIGH on column b, although this is usually not necessary. Suppose you have indexes that begin with the same subset From table above, I generated three commands. 1. UPDATE STATISTICS HIGH FOR TABLE t1 (a,b,c,d,x1); UPDATE STATISTICS HIGH FOR TABLE t1 (a,b,e,f,x2); UPDATE STATISTICS MEDIUM FOR TABLE t1 (f,x3,x4,x5); 2. UPDATE STATISTICS HIGH FOR TABLE t1 (a,b); UPDATE STATISTICS HIGH FOR TABLE t1 (c); UPDATE STATISTICS HIGH FOR TABLE t1 (e); UPDATE STATISTICS HIGH FOR TABLE t1 (f); UPDATE STATISTICS MEDIUM FOR TABLE t1(x3,x4,x5); UPDATE STATISTICS MEDIUM FOR TABLE t1(x2); UPDATE STATISTICS MEDIUM FOR TABLE t1(d,x1); 3. UPDATE STATISTICS HIGH FOR TABLE t1 (a); UPDATE STATISTICS HIGH FOR TABLE t1 (c); UPDATE STATISTICS HIGH FOR TABLE t1 (e); UPDATE STATISTICS HIGH FOR TABLE t1 (f); UPDATE STATISTICS MEDIUM FOR TABLE t1(d); UPDATE STATISTICS MEDIUM FOR TABLE t1(x1); UPDATE STATISTICS MEDIUM FOR TABLE t1(x2); UPDATE STATISTICS MEDIUM FOR TABLE t1(x3); UPDATE STATISTICS MEDIUM FOR TABLE t1(x4); UPDATE STATISTICS MEDIUM FOR TABLE t1(x5); Questions : 1. Is it correct 1-3 ? 2. What is the differences between 1-3 in term of data distribution ? 3. Any other recommend ? Thank You , Varaporn :-))

None of these is strictly correct according to the Performance Guide and to John Miller III's white paper on the subject. Obviously any three of the sets of commands that you present below will generate distributions and the server's performance for most queries will be identical after each set. The difference between these and the recommendations is that the recommended set of commands will produce useful distributions for the table at minimal cost in time to run the update statistics commands. These are the recommended commands and are indeed the ones that will be generated either by my dostats utility or by the IDS 11 automated update statistics feature, when there are no distributions already present, if you enable it: update statistics medium for table t1 (<list of all columns of t1 not listedbelow in HIGH command>); update statistics high for table t1( a, f, c, e ) distributions only; update statistics low for table t1(a, b, c, d, x1); update statistics low for table t1(a, b, e, f, x2); update statistics low for table t1(f, x3, x4, x5); For some older engines, breaking the commands up differently is more efficient, but for engines since 7.31xD3 and 9.30xC2 this has been the recommendation. Dostats detects the engine version and modifies the set of commands that it generates depending on the version (though you can optionally override that optimization because on some smaller systems with limited memory the older method is a bit faster). Anyway, these commands will first generate MEDIUM level distributions for all columns that do not head an index and are not the first different column between multiple indexes that begin with the same set of columns. Then it generates HIGH level distributions for the columns that either head an index (a & f) or are the first different column in some index (c & e). This is done in a single command to allow the engine to scan the table only once to gather the data for all four columns (similarly the MEDIUM columns in the first command should be listed in a single command or at least in as few commands as possible). The HIGH is run without DISTRIBUTIONS ONLY because the low level stats for these columns will be gathered in the last step anyway. Lastly a LOW is run for each whole index key one key in each command. This gathers low level stats at the table level (# rows & number of data pages), column level (2nd highest and 2nd lowest value), and at the index level (#nodes, #leaves, depth, & width). Please note that all of these decisions can be automated by using my dostats utility which part of the package utils2_ak and is downloadable either from the IIUG Software Repository (www.iiug.org/software) or from the Oninit web site (www.oninit.com/utils). You can also use the 11.xx Automated Update Statistics feature to produce and run stats commands and to schedule those runs. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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, Apr 2, 2009 at 4:50 AM, VARAPORN VERAVITHAYA <varaporn.v@cdg.co.th>wrote: > Hi , > > I have somw questions about update statistics from AnswerOnline 11.5 as > following : > > Here from IDS11.5 : > > Example of UPDATE STATISTICS HIGH statements for the first column in each > index that differs: > > For example, suppose you have the following indexes on table t1: > > CREATE INDEX ix_1 ON t1 (a, b, c, d, x1) ... > CREATE INDEX ix_2 ON t1 (a, b, e, f, x2) ... > CREATE INDEX ix_3 ON t1 (f, x3, x4, x5) ...> > Step 3 executes UPDATE STATISTICS HIGH on column a and column f. > > Then run UPDATE STATISTICS HIGH on columns c and e. > UPDATE STATISTICS HIGH FOR TABLE t1(c); > UPDATE STATISTICS HIGH FOR TABLE t1(e);> > In addition, you can run UPDATE STATISTICS HIGH on column b, although this > is > usually not necessary. Suppose you have indexes that begin with the same > subset > > >From table above, I generated three commands. > 1. > > UPDATE STATISTICS HIGH FOR TABLE t1 (a,b,c,d,x1);> > UPDATE STATISTICS HIGH FOR TABLE t1 (a,b,e,f,x2);> > UPDATE STATISTICS MEDIUM FOR TABLE t1 (f,x3,x4,x5);> > 2. > > UPDATE STATISTICS HIGH FOR TABLE t1 (a,b);> > UPDATE STATISTICS HIGH FOR TABLE t1 (c);> > UPDATE STATISTICS HIGH FOR TABLE t1 (e);> > UPDATE STATISTICS HIGH FOR TABLE t1 (f);> > UPDATE STATISTICS MEDIUM FOR TABLE t1(x3,x4,x5);> > UPDATE STATISTICS MEDIUM FOR TABLE t1(x2);> > UPDATE STATISTICS MEDIUM FOR TABLE t1(d,x1);> > 3. > > UPDATE STATISTICS HIGH FOR TABLE t1 (a);> > UPDATE STATISTICS HIGH FOR TABLE t1 (c);> > UPDATE STATISTICS HIGH FOR TABLE t1 (e);> > UPDATE STATISTICS HIGH FOR TABLE t1 (f);> > UPDATE STATISTICS MEDIUM FOR TABLE t1(d);> > UPDATE STATISTICS MEDIUM FOR TABLE t1(x1);> > UPDATE STATISTICS MEDIUM FOR TABLE t1(x2);> > UPDATE STATISTICS MEDIUM FOR TABLE t1(x3);> > UPDATE STATISTICS MEDIUM FOR TABLE t1(x4);> > UPDATE STATISTICS MEDIUM FOR TABLE t1(x5);> > Questions : > 1. Is it correct 1-3 ? > 2. What is the differences between 1-3 in term of data distribution ? > 3. Any other recommend ? > > Thank You , > Varaporn :-)) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016368e20f67437a504669776ed