Thank you for all your inputs. I am working on the suggestions but in mean time I tried below and PLSQL function looks no issue here. It looks like these functions are applied on whole result set instead of where these should get applied.
I tried running below and it got completed in 86 seconds. Please have a look and suggest if we can restrict these function in their actual inline view i.e. tb in my case.
I'm not sure I completely understand what you're trying to express so I might state some points that are already obvious to you:
- Why do you say "PL/SQL function looks no issue here" when you spent almost 68 seconds in the VIEW operator that evaluates the PL/SQL functions? I have to admit that the Rowsource Statistics are potentially misleading in this case since the work performed by the PL/SQL functions should be showing up in the three SELECT FROM DUAL scalar subquery operators rather than contributing to the VIEW operator. So if it takes 68 seconds to execute those three functions for 45K rows, it's to be expected that it takes approx. 10 minutes for 500K rows as you did in your original query where those functions were used to filter from 500K to 50K rows.
- In principle it's a good idea to wrap those PL/SQL function in a SELECT FROM DUAL subquery since that way it might benefit from the scalar subquery caching mechanism built into the SQL runtime engine of Oracle. Not sure you did this intentionally in your latest example. But in that latest example you didn't get any benefit from that caching, potentially because the combination of parameters was more or less unique across all 45K rows. You might get some benefit by using the scalar subquery caching in your original query though - ideally the data would arrive at that operator in the order of the two parameters used to call these functions to get most out of the caching
- Yet the better approach would be looking closer at what those PL/SQL function do and see if you can make that working more efficiently - perhaps it's possible to do that in pure SQL rather than calling PL/SQL - it's very likely this would provide a significant improvement
- If what you wrote is supposed to express that you think that those PL/SQL functions should be called less often because they should be applied to restricted data set, then this of course would be another reasonable optimisation. However I'm not entirely sure how that is supposed to work because currently these PL/SQL functions seem to be applied as a filter to a larger result set
So from my point of view still the most effective way to speed up that query is looking at those PL/SQL functions.
I haven't re-formatted your original query, so I can't compare what you've done with the shorter form to see what critical changes you may have made.
I think, however, that Randolf has covered the significant points.
I have written up a blog note demonstrating the issue of the "missing time" in the SQL Monitor report: https://jonathanlewis.wordpress.com/2019/03/21/lost-time/ and one of the observations from that was that the OUTER join resulted in the predicate moving into a separate FILTER operation - which has the effect in your case of having it evaluated 500K times instead of 45K, AND adds in the extra CPU impact of having 500K rows of data passing up through two call stacks.
Your smaller query here looks like it's a replacement for the bit of the query that produced operations 35 - 45 of the original, though the original plan shows a "right outer hash join" and this query is NOT an outer join. If it's producing the right results maybe there's an "outer" in the original query that needn't be there. If you can't eliminate the OUTER from the original then perhaps you could find the place where the three tables are joins an replace that section of the original query with this query - but adding the hint /*+ no_merge */ to this query so that Oracle has to generate the row source with this plan rather than finding a way of re-introducing an unwanted OUTER join.