Update statistics - slow index key search
Version: Informix 10 General Question about update statistics: For a given table that is fragmented into multiple dbspace: 1. Should update statistics be run in any specific way 2. Should the data distribution be dropped while running update statistics 3. I am seeing slow selects even though that column has an index. sqlexplain shows index path selected but the data is not being retrieved.
On Sep 21, 5:47 pm, mohitanch...@gmail.com wrote: > Version: Informix 10 > > General Question about update statistics: > > For a given table that is fragmented into multiple dbspace: > > 1. Should update statistics be run in any specific way > 2. Should the data distribution be dropped while running update > statistics > 3. I am seeing slow selects even though that column has an index. > sqlexplain shows index path selected but the data is not being > retrieved. 1. Nothing special. As usual follow the recommendations in the Performance Guide as modified in John Miller's paper and as implemented in my dostats utility. FYI, dostats processes the fragmentation expression column(s) as if it(they) are the lead column(s) of an index. This is not mentioned anywhere, but I believe that it can only help the optimizer decide how to process a query, so I included that. 2. It's not neccessary to drop distributions if you are running UPDATE STATISTICS at MEDIUM or HIGH levels as this will replace any existing distributions. The only exception to that is IBM recommends dropping all distributions after a version upgrade, especially a major version upgrade. Art S. Kagel
On Sep 24, 7:13 am, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Sep 21, 5:47 pm, mohitanch...@gmail.com wrote: > > > Version:Informix10 > > > General Question about update statistics: > > > For a given table that is fragmented into multiple dbspace: > > > 1. Should update statistics be run in any specific way > > 2. Should the data distribution be dropped while running update > > statistics > > 3. I am seeing slow selects even though that column has an index. > > sqlexplain shows index path selected but the data is not being > > retrieved. > > 1. Nothing special. As usual follow the recommendations in the > Performance Guide as modified in John Miller's paper and as > implemented in my dostats utility. FYI, dostats processes the > fragmentation expression column(s) as if it(they) are the lead > column(s) of an index. This is not mentioned anywhere, but I believe > that it can only help the optimizer decide how to process a query, so > I included that. > > 2. It's not neccessary to drop distributions if you are running UPDATE > STATISTICS at MEDIUM or HIGH levels as this will replace any existing > distributions. The only exception to that is IBM recommends dropping > all distributions after a version upgrade, especially a major version > upgrade. > > Art S. Kagel Which one is better, fragmenting table in more small fragments or few big fragments. I would guess small fragments would be better, but I am not sure at what point to say, ok this looks optimal - of course testing plays a vital part.
On Sep 24, 11:30 am, mohitanch...@gmail.com wrote: > On Sep 24, 7:13 am, "Art S. Kagel" <art.ka...@gmail.com> wrote: > > > > > On Sep 21, 5:47 pm, mohitanch...@gmail.com wrote: > > > > Version:Informix10 > > > > General Question about update statistics: > > > > For a given table that is fragmented into multiple dbspace: > > > > 1. Should update statistics be run in any specific way > > > 2. Should the data distribution be dropped while running update > > > statistics > > > 3. I am seeing slow selects even though that column has an index. > > > sqlexplain shows index path selected but the data is not being > > > retrieved. > > > 1. Nothing special. As usual follow the recommendations in the > > Performance Guide as modified in John Miller's paper and as > > implemented in my dostats utility. FYI, dostats processes the > > fragmentation expression column(s) as if it(they) are the lead > > column(s) of an index. This is not mentioned anywhere, but I believe > > that it can only help the optimizer decide how to process a query, so > > I included that. > > > 2. It's not neccessary to drop distributions if you are running UPDATE > > STATISTICS at MEDIUM or HIGH levels as this will replace any existing > > distributions. The only exception to that is IBM recommends dropping > > all distributions after a version upgrade, especially a major version > > upgrade. > > > Art S. Kagel > > Which one is better, fragmenting table in more small fragments or few > big fragments. I would guess small fragments would be better, but I am > not sure at what point to say, ok this looks optimal - of course > testing plays a vital part. This is a tough one, and as you say, only testing is going to give a definitive answer specific to your table and application. You could make a case that there are definite gains at least until you have a fragments on each physical disk/spindle/array/structure in your system. You could make a case that there are definite gains to have at least as many fragments as CPU VPs. You could make a case that the only thing that matters is being able to use fragment elimination to query a minimal number of rows at a time. You could make a case that the only thing that matters is to be able to search as many fragments in parallel as possible. You could make a case that the only thing that matters is to not swamp your IO subsystems with too many concurrent requests. You could make a case for investing in Bayer, AG! Art S. Kagel
On Sep 24, 1:31 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Sep 24, 11:30 am, mohitanch...@gmail.com wrote: > > > > > > > On Sep 24, 7:13 am, "Art S. Kagel" <art.ka...@gmail.com> wrote: > > > > On Sep 21, 5:47 pm, mohitanch...@gmail.com wrote: > > > > > Version:Informix10 > > > > > General Question about update statistics: > > > > > For a given table that is fragmented into multiple dbspace: > > > > > 1. Should update statistics be run in any specific way > > > > 2. Should the data distribution be dropped while running update > > > > statistics > > > > 3. I am seeing slow selects even though that column has an index. > > > > sqlexplain shows index path selected but the data is not being > > > > retrieved. > > > > 1. Nothing special. As usual follow the recommendations in the > > > Performance Guide as modified in John Miller's paper and as > > > implemented in my dostats utility. FYI, dostats processes the > > > fragmentation expression column(s) as if it(they) are the lead > > > column(s) of an index. This is not mentioned anywhere, but I believe > > > that it can only help the optimizer decide how to process a query, so > > > I included that. > > > > 2. It's not neccessary to drop distributions if you are running UPDATE > > > STATISTICS at MEDIUM or HIGH levels as this will replace any existing > > > distributions. The only exception to that is IBM recommends dropping > > > all distributions after a version upgrade, especially a major version > > > upgrade. > > > > Art S. Kagel > > > Which one is better, fragmenting table in more small fragments or few > > big fragments. I would guess small fragments would be better, but I am > > not sure at what point to say, ok this looks optimal - of course > > testing plays a vital part. > > This is a tough one, and as you say, only testing is going to give a > definitive answer specific to your table and application. > > You could make a case that there are definite gains at least until you > have a fragments on each physical disk/spindle/array/structure in your > system. > > You could make a case that there are definite gains to have at least > as many fragments as CPU VPs. > > You could make a case that the only thing that matters is being able > to use fragment elimination to query a minimal number of rows at a > time. > > You could make a case that the only thing that matters is to be able > to search as many fragments in parallel as possible. > > You could make a case that the only thing that matters is to not swamp > your IO subsystems with too many concurrent requests. > > You could make a case for investing in Bayer, AG! > > Art S. Kagel- Hide quoted text - > > - Show quoted text - Reason I asked that question was because I was seeing slow search times and didn't have any possible explanation of why the query is slow even though it has index built on it. I think I was getting worried, but later found there is a known bug. I am referring to: http://www-1.ibm.com/support/docview.wss?uid=swg1IC52745 I think not too much could be done other than following up with IBM.