update stats high question

I have a couple of functional as well as architectural questions for the update statistics gurus. After performing a migration from IDS 7 to IDS 94fc6it was recommended in the performance and migration manuals to drop dists and run update stats as appropriate. It was also suggested we only need to run update statistics high for the smaller tables.- One area I did not see addressed is that the high statement will implicitly perform the drop distributions as part of it's process? - Does this imply that simply running update statistics high for any table (regardless of size, width, performance and time) is the same as the more discrete and elaborate methods employed by dostats and other utilities for the indexed columns? - Is the intent of the various update statistics statements to minimize the impact on the system as well as the elapsed time to complete? - Is there an issue with the amount of data the high statement generates in the various system tables and is it actual counter productive with respect to the optimizer for a small to moderate size system (3-4 databases each at 24 gig and about 700 tables)? Thanks in advance, Doug

The algorithm recommended in the Performance Guide and in John Miller III's paper, and implemented in dostats, is designed to minimize the runtimes of the update stats runs while providing sufficiently detailed stats for the optimizer. If you are running HIGH on the entire table that is ALMOST enough, and will, for non-key leading columns, provide better stats than dostats, but will tend to run a bit longer. The only thing I think you will be missing are the LOW calculations for each index key. Perhaps John Miller can comment on that. So, based on my assertion, I would add to the HIGH's a LOW on each complete index key. Art S. Kagel ----- Original Message ----- From: Doug Fossmeyer <ids@iiug.org> At: 6/20 16:09:28 I have a couple of functional as well as architectural questions for the update statistics gurus. After performing a migration from IDS 7 to IDS 94fc6it was recommended in the performance and migration manuals to drop dists and run update stats as appropriate. It was also suggested we only need to run update statistics high for the smaller tables.- One area I did not see addressed is that the high statement will implicitly perform the drop distributions as part of it's process? - Does this imply that simply running update statistics high for any table (regardless of size, width, performance and time) is the same as the more discrete and elaborate methods employed by dostats and other utilities for the indexed columns? - Is the intent of the various update statistics statements to minimize the impact on the system as well as the elapsed time to complete? - Is there an issue with the amount of data the high statement generates in the various system tables and is it actual counter productive with respect to the optimizer for a small to moderate size system (3-4 databases each at 24 gig and about 700 tables)? Thanks in advance, Doug ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

What is the benefit of running an upstats low on each complete index key if the same column is used in multiple indexes. It seems to me that upstats low would then be generated on that column multiple times resulting in unnecessary work. ----- Original Message ---- From: "ART KAGEL, ...." <kagel@bloomberg.net> To: ids@iiug.org Sent: Wednesday, June 21, 2006 8:03:11 AM Subject: Re: update stats high question [7013] The algorithm recommended in the Performance Guide and in John Miller III's paper, and implemented in dostats, is designed to minimize the runtimes of the update stats runs while providing sufficiently detailed stats for the optimizer. If you are running HIGH on the entire table that is ALMOST enough, and will, for non-key leading columns, provide better stats than dostats, but will tend to run a bit longer. The only thing I think you will be missing are the LOW calculations for each index key. Perhaps John Miller can comment on that. So, based on my assertion, I would add to the HIGH's a LOW on each complete index key. Art S. Kagel ----- Original Message ----- From: Doug Fossmeyer <ids@iiug.org> At: 6/20 16:09:28 I have a couple of functional as well as architectural questions for the update statistics gurus. After performing a migration from IDS 7 to IDS 94fc6it was recommended in the performance and migration manuals to drop dists and run update stats as appropriate. It was also suggested we only need to run update statistics high for the smaller tables.- One area I did not see addressed is that the high statement will implicitly perform the drop distributions as part of it's process? - Does this imply that simply running update statistics high for any table (regardless of size, width, performance and time) is the same as the more discrete and elaborate methods employed by dostats and other utilities for the indexed columns? - Is the intent of the various update statistics statements to minimize the impact on the system as well as the elapsed time to complete? - Is there an issue with the amount of data the high statement generates in the various system tables and is it actual counter productive with respect to the optimizer for a small to moderate size system (3-4 databases each at 24 gig and about 700 tables)? Thanks in advance, Doug ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

But, the engine needs to calculate the depth and width of the index and the 2nd high and 2nd low key values to update the sysindexes/sysindices record for the key. It is my understanding that this will not be done unless a LOW on the EXACT key column list for each index (or a HIGH on that same exact list without the DISTRIBUTIONS ONLY clause) is run. Maybe John can enlighten us on this issue. Art ----- Original Message ----- From: Dl Redden <ids@iiug.org> At: 6/21 11:08:32 What is the benefit of running an upstats low on each complete index key if the same column is used in multiple indexes. It seems to me that upstats low would then be generated on that column multiple times resulting in unnecessary work. ----- Original Message ---- From: "ART KAGEL, ...." <kagel@bloomberg.net> To: ids@iiug.org Sent: Wednesday, June 21, 2006 8:03:11 AM Subject: Re: update stats high question [7013] The algorithm recommended in the Performance Guide and in John Miller III's paper, and implemented in dostats, is designed to minimize the runtimes of the update stats runs while providing sufficiently detailed stats for the optimizer. If you are running HIGH on the entire table that is ALMOST enough, and will, for non-key leading columns, provide better stats than dostats, but will tend to run a bit longer. The only thing I think you will be missing are the LOW calculations for each index key. Perhaps John Miller can comment on that. So, based on my assertion, I would add to the HIGH's a LOW on each complete index key. Art S. Kagel ----- Original Message ----- From: Doug Fossmeyer <ids@iiug.org> At: 6/20 16:09:28 I have a couple of functional as well as architectural questions for the update statistics gurus. After performing a migration from IDS 7 to IDS 94fc6it was recommended in the performance and migration manuals to drop dists and run update stats as appropriate. It was also suggested we only need to run update statistics high for the smaller tables.- One area I did not see addressed is that the high statement will implicitly perform the drop distributions as part of it's process? - Does this imply that simply running update statistics high for any table (regardless of size, width, performance and time) is the same as the more discrete and elaborate methods employed by dostats and other utilities for the indexed columns? - Is the intent of the various update statistics statements to minimize the impact on the system as well as the elapsed time to complete? - Is there an issue with the amount of data the high statement generates in the various system tables and is it actual counter productive with respect to the optimizer for a small to moderate size system (3-4 databases each at 24 gig and about 700 tables)? Thanks in advance, Doug ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

If you have 3 indexes: i1 on a, b i2 on a, c i3 on d, e You can run update statistics low on (a, b, c, d, e) in one statement and the engine will generate statistics for all 3 indexes just as it would if you ran 3 sets of update stats low on (a, b), (a, c) and (d, e). Unfortunately it doesn't do anything improve performance because the engine will process each of the three indexes serially. Walk all of the leaf pages of i1 -> store statistics in sysindexes for i1 Walk all of the leaf pages of i2 -> store statistics in sysindexes for i2 Walk all of the leaf pages of i3 -> store statistics in sysindexes for i3 Also, I don't think the column list has to be exact if you want to break it into 3 parts. Won't the engine generate statistics for index i2 if you update stats low on (a, c, e)? Andrew Ford -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, .... Sent: Wednesday, June 21, 2006 11:08 AM To: ids@iiug.org Subject: Re: update stats high question [7020] But, the engine needs to calculate the depth and width of the index and the 2nd high and 2nd low key values to update the sysindexes/sysindices record for the key. It is my understanding that this will not be done unless a LOW on the EXACT key column list for each index (or a HIGH on that same exact list without the DISTRIBUTIONS ONLY clause) is run. Maybe John can enlighten us on this issue. Art ----- Original Message ----- From: Dl Redden <ids@iiug.org> At: 6/21 11:08:32 What is the benefit of running an upstats low on each complete index key if the same column is used in multiple indexes. It seems to me that upstats low would then be generated on that column multiple times resulting in unnecessary work. ----- Original Message ---- From: "ART KAGEL, ...." <kagel@bloomberg.net> To: ids@iiug.org Sent: Wednesday, June 21, 2006 8:03:11 AM Subject: Re: update stats high question [7013] The algorithm recommended in the Performance Guide and in John Miller III's paper, and implemented in dostats, is designed to minimize the runtimes of the update stats runs while providing sufficiently detailed stats for the optimizer. If you are running HIGH on the entire table that is ALMOST enough, and will, for non-key leading columns, provide better stats than dostats, but will tend to run a bit longer. The only thing I think you will be missing are the LOW calculations for each index key. Perhaps John Miller can comment on that. So, based on my assertion, I would add to the HIGH's a LOW on each complete index key. Art S. Kagel ----- Original Message ----- From: Doug Fossmeyer <ids@iiug.org> At: 6/20 16:09:28 I have a couple of functional as well as architectural questions for the update statistics gurus. After performing a migration from IDS 7 to IDS94fc6 it was recommended in the performance and migration manuals to drop dists and run update stats as appropriate. It was also suggested we only need to run update statistics high for the smaller tables.- One area I did not see addressed is that the high statement will implicitly perform the drop distributions as part of it's process? - Does this imply that simply running update statistics high for any table (regardless of size, width, performance and time) is the same as the more discrete and elaborate methods employed by dostats and other utilities for the indexed columns? - Is the intent of the various update statistics statements to minimize the impact on the system as well as the elapsed time to complete? - Is there an issue with the amount of data the high statement generates in the various system tables and is it actual counter productive with respect to the optimizer for a small to moderate size system (3-4 databases each at 24 gig and about 700 tables)? Thanks in advance, Doug ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ----------------------------------------- -- this email delivered by anubis