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.
David Kurtz
/*+Go-Faster*/ Consultancy
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?