LEFT JOIN Performance Issue

I have a view, which has a column that comes from query

Without this query my count(*) against that view executes in less than 2 seconds, whereas it takes 2 days for the view with above extra column to show results of count(*).

IMPORTANT POINT : We have 4 environments DEV, TEST, UAT, and PROD. This problem only persists in TEST, whereas DEV and UAT are fine.

Many people are suggesting issue is with trim(lower()), but that should cause problem in other environments too, but it does not.

When I am in this situation (i.e. a query very slow in an instance and fast in another) I check what differences I can find between the environments:

• Compare EXPLAIN PLAN for your count query, and for that subquery, on the different environments, it might give an idea about where to check for differences
• Is the data different accross environments ? A difference in the data might explain a difference in performance.
• Are the optimizer statistics up-to-date on all related tables in TEST?
• Do the instances have the same UNDO tablespace size and TEMP tablespace size ?
• Is there a difference in hardware or in CPU/memory load accross environments ?
• Is there a difference in database parameters ?

I hope that helps.