0

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:

  1. 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

  2. For no reason using MERGE JOIN CARTESIAN or NESTED LOOP without indexes instead of HASH JOIN. USE_HASH or CARDINALITY hints solve this problem

  3. 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.

Improve this question
1
  • 3
    What you are experiencing is what we all experience. The worst execution plans are the result of an expectation of 0 rows (explain plan reports 1) from a table or a join when that is not the case. The key to handling this is first statistics gathered at the right time, then hints. There's no magic bullet for it database-wide. It's what happens when query complexity and bad data models that require query complexity meet the limitations of a non-intelligent optimizer that is trying to be intelligent with very limited resources (stats). We often end up with hints to stabilize such queries.
    – Paul W
    Commented Jun 10, 2023 at 22:00

1 Answer 1

Reset to default
1

Either check whether compound stats on multiple columns would help you estimate cardinalities better.

If not check this Pythian link

The hint OPT_ESTIMATE can help you adjust optimizer's estimates by percentage or you can floor estimate to some max. value. IMHO this is the best way how to tune optimizer plan, without actually enforcing anything particular.

Like:

select /*+ OPT_ESTIMATE(JOIN (C S) MAX=100000) */

PS: There is also a possibility that Oracle simply must use such a plan for some reasons. Like for example you're joining two columns having different data types and index can not be used.

Improve this answer
2
  • 1
    Compound stats (or DYNAMIC_SAMPLING) are really helpful sometimes, but not always. For example, when I have exists/not exists stats are (to some degree) useless. Thanks for OPT_ESTIMATE, looks like advanced version of CARDINALITY, I will try it. But it solves problem locally, for specific queries. I want global solution (if it exists at all). There are really cases when such plans must be used, but it's not a case as simple hints with no changes to code can solve it.
    – Andy DB Analyst
    Commented Jun 8, 2023 at 9:52
  • 1
    Yeah OPT_ESTIMATE is better version of CARDINALITY hint. I do not understand why it is not documented. If there is way to force exec. plan and you can not use hint/baseline/profile/sqlpatch then imho the only way is to fake and lock table/index stats.
    – ibre5041
    Commented Jun 8, 2023 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.