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

Lothar Flatz

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.

Franck Pachot

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

Lothar Flatz

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

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

Lothar Flatz

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

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

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.

top.gun

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.

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)

  );

AndrewSayer

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

Easy upvote!

FatMartinR

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