Normally, a view cannot be joined with an index-based nested loop join, since a view can't have an index defined on it. Join predicate pushdown (JPPD) is an optimizer query transformation that allows a view to be joined with an index-based nested-loop join method, which may provide a more optimal execution plan. More information on this transformation can be found on the Optimizer blog https://blogs.oracle.com/optimizer/optimizer-transformation:-join-predicate-pushdown

With this transformation, the view remains a separate query block, but it now contains the join predicate, which is pushed down from its containing query block into the view. The view thus becomes correlated and must be evaluated for each row of the outer query block. These pushed-down join predicates, once inside the view, open up new index access paths on the base tables inside the view; this allows the view to be joined with index-based nested-loop join method, thereby enabling the optimizer to select an efficient execution plan.


Take the following query for example,

SELECT s.order_id, V.prod_id
FROM   sales s,
       (SELECT p.prod_id, p.prod_name, p.prod_desc
        FROM   products p, product_details pd
        WHERE  p.prod_category_id = pd.prod_category_id
       ) V
WHERE  s.prod_id = V.prod_id(+) 
AND    s.prod_name = V.prod_name(+) 
AND    s.cust_id = 5;


Without Join predicate push down the execution plan is

----------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Cost   |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |     5 |    15 |
|*  1 |  HASH JOIN OUTER       |         |     5 |    15 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T4        |    15 |     2  | 
|*  3 |    INDEX RANGE SCAN       | T4_IND_THOUSAND |       |     1  | 
|   4 |   VIEW         |         |  5000 |    13  | 
|   5 |    NESTED LOOPS        |         |  5000 |    13 | 
|   6 |     TABLE ACCESS FULL       | T10        | 10000 |    12 |
|*  7 |     INDEX UNIQUE SCAN       | T5_IND_U3       |     4 |     0  | 
-----------------------------------------------------------------------------------

With join predicate push down the query is rewritten to be as follows (note this isn’t real SQL):

SELECT s.order_id, V.prod_id
FROM   sales s,
       (SELECT p.prod_id, p.prod_name, p.prod_desc
        FROM   products p, product_details pd
        WHERE  p.prod_category_id = pd.prod_category_id
        AND    s.prod_id = V.prod_id(+)
        AND    s.prod_name = V.prod_name(+))V
WHERE  s.cust_id = 5; 

Allowing the execution plan to become

---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Cost (%CPU)|
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |   |     5 |    12   (0)|
|   1 |  NESTED LOOPS OUTER         |   |     5 |    12   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED  | SALES   |     1 |     2   (0)|
|*  3 |    INDEX RANGE SCAN         | SALES_IND_PROD  |     1 |     1   (0)| 
|   4 |   VIEW PUSHED PREDICATE         |   |     1 |    10   (0)|
|*  5 |    HASH JOIN          |   |    10 |    10   (0)|
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTS  |    10 |     5   (0)|
|   7 |      BITMAP CONVERSION TO ROWIDS       |   |  |       |
|   8 |       BITMAP AND         |   |  |       |
|   9 |        BITMAP CONVERSION FROM ROWIDS   |   |  |       |
|* 10 |  INDEX RANGE SCAN        | PROD_IND_ID     |  |     1   (0)|
|  11 |        BITMAP CONVERSION FROM ROWIDS   |   |  |       |
|* 12 |  INDEX RANGE SCAN        | T10_IND_TEN  |  |     2   (0)| 
|  13 |     INDEX UNIQUE SCAN         | PD_IND_CAT  |  5000 |     5   (0)|
---------------------------------------------------------------------------------------

The join predicate pushdown transformation is not always optimal. With join predicate pushed-down the view becomes correlated and it must be evaluated for each row in the outer query; if there is a large number of outer rows, the cost of evaluating the view multiple times may make the nested-loop join suboptimal that is why the optimizer will cost the plan with and without the transformation and pick the plan with the lowest cost.

Based on your explanation it sounds like the optimizer is in correctly selecting to use the transformation. This is typically caused by a cardinality misestimate, where the optimizer estimates that the number of outer rows is less than it really is. This can happen for number of reason, everything for stale or missing statistics to correlation between the column used in the query. Correcting the cardinality misestimate typically enables the optimizer to make the right decision and not use the transformation.

Setting the underscore parameter disables the JPPD transformation altogether and is effectively fixing the problem at the wrong scope. After all join predicate push down is not a problem for every query on the system but you are disabling it for every query on the system. If you can’t fix the problem by correcting the cardinality mis-estimate then I would suggest you log a bug and correct the problem at the scope of the problem, in other words at the statement level.

You can disable the join predicate push transformation via the NO_PUSH_PRED hint, which you can add to the statement directly or via a SQL plan baseline, profile or SQL patch. You can find more information on how to do that in this blog post https://blogs.oracle.com/optimizer/using-sql-patch-to-add-hints-to-a-packaged-application