Re: Optimizer Issues with 10.00.xC8

Art S. Kagel (Oninit) wrote: > You may indeed have found a bug in the 10.00.xC8 optimizer and should > report this to IBM. > Already have, and am continuing to work on a test case. > However, at least on the Solaris DB side, I would make two suggestions > for quick fixes. First, if the selectivity of colA is so good, but with > millions of rows the default resolution of the data distributions may be > too low. Try running a HIGH on colA with double the default > resolution. That may be the key to getting better results from the > optimizer I hadn't thought to try that, but I wouldn't expect that to make that big a difference. I did some selectivity tests on the table. There are a little fewer than 200 million rows in the table. There are no more than 18 records for any one value of ColA. Whereas for ColC, nearly 60% (58.7%) of the rows have a value of ColC = 0. That, to my mind, makes ColC a bad index to begin with (and it would still be bad, even if you appended ColA to it, because you'd still have to trudge through all those zero values). Actually, what I really need to do is fragment the index on ColC so that all the 0s go in one fragment, and all the nonzero values go into another fragment or set of fragments. This has worked well on another table; but I digress... In further tests, what I've found is that queries run from dbaccess always choose the best index. But when you're using a cursor where colA = ? and colC = ?, it's prone to picking the wrong one (even though it used to work fine in FC6). I'm still trying to work on the particulars of that. > On the AIX side, again, this is likely a bug, especially given that this > used to work fine. That said, this is a common problem in IDS with > tables that suffer major changes in contents immediately prior to being > queried with no time to update stats properly in between. The best > solution for this is to UPDATE STATISTICS LOW...DROP DISTRIBUTIONS > immediately before the truncate/load operation, run that initial round > of time sensitive queries without data distributions, and reinstate the > distributions as soon after the initial round of queries as is > practical. Well, the real solution here is simply to run UPDATE STATISTICS after the truncate/reload, something we've been bugging the application team to do for nearly a year. However, we don't use distributions for this particular application. In fact, we almost never use distributions anywhere in the enterprise, because whenever we've tried it, we wind up having problems like what I describe above. Ditto for OPTCOMPIND=2, but again, I digress. (Someone, I think it may have been Mark Scranton, used to bug us about this when we'd bring it up at conferences. "You should be running distributions and setting OPTCOMPIND=2." Not if we want our queries to come back today, we shouldn't...) David: Providing an optimizer directive does seem to resolve the issue, but we generally try to avoid putting them into our applications on a permanent basis. They work as a short-term fix, but that's about it.

Thomas J. Girsch wrote: > Art S. Kagel (Oninit) wrote: > > >> You may indeed have found a bug in the 10.00.xC8 optimizer and should >> report this to IBM. >> >> > Already have, and am continuing to work on a test case. > > >> However, at least on the Solaris DB side, I would make two suggestions >> for quick fixes. First, if the selectivity of colA is so good, but with >> millions of rows the default resolution of the data distributions may be >> too low. Try running a HIGH on colA with double the default >> resolution. That may be the key to getting better results from the >> optimizer >> > > I hadn't thought to try that, but I wouldn't expect that to make that > big a difference. I did some selectivity tests on the table. There are > a little fewer than 200 million rows in the table. There are no more > than 18 records for any one value of ColA. Whereas for ColC, nearly 60% > (58.7%) of the rows have a value of ColC = 0. That, to my mind, makes > ColC a bad index to begin with (and it would still be bad, even if you > appended ColA to it, because you'd still have to trudge through all > Well, unless you need colC to lead the index for sorting, how about an index on colA then colC? That would sidestep the low selectivity of the colC index. If you still need the colC only index for some queries, then keep both. > those zero values). Actually, what I really need to do is fragment the > index on ColC so that all the 0s go in one fragment, and all the nonzero > values go into another fragment or set of fragments. This has worked > well on another table; but I digress... > > In further tests, what I've found is that queries run from dbaccess > always choose the best index. But when you're using a cursor where colA > = ? and colC = ?, it's prone to picking the wrong one (even though it > used to work fine in FC6). I'm still trying to work on the particulars > of that. > Interesting. That used to be a big problem. Since the engine doesn't know the values that you will ultimately supply to the replaceable parameters, the optimizer used to do a dumbed down version of the optimization that exhibited just the problems you are seeing with the queries working fine if it had exact values at prepare time, but not with parameters. I, and several other customers, complained about the problem and one of the big advances in the optimizer in 7.31/9.30 was for the engine to delay the final calculations of cost for these queries until it had the replacement values at cursor open/execute time. This sounds like they somehow broke that in this release. Art > >> On the AIX side, again, this is likely a bug, especially given that this >> used to work fine. That said, this is a common problem in IDS with >> tables that suffer major changes in contents immediately prior to being >> queried with no time to update stats properly in between. The best >> solution for this is to UPDATE STATISTICS LOW...DROP DISTRIBUTIONS >> immediately before the truncate/load operation, run that initial round >> of time sensitive queries without data distributions, and reinstate the >> distributions as soon after the initial round of queries as is >> practical. >> > > Well, the real solution here is simply to run UPDATE STATISTICS after > the truncate/reload, something we've been bugging the application team > to do for nearly a year. However, we don't use distributions for this > particular application. In fact, we almost never use distributions > anywhere in the enterprise, because whenever we've tried it, we wind up > having problems like what I describe above. Ditto for OPTCOMPIND=2, but > again, I digress. > > (Someone, I think it may have been Mark Scranton, used to bug us about > this when we'd bring it up at conferences. "You should be running > distributions and setting OPTCOMPIND=2." Not if we want our queries to > come back today, we shouldn't...) > > David: > > Providing an optimizer directive does seem to resolve the issue, but we > generally try to avoid putting them into our applications on a permanent > basis. They work as a short-term fix, but that's about it. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > =========================================================================================== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > =========================================================================================== > > > =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================

Art S. Kagel (Oninit) wrote: > Well, unless you need colC to lead the index for sorting, how about an > index on colA then colC? That would sidestep the low selectivity of the > colC index. If you still need the colC only index for some queries, > then keep both. > We have some queries that search based on ColA, some that search based on ColA and ColC combined, and some that search on ColC alone (but only when ColC is nonzero). Presumably we _could_ add an additional index on (ColA, ColC), but we shouldn't have to, especially since ColA by itself gets you plenty close enough -- within 18 rows out of 200 million makes for a pretty quick scan. > Interesting. That used to be a big problem. Since the engine doesn't > know the values that you will ultimately supply to the replaceable > parameters, the optimizer used to do a dumbed down version of the > optimization that exhibited just the problems you are seeing with the > queries working fine if it had exact values at prepare time, but not > with parameters. I, and several other customers, complained about the > problem and one of the big advances in the optimizer in 7.31/9.30 was > for the engine to delay the final calculations of cost for these queries > until it had the replacement values at cursor open/execute time. > This sounds like they somehow broke that in this release. > That doesn't seem to be it. I'm still getting inconsistent results, but what I'm finding is that which path it takes depends on which values it gets the _first time_ that the cursor is opened. So if it gets a ColC=0 request first, it picks the ColA index (the correct one), and does so for subsequent executions as well. But if it gets a ColC=[nonzero] request first, it picks the index on ColC, and seems to stick with it. Again, this _only_ appears to be the case if there are distributions on the table. Without distributions, it's reliably picking the index on ColA, the correct one. Which is exactly backward from the behavior I'd expect to see. With the data skew on ColC, I would expect it to _never_ select that index when it has a value for ColA.

Thomas J. Girsch wrote: > Art S. Kagel (Oninit) wrote: >> Well, unless you need colC to lead the index for sorting, how about an >> index on colA then colC? That would sidestep the low selectivity of >> the colC index. If you still need the colC only index for some >> queries, then keep both. >> > We have some queries that search based on ColA, some that search based > on ColA and ColC combined, and some that search on ColC alone (but only > when ColC is nonzero). Presumably we _could_ add an additional index on > (ColA, ColC), but we shouldn't have to, especially since ColA by itself > gets you plenty close enough -- within 18 rows out of 200 million makes > for a pretty quick scan. > >> Interesting. That used to be a big problem. Since the engine doesn't >> know the values that you will ultimately supply to the replaceable >> parameters, the optimizer used to do a dumbed down version of the >> optimization that exhibited just the problems you are seeing with the >> queries working fine if it had exact values at prepare time, but not >> with parameters. I, and several other customers, complained about the >> problem and one of the big advances in the optimizer in 7.31/9.30 was >> for the engine to delay the final calculations of cost for these >> queries until it had the replacement values at cursor open/execute time. >> This sounds like they somehow broke that in this release. >> > That doesn't seem to be it. I'm still getting inconsistent results, but > what I'm finding is that which path it takes depends on which values it > gets the _first time_ that the cursor is opened. So if it gets a ColC=0 > request first, it picks the ColA index (the correct one), and does so > for subsequent executions as well. But if it gets a ColC=[nonzero] > request first, it picks the index on ColC, and seems to stick with it. There is a "WITH REOPTIMIZATION" (or similar) clause for the open cursor statement... But IIRC it's not available in Java... Regards -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Thomas J. Girsch wrote: > Art S. Kagel (Oninit) wrote: > >> Well, unless you need colC to lead the index for sorting, how about an >> index on colA then colC? That would sidestep the low selectivity of the >> colC index. If you still need the colC only index for some queries, >> then keep both. >> >> > We have some queries that search based on ColA, some that search based > on ColA and ColC combined, and some that search on ColC alone (but only > when ColC is nonzero). Presumably we _could_ add an additional index on > (ColA, ColC), but we shouldn't have to, especially since ColA by itself > gets you plenty close enough -- within 18 rows out of 200 million makes > for a pretty quick scan. > > >> Interesting. That used to be a big problem. Since the engine doesn't >> know the values that you will ultimately supply to the replaceable >> parameters, the optimizer used to do a dumbed down version of the >> optimization that exhibited just the problems you are seeing with the >> queries working fine if it had exact values at prepare time, but not >> with parameters. I, and several other customers, complained about the >> problem and one of the big advances in the optimizer in 7.31/9.30 was >> for the engine to delay the final calculations of cost for these queries >> until it had the replacement values at cursor open/execute time. >> This sounds like they somehow broke that in this release. >> >> > That doesn't seem to be it. I'm still getting inconsistent results, but > what I'm finding is that which path it takes depends on which values it > gets the _first time_ that the cursor is opened. So if it gets a ColC=0 > request first, it picks the ColA index (the correct one), and does so > for subsequent executions as well. But if it gets a ColC=[nonzero] > request first, it picks the index on ColC, and seems to stick with it. > > Again, this _only_ appears to be the case if there are distributions on > the table. Without distributions, it's reliably picking the index on > ColA, the correct one. Which is exactly backward from the behavior I'd > expect to see. With the data skew on ColC, I would expect it to _never_ > select that index when it has a value for ColA. Makes sense to me. Without distributions it uses the older dumber optimizer algorithms which are dependend on the relative depth and number of unique values of the indexes (stored in sysindices) and the colmin & colmax values (stored in syscolumns) which are the second smallest and second greatest values for the column. Since zero is likely the smallest value of colC and the number of unique values for the colC only index is relatively low compared to the number of unique keys in the unique index on colA & colB (which is somewhere shy of 200million if I remember correctly) the colA, colB index is clearly the more selective according to the algorithms used in the absence of distributions. Art S. Kagel Oninit =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================