You've got an 80 line SQL riddled with views, sub-queries, and SELECT expressions. Did you honestly expect it to scale well?

In my experience, a SQL this complex is doomed to failure. If SQL statements reflect a business requirement, I find it improbable that a real business requirement could be this ridiculously complex. That means that either the data modeller was an oxygen-thief, or the author of the SQL did not understand the model.

My suggestion would be to start from scratch. Find out what the SQL is actually supposed to do, build it up bit by bit, and tune it as you go. Avoid views on views. Avoid views containing GROUP BY, DISTINCT, UNION, or anything even remotely complex (stick to plain inner joins). Avoid outer joining to views.Ross Leishman

Hi Ross,
I tried to make the query simpler by putting in values where it can be done. Now it is taking only 6 seconds but again the records may increase upto millions and this might take more time than taking now.

Please advice on the query with explain plan below to increase the performance so that it can process faster when records ae in millions with many concurrent users online.