|
I am looking for suggestions to troubleshoot a query that executes between 1 and 2 seconds, but takes 20 or minutes to actually retrieve the data. On certain other machines it never finishes. Index suggestions are non-existent. There are only several hundred thousands rows, and yes long complicated query. Odd: I can run the query in interactive sql; stop it in the middle of the 'hang', then re-run it, and it returns all of the data in about 5 seconds. As if the cache doesn't know what to do the first time around. How do I troubleshoot something like this? If I run the plan what am I looking for? |
|
I solved this eventually - there was a hot fix (.3867) that took care of the issue. However, I still would like to know if there is better documentation on how to read the plan information - both graphical and text. I read the docx and clear as mud. There are so many statistics, and it would be nice to know what is 'good' about a certain figure. Thanks. 2
Reading graphical plans is admittedly a bit of an art form. However, there is some 'basic' information you can generically determine from the plans: In the graphical plans, black thick lines indicate where a majority of the time is going. If you have detailed graphical plans (with statistics, not just estimates), you can drill into the actual operation that is slow and see 'how slow it is' by looking at the summary subnode statistics with the same node. Typically the time taken will either be related to the number of I/Os required to pull the table pages into the cache from disk (I/O-bound), or the number of CPU operations on the number of rows we're trying to manage already in the cache (CPU-bound). Statistics are neither good nor bad by themselves - they are just listings of distributions of values in a histogram. The way they can be 'bad' is if the distributions of the values stored in the histogram tables do not actually match the distribution of values in the database table. The way to determine this for certain is to run 'dbhist' and directly compare the results with queries. If they are not 'correct', you should run 'CREATE STATISTICS' to have the database rebuild the statistical information. SQL Anywhere 12 and up has an enhancement known as the 'statistics governor' that runs in the background to help avoid these situations. If you're comparing plans to try and understand why the optimizer is picking a different index/scan strategy, look for changes in the selectivity estimates on predicates that you're using. If you ever need immediate help to understand a graphical plan, you would want to open a technical support case with us directly.
(15 Apr '13, 09:17)
Jeff Albion
|
How do you test this query? (Note: DBISQL is usually not well-suited here because it does much work behind the curtains...) How many rows are returned? What is the value of database option "optimization_goal"?
I think it's difficult to give helpful hints without knowing more details about the actual query...