THE 10053 TRACE FILE in Java

Generator Code 3/9 in Java THE 10053 TRACE FILE

Final - All Rows Plan: JOIN ORDER: 11 CST: 361 CDN: 1 RSC: 361 RSP: 361 BYTES: 96 IO-RSC: 360 IO-RSP: 360 CPU-RSC: 5892137 CPU-RSP: 5892137

It s a lot of work to get this far, and a lot of it is very repetitive. To summarize what has happened with the join orders, I have extracted the orders that the optimizer investigated, and listed them separately here. It can be quite informative simply to use grep (Unix) or find (Windows) to list all the lines starting with Join order to see how many join orders the optimizer investigated and how many subplans. Join Join Join Join Join Join Join Join Join Join Join Join Join Join Join Join Join Join order[1]: order[2]: order[3]: order[4]: order[5]: order[6]: order[7]: order[8]: order[9]: order[10]: order[11]: order[12]: order[13]: order[14]: order[15]: order[16]: order[17]: order[18]: CHILD[C]#0 CHILD[C]#0 CHILD[C]#0 CHILD[C]#0 PARENT[P]#1 PARENT[P]#1 PARENT[P]#1 PARENT[P]#1 PARENT[P]#1 PARENT[P]#1 GRANDPARENT[GP]#2 GREATGRANDPARENT[GGP]#3 PARENT[P]#1 GREATGRANDPARENT[GGP]#3 GRANDPARENT[GP]#2 GRANDPARENT[GP]#2 PARENT[P]#1 GREATGRANDPARENT[GGP]#3 GREATGRANDPARENT[GGP]#3 PARENT[P]#1 GRANDPARENT[GP]#2 CHILD[C]#0 GRANDPARENT[GP]#2 GREATGRANDPARENT[GGP]#3 CHILD[C]#0 GREATGRANDPARENT[GGP]#3 GRANDPARENT[GP]#2 GRANDPARENT[GP]#2 CHILD[C]#0 GREATGRANDPARENT[GGP]#3 GRANDPARENT[GP]#2 GREATGRANDPARENT[GGP]#3 CHILD[C]#0 GREATGRANDPARENT[GGP]#3 CHILD[C]#0 GRANDPARENT[GP]#2 CHILD[C]#0 PARENT[P]#1 GREATGRANDPARENT[GGP]#3 PARENT[P]#1 CHILD[C]#0 GREATGRANDPARENT[GGP]#3 PARENT[P]#1 GREATGRANDPARENT[GGP]#3 CHILD[C]#0 GREATGRANDPARENT[GGP]#3 CHILD[C]#0 PARENT[P]#1 GREATGRANDPARENT[GGP]#3 PARENT[P]#1 CHILD[C]#0 CHILD[C]#0 PARENT[P]#1 GRANDPARENT[GP]#2 PARENT[P]#1 CHILD[C]#0 GRANDPARENT[GP]#2 GRANDPARENT[GP]#2 CHILD[C]#0 PARENT[P]#1 GRANDPARENT[GP]#2 PARENT[P]#1 CHILD[C]#0

GRANDPARENT[GP]#2 GRANDPARENT[GP]#2 GRANDPARENT[GP]#2 GRANDPARENT[GP]#2 GRANDPARENT[GP]#2

GREATGRANDPARENT[GGP]#3 GREATGRANDPARENT[GGP]#3 GREATGRANDPARENT[GGP]#3 GREATGRANDPARENT[GGP]#3

Reduced to the simple numeric form to decrease the visual confusion, and listing the missing join orders at the spot that we would expect them to be, we get the following: Join order[1]: Join order[2]: Join order[3]: Skipped: Join order[4]: Skipped: #0 #0 #0 #0, #0 #0, #1 #1 #2 #2, #3 #3, #2 #3 #1 #3, #1 #2, #3 #2 #3 #1 #2 #1 Best so far Failed on 3rd Failed on 2nd Failed on 2nd ***

order[5]: order[6]: order[7]: order[8]: order[9]: Skipped:

#1 #1 #1 #1 #1 #1, #2 #2, #2 #2 #2 #2 #3 #3, #3 #3, #3 #3

#0 #0 #2 #2 #3 #3, #0 #0, #1 #1 #3 #3 #0 #0, #1 #1, #2 #2

#2 #3 #0 #3 #0 #2, #1 #3, #0 #3 #0 #1 #1 #2, #0 #2, #0 #1

#3 #2 #3 #0 #2 #0 #3 #1 #3 #0 #1 #0 #2 #1 #2 #0 #1 #0

*** *** ***

Join order[10]: Skipped: Join order[11]: Join order[12]: Join order[13]: Join order[14]: Join order[15]: Skipped: Join order[16]: Skipped: Join order[17]: Join order[18]:

Failed on 2nd Best so far Failed on 3rd Failed on 3rd Failed on 2nd Failed on 2nd Failed on 3rd Failed on 3rd *** ***

As you can see from this listing, for small numbers of tables, the optimizer will cycle through all the possible permutations in a very straightforward fashion. But it will simply skip some permutations that cannot possibly be any good, because a similar permutation with the same starting order of tables is known to be a bad choice. In fact, with a potential 24 join orders, Oracle only ran the calculations to completion on six of the possibilities (marked with ***). For larger numbers of tables, things can be different. In the first place, the optimizer may decide that after testing a few join orders the cost of executing the query is so small that it might as well go ahead without checking any more join orders. Essentially, as soon as the time spent in checking join orders exceeds the predicted run time, Oracle runs with the best its got so far. This does mean that if the optimizer happens to pick an unsuitable starting table because it got the cardinality wrong the standard permutation cycle can cause a problem. In a ten-table join, it would take a long time to cycle the first table out of pole position. So the optimizer has an algorithm that comes into play with larger numbers of tables, and the modified algorithm starts switching the leading tables around if too many join orders have been tested without a reasonable cost appearing.

Note For users of Oracle 8, I understand that changing the parameter optimizer_max_permutations from its default of 80,000 to any other value will make Oracle 8 switch to the new algorithm. For users of Oracle 10, you might note that there is a new parameter called _optimizer_join_order_control, which takes the value 3 so Oracle Corp. may have come up with yet another, better algorithm for permuting through the join orders.