I am trying to optimize my SQL in the Tuning Lab with the optimization level set to 10. Why does it take so long to return results?
The optimization involved a very complicated method to rewrite SQL, which returns the alternatives with different explain plan. The most resource spending, most of CPU usage, and the most CPU time occurs during explain plan generation and waiting for the database to return the results. When using level 10 to optimize, the optimizer will increase the quota of SQL rewritten and number of hints applied. This means more SQL need to generate explain plan and more resource spent. That is the reason why the default intelligent level is set to 4 rather than 10.
