Update statistics - best practice
User sought advice on selective statistics gathering for Informix tables, particularly avoiding distributions on date/time columns that mislead the optimizer in write-intensive tables. Art Kagel confirmed dostats supports this via the -I and -C flags (though noting they were broken) and the -x option to exclude tables. User confirmed this approach would suit their needs, planning to test before production use.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I was wondering if anyone could offer any advice on statistics gathering. I know a lot of people use 'dostats' for this purpose which gathers distributions of varying resolution on all columns. I would like to look at an alternative solution where: 1. No distributions are gathered on some date/time columns. This is because we have a lot of write-intensive tables where the current date is inserted and distributions mislead the optimiser into believing the maximum value for date is in the past. 2. I don't necessarily want or need distributions on columns that are not part of an index. I have some evidence that this confuses the optimiser under specific scenarios. I can write something like this myself without a massive amount of trouble but has anyone got anything 'off the shelf' or any comments on my requirements? Ben.
You should be able to do: dostats -d mydatabase --clean-distributions -I -C That would clear out all of the old distributions for each table then run HIGH on the initial index key column(s) only and LOW on each full index key. HOWEVER, the -I and -C seem to be broken. Haven't used either for a while - and apparently neither has anyone else. I'm on it now. If you want an updated version with the fix, let me know. 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 Wed, Nov 28, 2012 at 12:34 PM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > Hi, > > I was wondering if anyone could offer any advice on statistics gathering. I > know a lot of people use 'dostats' for this purpose which gathers > distributions of varying resolution on all columns. > > I would like to look at an alternative solution where: > > 1. No distributions are gathered on some date/time columns. This is > because we > have a lot of write-intensive tables where the current date is inserted and > distributions mislead the optimiser into believing the maximum value for > date > is in the past. > > 2. I don't necessarily want or need distributions on columns that are not > part > of an index. I have some evidence that this confuses the optimiser under > specific scenarios. > > I can write something like this myself without a massive amount of trouble > but > has anyone got anything 'off the shelf' or any comments on my requirements? > > Ben. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340bef4b108a04cf922c18
On Wed, Nov 28, 2012 at 5:34 PM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > Hi, > > I was wondering if anyone could offer any advice on statistics gathering. I > know a lot of people use 'dostats' for this purpose which gathers > distributions of varying resolution on all columns. > > I'm not a regular user of dostats, but I suppose it allows you to exclude some tables... If your problems don't affect too many you could exclude some and do customized instructions for them. Not a good option if your situations happen on a lot of tables... > I would like to look at an alternative solution where: > > 1. No distributions are gathered on some date/time columns. This is > because we > have a lot of write-intensive tables where the current date is inserted and > distributions mislead the optimiser into believing the maximum value for > date > is in the past. > This is a very usual issue. I'm wondering which version you're using... I believe in 11.70.FC5 a "timid" attempt to solve this was implemented. Note this is not a criticism to R&D. This is a really hard problem to solve. Again, if you don't have too many situations you could consider tweaking the queries... Using directives, external directives or operations on that specific column(s). These options are totally against my generic recommendations, but this is really a special case. > > 2. I don't necessarily want or need distributions on columns that are not > part > of an index. I have some evidence that this confuses the optimiser under > specific scenarios. > These would probably deserve a PMR. It shouldn't happen, and I haven't come across this as far as I can remember. Nevertheless it can be a good option to implement in my scripts... > I can write something like this myself without a massive amount of trouble > but > has anyone got anything 'off the shelf' or any comments on my requirements? > > Art has already answered... Hopefully he'll solve your problems, but this is just my 2 cents. Regards -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7b6d88b09f43cc04cf95bdea
Hi Art, Thank you for your fast reply. It's good to know that dostats can support this requirement, even if the feature is presently broken. There is no need to rush a new release out to me but when you do make it available I will experiment with it before moving it to our live environment. Ben.
Hi Fernando, I think excluding certain tables and doing something manually could be the answer here. We're using 11.50.FC9W2 and I do have a PMR open for the optimiser problem I mentioned, which is on the verge of being logged as a defect. Our vendor has implemented some hints but the problem that if a query plan changes we may find a new place in the code where a hint is needed and then we may have to hot fix the code. Having no distributions on the date/time columns seems to fix the problem in that the optimiser has to make assumptions about the selectivity of the date/time field column. My tests have shown that this leads to constant high cost estimates no matter what date value is used and this is a good thing since the distributions on the other table will show the index we do want to use is really quite selective. The only disadvantage I can see to having no stats on the date/time column is if we query the table with a wide date range and a sequential scan would be more efficient than reading the index and then the data it points to. I can't really imagine a scenario in our application where such a query would be run. Ben.
You can exclude tables with the -x option in dostats. -x takes a table, a file listing tables, of a query returning tabnames to ignore: -x mytable -x @tabfile -x '!select tabname from bad_tab_list' 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 Fri, Nov 30, 2012 at 6:05 AM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > Hi Fernando, > > I think excluding certain tables and doing something manually could be the > answer here. > > We're using 11.50.FC9W2 and I do have a PMR open for the optimiser problem > I > mentioned, which is on the verge of being logged as a defect. Our vendor > has > implemented some hints but the problem that if a query plan changes we may > find a new place in the code where a hint is needed and then we may have to > hot fix the code. > > Having no distributions on the date/time columns seems to fix the problem > in > that the optimiser has to make assumptions about the selectivity of the > date/time field column. My tests have shown that this leads to constant > high > cost estimates no matter what date value is used and this is a good thing > since the distributions on the other table will show the index we do want > to > use is really quite selective. > > The only disadvantage I can see to having no stats on the date/time column > is > if we query the table with a wide date range and a sequential scan would be > more efficient than reading the index and then the data it points to. I > can't > really imagine a scenario in our application where such a query would be > run. > > Ben. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9399c71abec8204cfb46dc3