I'm not talking about stale statistics, or just simply "bad"/"non-optimal" plans.
We have a lot of complicated queries running in our database. Normally all works as expected. But from time to time we have cases when optimizer miscalculates cardinalities and chooses ridiculuous execution plans. The worst cases are when optimizer evaluates subquery to have 1 row. Then we have plans with:
Wrong join order with MERGE JOIN CARTESIAN and thousands of rows tables/subqueries. Optimizer for some reasons chooses plans like "SELECT * FROM TAB1... (CROSS) JOIN TAB3... JOIN TAB2 ON TAB1.COL11 = TAB3.COL31 AND TAB2.COL21 = TAB3.COL32" instead of joining "TAB1 JOIN TAB2 JOIN TAB3" as expected. With LEADING/ORDERED hints it starts working properly
For no reason using MERGE JOIN CARTESIAN or NESTED LOOP without indexes instead of HASH JOIN. USE_HASH or CARDINALITY hints solve this problem
Using VIEW PUSHED PREDICATE with FULL TABLE SCAN. This leads to scanning small tables thousands of times. It takes additional minutes/hours for queries to execute. CARDINALITY/MATERIALIZE/NO_PUSH_PRED hints solve this problem
The question is:
Is there a way to globally force the optimizer not to use CARTESIAN/VIEW PUSHED PREDICATE if there is no guarantee of 1 row result? Or at least decrease a probability of using it? Like when we had problems of indexes overusing while doing analysis, "ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 200" partially solved it.