I have a complex query with over a 1,000 lines that includes a lot of and\or statements and IN statements. When I attempt to optimize at level 2 and after about 20 minutes it indicated that it did not find any alternatives. I changed the intelligence to level 3 and after another 20 minutes or so I get the following error:
Exception happens at
Class : TComThread
Method : CreateNewLab
Message: Failed to create new tuning lab
TIniFN.ReadSection - Exception failure, Error Message
- Out of memory
A memory issue can happened at complex SQL with any number of alternatives found as it's checking its syntax and performing an explain plan.
Enhancement Request ST40166 has been submitted to Development for consideration in a future release of Quest SQL Optimizer for Oracle.