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.
Monthly Archives: February 2013
Why between to_date(’1582-10-15′,’yyyy-mm-dd’) and to_date(’1582-10-04′,’yyyy-mm-dd’) only one day
You may wonder why between these dates only one day:
SQL> select date'1582-10-15'-date'1582-10-04' from dual;
DATE'1582-10-15'-DATE'1582-10-04'
---------------------------------
1
SQL> select date'1582-10-05' "dt_1"
2 ,date'1582-10-05' + 1 "dt_1 + 1"
3 ,date'1582-10-05' - 1 "dt_1 - 1"
4 ,to_date('1582-10-05','yyyy-mm-dd') "dt_1 and to_date"
5 from dual;
dt_1 dt_1 + 1 dt_1 - 1 dt_1 and to_date
------------------ ------------------ ------------------ ------------------
October 05, 1582 October 16, 1582 October 04, 1582 October 15, 1582
Over the last 2 months, I gave link to answer several times, so i decided to post it here: http://www.orafaq.com/papers/dates_o.doc
BTW, yet another trick:
SQL> select date'0000-02-29','to_char:'||date'0000-02-29' from dual;
DATE'0000-02-29' 'TO_CHAR:'||DATE'0000-02-29'
------------------- ---------------------------
29.02.0000 00:00:00 to_char:00.00.0000 00:00:00
SQL> select to_date('0000-02-29','yyyy-mm-dd') error from dual;
select to_date('0000-02-29','yyyy-mm-dd') error from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
About unnecessary work with predicate “field=:bind_variable” where bind_variable is null
Although we know that in the situation 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 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 examples, will not be any reads only if it is access predicate on index range/unique scan(it is quite obvious because of ) or full table scan on 11.2.0.3 and with gathered stats. If it is FTS on previous versions will be read only segment header. 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
Deterministic function vs scalar subquery caching. Part 2
In previous part i already point out that:
- Both mechanisms are based on hash functions.
- Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
- Hash collisions depends on the single parameter “_query_execution_cache_max_size” for both mechanizms, but they are more frequent in SSC.
Today’s topic:
4. Deterministic functions does not keeps last result as scalar subquery caching
5. Caching of deterministic functions results turns off after a certain number of attempts to get the value from the cache. But SSC always returns results from cache if values already cached.
Continue reading
Deterministic function vs scalar subquery caching. Part 1
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.
Today’s topics:
1. Both mechanisms are based on hash functions.(About hash tables and hash collisions for scalar subquery caching excelent wrote Tom Kyte and Jonathan Lewis(“Cost-Based Oracle fundamentals” chapter 9))
2. Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
3. Hash collisions depends on the single parameter “_query_execution_cache_max_size” for both mechanizms, but they are more frequent in SSC.
UPD: Part 2
Continue reading
Materialization in subquery factoring without hint “materialize” can be considered only when exists at least one predicate
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:
Update: I did some additional tests and found:
- with “table()” but without “xmltable” materialization occurs always regardless of existence of predicates or another tables in subquery factoring clause
- with “xmltable” behavior is very strange – decision about materialization depends from ‘columns …’ clause: when it exists – materialization occurs, if not – not occurs.
- with “selects a subset of table columns” as David Aldridge said – decision still depends from predicates existence