Thanks for taking your time to report this sorting problem to us.
If you are looking at the order of the SQL alternatives in the Tuning Lab after the optimization process is finished but before the execution of the SQL alternatives has taken place, the SQL statements should be displayed in the correct numerical order when you click the Scenario column heading in the Scenario Explorer window to change the order from ascending to descending.
If you have executed the SQL alternatives, then the SQL statements are first sorted into 3 groups and displayed in those groups. These groups are 1) Executed SQL that completed the execution, 2) Executed SQL that was terminated because it ran too long, and 3) unexecuted SQL alternatives.
So after the SQL alternatives have been executed, the alternatives can no longer be sorted in a “correct numerical order” if 2 or more of the above group exist. But within each group the sort order should be correct.
But I find the following problem with sorting the SQL statements after execution when the above 3 groups are created. If this is not the problem that you are seeing, can you please give more details on where you are seeing the problem and steps to reproduce it so we can find it and fix it.
This sorting problem I found happens when these conditions are present.
- After executing only the original SQL statement and some of the alternatives.
- Some of the executed SQL statements were terminated before completion.
I found that the SQL alternatives were sorted into four groups. There were a group of terminated SQL alternatives before the unexecuted SQL and another group of terminated SQL alternatives after the unexecuted SQL. All the terminated SQL should be grouped together.