Hi team,
We've been working with a customer on optimizing a couple of critical queries and for one of the queries in the optimizer events I see the MEMORY LIMIT HIT message:The optimizer used all of its allocated memory while planning. The query joins multiple views, and it is not possible to rewrite it.
I've looked up this error on the internal resources, and I found that we can allocate more memory for the optimizer with the database parameter MaxOptMemMB.
I have a couple of questions regrading this:
- What actually happens when the optimizer hits the memory limit while planning? What plan does it chose?
- Could the optimizer choose less costly plans when it has more memory available for planning but end up being too conservative (thus having higher execution times and potentially affecting other query performance that do not need more than the default for planning) I've been testing on my environment and had cases where the same query runs faster when it hits the 100MB limit (but costs a bit more)
- Is there a way to set MaxOptMemMB on session level for testing
- Is it safe to increase it?
Many thanks,
Chaima
