Forum Stats

  • 3,824,849 Users
  • 2,260,430 Discussions
  • 7,896,330 Comments

Discussions

Deal with smart logic: Implement the reopt(ALWAYS) Hint like DB2

Lothar Flatz
Lothar Flatz Member Posts: 687 Silver Badge
edited Jan 11, 2016 6:20PM in Database Ideas - Ideas

We currently come across more and more statements like this one (source The Hitchhiker’s Guide to the EXPLAIN PLAN Part 36: The sisters of the mother of all SQL antipatterns - Oracle - Oracle …).

The author here Conditional WHERE clauses in SQL - Avoid Smart Logic describes it as " is one of the worst performance anti-patterns of all."

SELECT COUNT(*)

FROM

  (SELECT 1

  FROM employees

  WHERE (job_id = NVL(:job_id, job_id))

  AND (department_id = NVL(:department_id, department_id))

  AND (manager_id = NVL(:manager_id, manager_id))

  AND (employee_id = NVL(:employee_id, employee_id))

  );

For the developer it is easier to write statements like that, for the optimizer it is nightmare.

There are two ways to deal with that:

  1. change the statement , which might be impossible for the DBA on the receiving end
  2. Use the REOPT hint with the option ALWAYS. That would mean that for every call the statement will be reoptimized according to the current bind variables. That idea is shown here: Conditional WHERE clauses in SQL - Avoid Smart Logic

However the REOPT hint or similar does not exist in Oracle. We need it. If you in double please read the websites referenced here.

Lothar FlatzberxIggy Fernandezctriebuser7058190Zlatko SiroticManish ChaturvediFranck PachotPravin TakpiremarkmevansMartin Preiss1594234vinaykumar2borneselArpit Jain -OracleJitendraIvica Arsova_nullbhagatsinghRandolf GeistulohmannsensoftSven W.AndrewSayerDom BrooksThomas AreggerRainer Stenzel
31 votes

Active · Last Updated

«1

Comments

  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    I have dreamed about a 'NO_SHARING' hint for years... I vote up

    Lothar Flatz
  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    I have dreamed about a 'NO_SHARING' hint for years... I vote up

    NO_SHARING would be a better, more Oracle like name.

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown
    .. for the optimizer it is nightmare ...
    

    In fact the NVL function is one of the best ways to write the SQL:

    https://blogs.oracle.com/sql/entry/avoid_or_bind_variable_predicates

    There are other options, and these are also in the link.

    Franck PachotLothar Flatz
  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge
    .. for the optimizer it is nightmare ...
    

    In fact the NVL function is one of the best ways to write the SQL:

    https://blogs.oracle.com/sql/entry/avoid_or_bind_variable_predicates

    There are other options, and these are also in the link.

    I am just writing a search procedure. Using dynamic sql as the article suggests. Actually also with the nvl the plans are a bit hard to read.

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown
    However the REOPT hint or similar does not exist in Oracle.
    

    Oracle already has a solution at the system level:

    alter systen set CURSOR_SHARING=FORCE;

    Are you saying this parameter is not effective, and you would like to hint the SQL statement instead?

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge
    However the REOPT hint or similar does not exist in Oracle.
    

    Oracle already has a solution at the system level:

    alter systen set CURSOR_SHARING=FORCE;

    Are you saying this parameter is not effective, and you would like to hint the SQL statement instead?

    Excuse me that is exactly the opposite of the hint suggested here. We HAVE bind variables and we WANT literals. We have bind variables because an application that we can not change sends them. If we had literals the optimizer would be up to the job.

    Dom Brooks
  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    Excuse me that is exactly the opposite of the hint suggested here. We HAVE bind variables and we WANT literals. We have bind variables because an application that we can not change sends them. If we had literals the optimizer would be up to the job.

    Well excuse me, you are correct.

    However you know you can't have a hint because you can;t change the app - so you need to propose a new system parameter..... just not CURSOR_SHARING

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown
    edited Sep 23, 2019 5:56AM

    I'm not sure if the proposal would help with the described problem. Also adaptive cursor sharing takes changing bind values into account already. So it seems as if the feature requested partially is already there. I think the NVL function (or coalesce) is the better way to solve it, and not a general hint that would overrule all bindings.

    A where condition like this one: AND (department_id = NVL(:department_id, department_id))

    If the bind variable is NULL then a full table scan, if the bind variable has a value, then a index scan is prefered.

    BUT: the NVL function prevents index usage on the department_id column.Or more specifically the department_id column inside the nvl function prevents that.

    The strong argument here is that the optimizer is able to find the best plan if LITERALS are used instead of bind parameters.

    Question now is why is there such a difference?

    I think what would be extremly valueable is an query rewrite mechanism,

    that is able to eliminate such a condition from the where clause, in case a NULL value is binded.

    So special handling/optimization for binded NULL values.

    In the end Lothars original select should be rewritten into this, if only the :JOB_ID is not null.

    SELECT COUNT(*)

    FROM

      (SELECT 1

      FROM employees

      WHERE (job_id = :job_id)

      AND (1=1)

      AND (1=1)

      AND (1=1)

      );

    Rainer Stenzel
  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown

    Couple this with something like SQL Server’s OPTIMIZE FOR and youve suddenly got full control over bind peeking problems.

    Easy upvote!

    Dom Brooks
  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    The recognised way of doing this kind of query in Oracle is to use dynamic SQL, so the query only contains those predicates needed.