Query Tunning

[gandolf989]

Originally posted by stmontgo
Yes follow along closely! Let's see what happens next. My money is on PS Financials with NVISION Oracle 8i with optimizer_max_permutations=80000 but that's just a wild guess

I'm not qualified to make that kind of guess with any accuracy. According to www.dba-oracle.com:

The _optimizer_search_limit and _optimizer_max_permutations parameters work together, and the optimizer will generate possible table joins permutations until the value specified by _optimizer_search_limit or _optimizer_max_permutations is exceeded. When the optimizer stops evaluating table join combinations, it will choose the combination with the lowest cost. For example, queries joining nine tables together will exceed the optimizer_search_limit but still may spend expensive time attempting to evaluate all 362,880 possible table join orders (nine factorial) until the optimizer_max_permutations parameter has exceeded its default limit of 80,000 table join orders.

And according to www.orafaq.com:

NOTE: This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.

But why does this query suck?