 | |
How to force index usage
sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: How to force index usage -
08-03-2009
, 02:46 PM
|
Basically, this case nails down to: How to force index usage without FORCE INDEX? (Why does it require DBA auth anyway? Is it a try-out tool only?) Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a76fb16$1 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for your joint wit & wisdom. Yes, the SELECTs issued by the application 'in between' include a condition for the date range on the same problematic column (where the other, slow SELECT is given a sequential scan instead of index usage), so are you saying that histogram stats are rewritten and verge on the edge of an optimizer formula deciding about seq.scan / index usage? Any chance of converting them to the permanent-index-usage version? I guess if there would be a way, you'd mention it already. Can optimizer hints, e.g. (A >= B,0) be used effectively here? I've tried recreating statistics (DROP STATISTICS ON table; CREATE STATISTICS table); tried out the SELECT, frozen on the 1st run, stopped after 30 minutes. At these very moments, a test with transferring that one index to CLUSTERED is in progress.. if that won't work, I'm basically out of options for the moment. Pavel "Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in message news:4a731529 (AT) forums-3-dub (DOT) sybase.com... Hmmm.... Interesting bit of info this: The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. That indicates the stats are getting rewritten between those stats due to the interleaving selects. [a test for this possibility could be to turn off the update_statistics option through a test run but dumping the histograms at each step will capture that part in fine detail] Rewritten one way the estimates are marginally worse for the plan that picks the index, rewritten another the estimates are marginally better for picking the index... and the coin turns solely on that proposition; apparently. {With the marginal difference one way, you get the performance you want but when it goes the other way gives you get a table scan.} Can I also assume these interleaving queries also use date ranges with different starting and ending sargs? [That can easiliy rebucket-ize the stats causing statistics to be grouped together or broken appart.] But since that seems to likely be the case then the index is probably giving you UN-reasonably better performance than can be expected (in the general case). Given the index is (now obviously) not declared as clustered, the optimizer is not able to estimate anywhere near the performance you are actually seeing and it is free to pick alternate plans whenever the estimate numbers dictate that. {Again my understanding here is that this is due to the data just happening to be captured in mostly clustered order, that and the starts are getting regrouped to not favour the index scanning plan.} To me it also sounds like the nature of the query is itself marginally optimizable as well and that is definitely something worth drilling into more. As to your X[B|C|S] woes, that confusion is normal. I believe the X-type is *determined* independantly from the content of the captured[ | dumped] information. The XB designation is determined from the estimated plan cost; but they will often also have actual statistics if the running cost also goes over. Further XC entries will not capture actual stats unless the operation completes (i.e. not cancelled, not closed). At least that was my experience with this facility. A little confusing but still quite useful as-is. ... NOTE THE HEAVY USE OF WORDS LIKE: marginal[ly], coin flipping references, ...? That is intentional since this question looks like optimizer is being asked to dance a jig at the edge of a precipace. [textual picture intended] "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a71db3b$2 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for these thoughts, since I've mentioned before that the statements runs very fast in ISQL and occassionally slow from the application, the plans confirming it's the 1 index usage difference were from LogExpensiveQueries already. I'm actually having issues with it as XC plans return estimates only... plus I see the statistics in XB, while XBs are having few seconds only, XC having 10k+ seconds. LEQ logs have been captured on 9.0.2.3632. The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. I'll respond with the results of tests (recreating histogram stats and clustered index). Pavel |
#12
| |||
| |||
|
|
Update: Changed the index to clustered, the SELECT is hanging for a couple of hours now. This leaves me with desperate longing for the answer on the below question. Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a7715d4$3 (AT) forums-3-dub (DOT) sybase.com... Basically, this case nails down to: How to force index usage without FORCE INDEX? (Why does it require DBA auth anyway? Is it a try-out tool only?) Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a76fb16$1 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for your joint wit & wisdom. Yes, the SELECTs issued by the application 'in between' include a condition for the date range on the same problematic column (where the other, slow SELECT is given a sequential scan instead of index usage), so are you saying that histogram stats are rewritten and verge on the edge of an optimizer formula deciding about seq.scan / index usage? Any chance of converting them to the permanent-index-usage version? I guess if there would be a way, you'd mention it already. Can optimizer hints, e.g. (A >= B,0) be used effectively here? I've tried recreating statistics (DROP STATISTICS ON table; CREATE STATISTICS table); tried out the SELECT, frozen on the 1st run, stopped after 30 minutes. At these very moments, a test with transferring that one index to CLUSTERED is in progress.. if that won't work, I'm basically out of options for the moment. Pavel "Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in message news:4a731529 (AT) forums-3-dub (DOT) sybase.com... Hmmm.... Interesting bit of info this: The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. That indicates the stats are getting rewritten between those stats due to the interleaving selects. [a test for this possibility could be to turn off the update_statistics option through a test run but dumping the histograms at each step will capture that part in fine detail] Rewritten one way the estimates are marginally worse for the plan that picks the index, rewritten another the estimates are marginally better for picking the index... and the coin turns solely on that proposition; apparently. {With the marginal difference one way, you get the performance you want but when it goes the other way gives you get a table scan.} Can I also assume these interleaving queries also use date ranges with different starting and ending sargs? [That can easiliy rebucket-ize the stats causing statistics to be grouped together or broken appart.] But since that seems to likely be the case then the index is probably giving you UN-reasonably better performance than can be expected (in the general case). Given the index is (now obviously) not declared as clustered, the optimizer is not able to estimate anywhere near the performance you are actually seeing and it is free to pick alternate plans whenever the estimate numbers dictate that. {Again my understanding here is that this is due to the data just happening to be captured in mostly clustered order, that and the starts are getting regrouped to not favour the index scanning plan.} To me it also sounds like the nature of the query is itself marginally optimizable as well and that is definitely something worth drilling into more. As to your X[B|C|S] woes, that confusion is normal. I believe the X-type is *determined* independantly from the content of the captured[ | dumped] information. The XB designation is determined from the estimated plan cost; but they will often also have actual statistics if the running cost also goes over. Further XC entries will not capture actual stats unless the operation completes (i.e. not cancelled, not closed). At least that was my experience with this facility. A little confusing but still quite useful as-is. ... NOTE THE HEAVY USE OF WORDS LIKE: marginal[ly], coin flipping references, ...? That is intentional since this question looks like optimizer is being asked to dance a jig at the edge of a precipace. [textual picture intended] "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a71db3b$2 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for these thoughts, since I've mentioned before that the statements runs very fast in ISQL and occassionally slow from the application, the plans confirming it's the 1 index usage difference were from LogExpensiveQueries already. I'm actually having issues with it as XC plans return estimates only... plus I see the statistics in XB, while XBs are having few seconds only, XC having 10k+ seconds. LEQ logs have been captured on 9.0.2.3632. The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. I'll respond with the results of tests (recreating histogram stats and clustered index). Pavel |
#13
| |||
| |||
|
|
*** Since a clustering index did not correct the problem, that shoots down most of my theories about what you are actually hitting. * * * It is best to find out exactly how this is really * * * * * * failing now. I do strongly recommend * * * * * * supplying the plans in a real support case or * * * * * * at least start a new thread with the plans in tow.* * * There is a way to provide the optimizer using "index-hints" [SEE: "FROM clause" in the online docs looking at the INDEX() and FORCE INDEX() ] but it is exceeding rare to actually require those. Not to mention how much of a kludge they may be. It may end up being the right thing to do in some final analysis but since that analysis has not happened . . . "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a773ead$2 (AT) forums-3-dub (DOT) sybase.com... Update: Changed the index to clustered, the SELECT is hanging for a couple of hours now. This leaves me with desperate longing for the answer on the below question. Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a7715d4$3 (AT) forums-3-dub (DOT) sybase.com... Basically, this case nails down to: How to force index usage without FORCE INDEX? (Why does it require DBA auth anyway? Is it a try-out tool only?) Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a76fb16$1 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for your joint wit & wisdom. Yes, the SELECTs issued by the application 'in between' include a condition for the date range on the same problematic column (where the other, slow SELECT is given a sequential scan instead of index usage), so are you saying that histogram stats are rewritten and verge on the edge of an optimizer formula deciding about seq.scan / index usage? Any chance of converting them to the permanent-index-usage version? I guess if there would be a way, you'd mention it already. Can optimizer hints, e.g. (A >= B,0) be used effectively here? I've tried recreating statistics (DROP STATISTICS ON table; CREATE STATISTICS table); tried out the SELECT, frozen on the 1st run, stopped after 30 minutes. At these very moments, a test with transferring that one index to CLUSTERED is in progress.. if that won't work, I'm basically out of options for the moment. Pavel "Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in message news:4a731529 (AT) forums-3-dub (DOT) sybase.com... Hmmm.... Interesting bit of info this: The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. That indicates the stats are getting rewritten between those stats due to the interleaving selects. [a test for this possibility could be to turn off the update_statistics option through a test run but dumping the histograms at each step will capture that part in fine detail] Rewritten one way the estimates are marginally worse for the plan that picks the index, rewritten another the estimates are marginally better for picking the index... and the coin turns solely on that proposition; apparently. {With the marginal difference one way, you get the performance you want but when it goes the other way gives you get a table scan.} Can I also assume these interleaving queries also use date ranges with different starting and ending sargs? [That can easiliy rebucket-ize the stats causing statistics to be grouped together or broken appart.] But since that seems to likely be the case then the index is probably giving you UN-reasonably better performance than can be expected (in the general case). Given the index is (now obviously) not declared as clustered, the optimizer is not able to estimate anywhere near the performance you are actually seeing and it is free to pick alternate plans whenever the estimate numbers dictate that. {Again my understanding here is that this is due to the data just happening to be captured in mostly clustered order, that and the starts are getting regrouped to not favour the index scanning plan.} To me it also sounds like the nature of the query is itself marginally optimizable as well and that is definitely something worth drilling into more. As to your X[B|C|S] woes, that confusion is normal. I believe the X-type is *determined* independantly from the content of the captured[ | dumped] information. The XB designation is determined from the estimated plan cost; but they will often also have actual statistics if the running cost also goes over. Further XC entries will not capture actual stats unless the operation completes (i.e. not cancelled, not closed). At least that was my experience with this facility. A little confusing but still quite useful as-is. ... NOTE THE HEAVY USE OF WORDS LIKE: marginal[ly], coin flipping references, ...? That is intentional since this question looks like optimizer is being asked to dance a jig at the edge of a precipace. [textual picture intended] "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a71db3b$2 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for these thoughts, since I've mentioned before that the statements runs very fast in ISQL and occassionally slow from the application, the plans confirming it's the 1 index usage difference were from LogExpensiveQueries already. I'm actually having issues with it as XC plans return estimates only... plus I see the statistics in XB, while XBs are having few seconds only, XC having 10k+ seconds. LEQ logs have been captured on 9.0.2.3632. The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. I'll respond with the results of tests (recreating histogram stats and clustered index). Pavel |
#14
| |||
| |||
|
|
If FORCE INDEX really doesn't require DBA, how come nobody protested when I mentioned this fact a couple of messages ago? |
others will not be tracking a thread that has been responded too.
I avoided the whole side discussion on index hints since I normally
reject table- and index-hints as being obtuse and (historically) hard to
migrate/upgrade. While upgrading may not become an issue in the
near future, the forcing of indexes is still side-stepping the basic
question about why this is not working stabily for you. To me, that
represents a real opportunity to learn about how these queries and
schema (your application) operates under different data distributions,
that will be missed now.
Anyway ... good luck
"Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote
Quote:
|
Nick, we have confirmed that the stats on the table are so on the edge of one of the optimizer's access plan creating formulas, that running another single SELECT on that table, altering the histogram stats slightly, is enough to induce a sequential scan for the original (previously fast) query on that table, causing it's runtime to drop to 9 hours (from 2 secs). This confirmation is no exact final mega in-depth critical absolute analysis, I acknowledge that. That is beyond my scope of knowledge, and I find much faster forcing the index than opening a help case with Sybase in this situation. Information that FORCE INDEX requires DBA authority was mouth-given to me in the past by my teammembers, sadly I don't remember the issue exactly. I've pursued this fact in the help today and found no inclination that FORCE INDEX (indexname) or WITH (INDEX (indexname)) would require DBA authority. For me personally, this is the solution right now unless someone objects reasonably. If FORCE INDEX really doesn't require DBA, how come nobody protested when I mentioned this fact a couple of messages ago? Nick, you've been helpful. Thanks very much. Pavel "Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in message news:4a7842a8$2 (AT) forums-3-dub (DOT) sybase.com... *** Since a clustering index did not correct the problem, that shoots down most of my theories about what you are actually hitting. * * * It is best to find out exactly how this is really * * * * * * failing now. I do strongly recommend * * * * * * supplying the plans in a real support case or * * * * * * at least start a new thread with the plans in tow.* * * There is a way to provide the optimizer using "index-hints" [SEE: "FROM clause" in the online docs looking at the INDEX() and FORCE INDEX() ] but it is exceeding rare to actually require those. Not to mention how much of a kludge they may be. It may end up being the right thing to do in some final analysis but since that analysis has not happened . . . "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a773ead$2 (AT) forums-3-dub (DOT) sybase.com... Update: Changed the index to clustered, the SELECT is hanging for a couple of hours now. This leaves me with desperate longing for the answer on the below question. Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a7715d4$3 (AT) forums-3-dub (DOT) sybase.com... Basically, this case nails down to: How to force index usage without FORCE INDEX? (Why does it require DBA auth anyway? Is it a try-out tool only?) Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a76fb16$1 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for your joint wit & wisdom. Yes, the SELECTs issued by the application 'in between' include a condition for the date range on the same problematic column (where the other, slow SELECT is given a sequential scan instead of index usage), so are you saying that histogram stats are rewritten and verge on the edge of an optimizer formula deciding about seq.scan / index usage? Any chance of converting them to the permanent-index-usage version? I guess if there would be a way, you'd mention it already. Can optimizer hints, e.g. (A >= B,0) be used effectively here? I've tried recreating statistics (DROP STATISTICS ON table; CREATE STATISTICS table); tried out the SELECT, frozen on the 1st run, stopped after 30 minutes. At these very moments, a test with transferring that one index to CLUSTERED is in progress.. if that won't work, I'm basically out of options for the moment. Pavel "Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in message news:4a731529 (AT) forums-3-dub (DOT) sybase.com... Hmmm.... Interesting bit of info this: The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. That indicates the stats are getting rewritten between those stats due to the interleaving selects. [a test for this possibility could be to turn off the update_statistics option through a test run but dumping the histograms at each step will capture that part in fine detail] Rewritten one way the estimates are marginally worse for the plan that picks the index, rewritten another the estimates are marginally better for picking the index... and the coin turns solely on that proposition; apparently. {With the marginal difference one way, you get the performance you want but when it goes the other way gives you get a table scan.} Can I also assume these interleaving queries also use date ranges with different starting and ending sargs? [That can easiliy rebucket-ize the stats causing statistics to be grouped together or broken appart.] But since that seems to likely be the case then the index is probably giving you UN-reasonably better performance than can be expected (in the general case). Given the index is (now obviously) not declared as clustered, the optimizer is not able to estimate anywhere near the performance you are actually seeing and it is free to pick alternate plans whenever the estimate numbers dictate that. {Again my understanding here is that this is due to the data just happening to be captured in mostly clustered order, that and the starts are getting regrouped to not favour the index scanning plan.} To me it also sounds like the nature of the query is itself marginally optimizable as well and that is definitely something worth drilling into more. As to your X[B|C|S] woes, that confusion is normal. I believe the X-type is *determined* independantly from the content of the captured[ | dumped] information. The XB designation is determined from the estimated plan cost; but they will often also have actual statistics if the running cost also goes over. Further XC entries will not capture actual stats unless the operation completes (i.e. not cancelled, not closed). At least that was my experience with this facility. A little confusing but still quite useful as-is. ... NOTE THE HEAVY USE OF WORDS LIKE: marginal[ly], coin flipping references, ...? That is intentional since this question looks like optimizer is being asked to dance a jig at the edge of a precipace. [textual picture intended] "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a71db3b$2 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for these thoughts, since I've mentioned before that the statements runs very fast in ISQL and occassionally slow from the application, the plans confirming it's the 1 index usage difference were from LogExpensiveQueries already. I'm actually having issues with it as XC plans return estimates only... plus I see the statistics in XB, while XBs are having few seconds only, XC having 10k+ seconds. LEQ logs have been captured on 9.0.2.3632. The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. I'll respond with the results of tests (recreating histogram stats and clustered index). Pavel |
#15
| |||
| |||
|
|
Nick, we have confirmed that the stats on the table are so on the edge of one of the optimizer's access plan creating formulas, that running another single SELECT on that table, altering the histogram stats slightly, is enough to induce a sequential scan for the original (previously fast) query on that table, causing it's runtime to drop to 9 hours (from 2 secs). This confirmation is no exact final mega in-depth critical absolute analysis, I acknowledge that. That is beyond my scope of knowledge, and I find much faster forcing the index than opening a help case with Sybase in this situation. Information that FORCE INDEX requires DBA authority was mouth-given to me in the past by my teammembers, sadly I don't remember the issue exactly. I've pursued this fact in the help today and found no inclination that FORCE INDEX (indexname) or WITH (INDEX (indexname)) would require DBA authority. For me personally, this is the solution right now unless someone objects reasonably. If FORCE INDEX really doesn't require DBA, how come nobody protested when I mentioned this fact a couple of messages ago? Nick, you've been helpful. Thanks very much. Pavel "Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in message news:4a7842a8$2 (AT) forums-3-dub (DOT) sybase.com... *** Since a clustering index did not correct the problem, that shoots down most of my theories about what you are actually hitting. * * * It is best to find out exactly how this is really * * * * * * failing now. I do strongly recommend * * * * * * supplying the plans in a real support case or * * * * * * at least start a new thread with the plans in tow.* * * There is a way to provide the optimizer using "index-hints" [SEE: "FROM clause" in the online docs looking at the INDEX() and FORCE INDEX() ] but it is exceeding rare to actually require those. Not to mention how much of a kludge they may be. It may end up being the right thing to do in some final analysis but since that analysis has not happened . . . "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a773ead$2 (AT) forums-3-dub (DOT) sybase.com... Update: Changed the index to clustered, the SELECT is hanging for a couple of hours now. This leaves me with desperate longing for the answer on the below question. Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a7715d4$3 (AT) forums-3-dub (DOT) sybase.com... Basically, this case nails down to: How to force index usage without FORCE INDEX? (Why does it require DBA auth anyway? Is it a try-out tool only?) Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a76fb16$1 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for your joint wit & wisdom. Yes, the SELECTs issued by the application 'in between' include a condition for the date range on the same problematic column (where the other, slow SELECT is given a sequential scan instead of index usage), so are you saying that histogram stats are rewritten and verge on the edge of an optimizer formula deciding about seq.scan / index usage? Any chance of converting them to the permanent-index-usage version? I guess if there would be a way, you'd mention it already. Can optimizer hints, e.g. (A >= B,0) be used effectively here? I've tried recreating statistics (DROP STATISTICS ON table; CREATE STATISTICS table); tried out the SELECT, frozen on the 1st run, stopped after 30 minutes. At these very moments, a test with transferring that one index to CLUSTERED is in progress.. if that won't work, I'm basically out of options for the moment. Pavel "Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in message news:4a731529 (AT) forums-3-dub (DOT) sybase.com... Hmmm.... Interesting bit of info this: The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. That indicates the stats are getting rewritten between those stats due to the interleaving selects. [a test for this possibility could be to turn off the update_statistics option through a test run but dumping the histograms at each step will capture that part in fine detail] Rewritten one way the estimates are marginally worse for the plan that picks the index, rewritten another the estimates are marginally better for picking the index... and the coin turns solely on that proposition; apparently. {With the marginal difference one way, you get the performance you want but when it goes the other way gives you get a table scan.} Can I also assume these interleaving queries also use date ranges with different starting and ending sargs? [That can easiliy rebucket-ize the stats causing statistics to be grouped together or broken appart.] But since that seems to likely be the case then the index is probably giving you UN-reasonably better performance than can be expected (in the general case). Given the index is (now obviously) not declared as clustered, the optimizer is not able to estimate anywhere near the performance you are actually seeing and it is free to pick alternate plans whenever the estimate numbers dictate that. {Again my understanding here is that this is due to the data just happening to be captured in mostly clustered order, that and the starts are getting regrouped to not favour the index scanning plan.} To me it also sounds like the nature of the query is itself marginally optimizable as well and that is definitely something worth drilling into more. As to your X[B|C|S] woes, that confusion is normal. I believe the X-type is *determined* independantly from the content of the captured[ | dumped] information. The XB designation is determined from the estimated plan cost; but they will often also have actual statistics if the running cost also goes over. Further XC entries will not capture actual stats unless the operation completes (i.e. not cancelled, not closed). At least that was my experience with this facility. A little confusing but still quite useful as-is. ... NOTE THE HEAVY USE OF WORDS LIKE: marginal[ly], coin flipping references, ...? That is intentional since this question looks like optimizer is being asked to dance a jig at the edge of a precipace. [textual picture intended] "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a71db3b$2 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for these thoughts, since I've mentioned before that the statements runs very fast in ISQL and occassionally slow from the application, the plans confirming it's the 1 index usage difference were from LogExpensiveQueries already. I'm actually having issues with it as XC plans return estimates only... plus I see the statistics in XB, while XBs are having few seconds only, XC having 10k+ seconds. LEQ logs have been captured on 9.0.2.3632. The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. I'll respond with the results of tests (recreating histogram stats and clustered index). Pavel |
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere
Blog: http://iablog.sybase.com/paulley
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://case-express.sybase.com
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport
#16
| |||
| |||
|
|
Your build of 9.0.2 (2551), Pavel, is over 1300 builds old. Before resorting to an index hint, I might try applying the latest EBF to rule out bug fixes to selectivity estimation/histogram maintenance that have been made over the intervening years. If nothing changes, then I'd use the hint (document it) and move on. Glenn Pavel Karady wrote: Nick, we have confirmed that the stats on the table are so on the edge of one of the optimizer's access plan creating formulas, that running another single SELECT on that table, altering the histogram stats slightly, is enough to induce a sequential scan for the original (previously fast) query on that table, causing it's runtime to drop to 9 hours (from 2 secs). This confirmation is no exact final mega in-depth critical absolute analysis, I acknowledge that. That is beyond my scope of knowledge, and I find much faster forcing the index than opening a help case with Sybase in this situation. Information that FORCE INDEX requires DBA authority was mouth-given to me in the past by my teammembers, sadly I don't remember the issue exactly. I've pursued this fact in the help today and found no inclination that FORCE INDEX (indexname) or WITH (INDEX (indexname)) would require DBA authority. For me personally, this is the solution right now unless someone objects reasonably. If FORCE INDEX really doesn't require DBA, how come nobody protested when I mentioned this fact a couple of messages ago? Nick, you've been helpful. Thanks very much. Pavel "Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in message news:4a7842a8$2 (AT) forums-3-dub (DOT) sybase.com... *** Since a clustering index did not correct the problem, that shoots down most of my theories about what you are actually hitting. * * * It is best to find out exactly how this is really * * * * * * failing now. I do strongly recommend * * * * * * supplying the plans in a real support case or * * * * * * at least start a new thread with the plans in tow.* * * There is a way to provide the optimizer using "index-hints" [SEE: "FROM clause" in the online docs looking at the INDEX() and FORCE INDEX() ] but it is exceeding rare to actually require those. Not to mention how much of a kludge they may be. It may end up being the right thing to do in some final analysis but since that analysis has not happened . . . "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a773ead$2 (AT) forums-3-dub (DOT) sybase.com... Update: Changed the index to clustered, the SELECT is hanging for a couple of hours now. This leaves me with desperate longing for the answer on the below question. Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a7715d4$3 (AT) forums-3-dub (DOT) sybase.com... Basically, this case nails down to: How to force index usage without FORCE INDEX? (Why does it require DBA auth anyway? Is it a try-out tool only?) Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a76fb16$1 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for your joint wit & wisdom. Yes, the SELECTs issued by the application 'in between' include a condition for the date range on the same problematic column (where the other, slow SELECT is given a sequential scan instead of index usage), so are you saying that histogram stats are rewritten and verge on the edge of an optimizer formula deciding about seq.scan / index usage? Any chance of converting them to the permanent-index-usage version? I guess if there would be a way, you'd mention it already. Can optimizer hints, e.g. (A >= B,0) be used effectively here? I've tried recreating statistics (DROP STATISTICS ON table; CREATE STATISTICS table); tried out the SELECT, frozen on the 1st run, stopped after 30 minutes. At these very moments, a test with transferring that one index to CLUSTERED is in progress.. if that won't work, I'm basically out of options for the moment. Pavel "Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in message news:4a731529 (AT) forums-3-dub (DOT) sybase.com... Hmmm.... Interesting bit of info this: The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. That indicates the stats are getting rewritten between those stats due to the interleaving selects. [a test for this possibility could be to turn off the update_statistics option through a test run but dumping the histograms at each step will capture that part in fine detail] Rewritten one way the estimates are marginally worse for the plan that picks the index, rewritten another the estimates are marginally better for picking the index... and the coin turns solely on that proposition; apparently. {With the marginal difference one way, you get the performance you want but when it goes the other way gives you get a table scan.} Can I also assume these interleaving queries also use date ranges with different starting and ending sargs? [That can easiliy rebucket-ize the stats causing statistics to be grouped together or broken appart.] But since that seems to likely be the case then the index is probably giving you UN-reasonably better performance than can be expected (in the general case). Given the index is (now obviously) not declared as clustered, the optimizer is not able to estimate anywhere near the performance you are actually seeing and it is free to pick alternate plans whenever the estimate numbers dictate that. {Again my understanding here is that this is due to the data just happening to be captured in mostly clustered order, that and the starts are getting regrouped to not favour the index scanning plan.} To me it also sounds like the nature of the query is itself marginally optimizable as well and that is definitely something worth drilling into more. As to your X[B|C|S] woes, that confusion is normal. I believe the X-type is *determined* independantly from the content of the captured[ | dumped] information. The XB designation is determined from the estimated plan cost; but they will often also have actual statistics if the running cost also goes over. Further XC entries will not capture actual stats unless the operation completes (i.e. not cancelled, not closed). At least that was my experience with this facility. A little confusing but still quite useful as-is. ... NOTE THE HEAVY USE OF WORDS LIKE: marginal[ly], coin flipping references, ...? That is intentional since this question looks like optimizer is being asked to dance a jig at the edge of a precipace. [textual picture intended] "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a71db3b$2 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for these thoughts, since I've mentioned before that the statements runs very fast in ISQL and occassionally slow from the application, the plans confirming it's the 1 index usage difference were from LogExpensiveQueries already. I'm actually having issues with it as XC plans return estimates only... plus I see the statistics in XB, while XBs are having few seconds only, XC having 10k+ seconds. LEQ logs have been captured on 9.0.2.3632. The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. I'll respond with the results of tests (recreating histogram stats and clustered index). Pavel -- Glenn Paulley Director, Engineering (Query Processing) Sybase iAnywhere Blog: http://iablog.sybase.com/paulley EBF's and Patches: http://downloads.sybase.com choose SQL Anywhere Studio >> change 'time frame' to all To Submit Bug Reports: http://case-express.sybase.com SQL Anywhere Studio Supported Platforms and Support Status http://my.sybase.com/detail?id=1002288 Whitepapers, TechDocs, and bug fixes are all available through the Sybase iAnywhere pages at http://www.sybase.com/products/datab...chnicalsupport |
#17
| |||
| |||
|
|
Greetings Glenn, EBF 3632 was the first thing tried out, same behavior there, that's why I came here as the last resort. Your suggestion is exactly what happened. Also, EBF 3632 Index Consultant was tried out, recommended 0 new indexes (indices?). Thanks, Pavel "Glenn Paulley [Sybase iAnywhere]" <paulley (AT) ianywhere (DOT) com> wrote in message news:4a8051b3$3 (AT) forums-3-dub (DOT) sybase.com... Your build of 9.0.2 (2551), Pavel, is over 1300 builds old. Before resorting to an index hint, I might try applying the latest EBF to rule out bug fixes to selectivity estimation/histogram maintenance that have been made over the intervening years. If nothing changes, then I'd use the hint (document it) and move on. Glenn Pavel Karady wrote: Nick, we have confirmed that the stats on the table are so on the edge of one of the optimizer's access plan creating formulas, that running another single SELECT on that table, altering the histogram stats slightly, is enough to induce a sequential scan for the original (previously fast) query on that table, causing it's runtime to drop to 9 hours (from 2 secs). This confirmation is no exact final mega in-depth critical absolute analysis, I acknowledge that. That is beyond my scope of knowledge, and I find much faster forcing the index than opening a help case with Sybase in this situation. Information that FORCE INDEX requires DBA authority was mouth-given to me in the past by my teammembers, sadly I don't remember the issue exactly. I've pursued this fact in the help today and found no inclination that FORCE INDEX (indexname) or WITH (INDEX (indexname)) would require DBA authority. For me personally, this is the solution right now unless someone objects reasonably. If FORCE INDEX really doesn't require DBA, how come nobody protested when I mentioned this fact a couple of messages ago? Nick, you've been helpful. Thanks very much. Pavel "Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in message news:4a7842a8$2 (AT) forums-3-dub (DOT) sybase.com... *** Since a clustering index did not correct the problem, that shoots down most of my theories about what you are actually hitting. * * * It is best to find out exactly how this is really * * * * * * failing now. I do strongly recommend * * * * * * supplying the plans in a real support case or * * * * * * at least start a new thread with the plans in tow.* * * There is a way to provide the optimizer using "index-hints" [SEE: "FROM clause" in the online docs looking at the INDEX() and FORCE INDEX() ] but it is exceeding rare to actually require those. Not to mention how much of a kludge they may be. It may end up being the right thing to do in some final analysis but since that analysis has not happened . . . "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a773ead$2 (AT) forums-3-dub (DOT) sybase.com... Update: Changed the index to clustered, the SELECT is hanging for a couple of hours now. This leaves me with desperate longing for the answer on the below question. Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a7715d4$3 (AT) forums-3-dub (DOT) sybase.com... Basically, this case nails down to: How to force index usage without FORCE INDEX? (Why does it require DBA auth anyway? Is it a try-out tool only?) Pavel "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a76fb16$1 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for your joint wit & wisdom. Yes, the SELECTs issued by the application 'in between' include a condition for the date range on the same problematic column (where the other, slow SELECT is given a sequential scan instead of index usage), so are you saying that histogram stats are rewritten and verge on the edge of an optimizer formula deciding about seq.scan / index usage? Any chance of converting them to the permanent-index-usage version? I guess if there would be a way, you'd mention it already. Can optimizer hints, e.g. (A >= B,0) be used effectively here? I've tried recreating statistics (DROP STATISTICS ON table; CREATE STATISTICS table); tried out the SELECT, frozen on the 1st run, stopped after 30 minutes. At these very moments, a test with transferring that one index to CLUSTERED is in progress.. if that won't work, I'm basically out of options for the moment. Pavel "Nick Elson [Sybase iAnywhere]" <@nick@.@elson@@sybase@.@com@> wrote in message news:4a731529 (AT) forums-3-dub (DOT) sybase.com... Hmmm.... Interesting bit of info this: The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. That indicates the stats are getting rewritten between those stats due to the interleaving selects. [a test for this possibility could be to turn off the update_statistics option through a test run but dumping the histograms at each step will capture that part in fine detail] Rewritten one way the estimates are marginally worse for the plan that picks the index, rewritten another the estimates are marginally better for picking the index... and the coin turns solely on that proposition; apparently. {With the marginal difference one way, you get the performance you want but when it goes the other way gives you get a table scan.} Can I also assume these interleaving queries also use date ranges with different starting and ending sargs? [That can easiliy rebucket-ize the stats causing statistics to be grouped together or broken appart.] But since that seems to likely be the case then the index is probably giving you UN-reasonably better performance than can be expected (in the general case). Given the index is (now obviously) not declared as clustered, the optimizer is not able to estimate anywhere near the performance you are actually seeing and it is free to pick alternate plans whenever the estimate numbers dictate that. {Again my understanding here is that this is due to the data just happening to be captured in mostly clustered order, that and the starts are getting regrouped to not favour the index scanning plan.} To me it also sounds like the nature of the query is itself marginally optimizable as well and that is definitely something worth drilling into more. As to your X[B|C|S] woes, that confusion is normal. I believe the X-type is *determined* independantly from the content of the captured[ | dumped] information. The XB designation is determined from the estimated plan cost; but they will often also have actual statistics if the running cost also goes over. Further XC entries will not capture actual stats unless the operation completes (i.e. not cancelled, not closed). At least that was my experience with this facility. A little confusing but still quite useful as-is. ... NOTE THE HEAVY USE OF WORDS LIKE: marginal[ly], coin flipping references, ...? That is intentional since this question looks like optimizer is being asked to dance a jig at the edge of a precipace. [textual picture intended] "Pavel Karady" <firstname.lastname (AT) nospam_kogerusa (DOT) com> wrote in message news:4a71db3b$2 (AT) forums-3-dub (DOT) sybase.com... Nick, thanks for these thoughts, since I've mentioned before that the statements runs very fast in ISQL and occassionally slow from the application, the plans confirming it's the 1 index usage difference were from LogExpensiveQueries already. I'm actually having issues with it as XC plans return estimates only... plus I see the statistics in XB, while XBs are having few seconds only, XC having 10k+ seconds. LEQ logs have been captured on 9.0.2.3632. The issue has been reproduced in a separated environment with only 1 application running. This application issues the faulting SELECT a few times, then a different SELECT, then the faulting one a few times, then another different one, then the faulting one and voila the faulting one is slow. I'll respond with the results of tests (recreating histogram stats and clustered index). Pavel -- Glenn Paulley Director, Engineering (Query Processing) Sybase iAnywhere Blog: http://iablog.sybase.com/paulley EBF's and Patches: http://downloads.sybase.com choose SQL Anywhere Studio >> change 'time frame' to all To Submit Bug Reports: http://case-express.sybase.com SQL Anywhere Studio Supported Platforms and Support Status http://my.sybase.com/detail?id=1002288 Whitepapers, TechDocs, and bug fixes are all available through the Sybase iAnywhere pages at http://www.sybase.com/products/datab...chnicalsupport |
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere
Blog: http://iablog.sybase.com/paulley
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://case-express.sybase.com
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.