Monthly Archives: February 2013

There is well-known bug with “for update order by” on 11.2, when rows locks not in specified order, although the ordering occurs.
I already wrote on my russian blog about the appearance of “buffer sort” in plans with “for update” even if sort order was not specified. And this behavior can be disabled for example by specifying /*+ opt_param( ‘optimizer_features_enable’ ‘11.1.0.7’ ) */.
But if we want to solve problem with deadlock, we need to force index full scan/index range scan ascending with “buffer sort” usage.

UPD: The patch is already available for 11.2.0.2 and 11.2.0.3: Patch 13371104: LOCK ORDER HAS CHANGED BETWEEN 10.2.0.5 AND 11.2.0.2.Read more: Simple example

Although we know that in the case when we do select from a table and one of the predicates is “field=:bind_variable” and :bind_variable is null, we should not get the rows, oracle not always “think” the same and don’t add upper filter predicate “:bind_variable is not null”, so it can do unnecessary work. It is completely depends from a plan, which will be chosen. Of course this applies to other predicates like >, < or != too.
For example, there will not be any reads only if it is an access predicate on index range/unique scan or full table scan on 11.2.0.3 and with gathered stats. If it is FTS on previous versions, then only segment header is read. In others cases oracle will do useless scans.
So if bind variable can be null and you want to be sure that oracle will not do futile work in such cases, just add predicate ":bind_variable is not null".
This note is just aggregated info from recent question from our forum where i participated

I recently did a comparison caching mechanisms of scalar subquery caching(SSC) and deterministic functions in 11.2. Unfortunately, I do not have enough time to do a full analysis, so I will post it in parts.

I found just now that materialization can not be considered by optimizer if there are no predicates in subquery factoring clause. Of course, i mean cases without forcing materialization through hint “materialize”.
Simple example: