1 like
·
216 reads
4 comments
Caro, nice write-up. You wrote, "Due to the complex nature of our queries this took sometimes 30 seconds and more.. :(". IMO it would be interesting to explore why parsing took so long. There are several possible reasons for that. In any case, 30 seconds is far too long. It should always be less than a second which probably would have been unnoticed by the users. 30 seconds is also not acceptable for new sessions. Therefore, fixing the parsing issue would have been an adequate solution approach.
Hi Philipp, thank you very much for your response :) I've added a note for the long parse time. The queries were using many objects via Views on Views on Views with the MViews in between. So we used a lot of objects (can't remember the exact number anymore) and the parse time seemed to be reasonable. At least the Tuning guys didn't mention it specifically :D
What bothers me is the different behavior when selecting data from a query with Query Rewrite and selecting data from the Materialized View directly. I can imagine that the Optimizer is forced to get a new plan due to changes in the data. Maybe it would then decide to query the data directly from the table and not from the Materialized View 🤔
I would expect a complete refresh to invalidate cursors on those materialized views.
I have successfully used fast refreshed materialized views in a high-parse environment (to introduce HCC on Exadata in PeopleSoft Financials).
The parse is a symptom. The question is what is the cause of so much parse, and why is it such a problem for you?
Hi David, I think the real problem was a series of bad decisions :D I also think that Query Rewrite is not bad at all. The behavior was unexpected and should be checked before implementing MViews with such an option. But I think this is applies for any technical solution.