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.
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 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 examples, will not be any reads only if it is an 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
Update:The explanation about segment header reads you can listen from Enkitec.tv by Tanel Poder
Full test code you can download as file – test.sql.
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
Bug in documentation about dbms_stats.gather_schema_stats or in the dbms_stats itself
Recently I had to gather pending stats with Object Filter List by several objects, so I copied example from documentation and was surprised: instead of gather stats by specified filter list, oracle started to gather stats for all tables in current_schema! And ‘filter list’ applies only with dbms_stats.GATHER_DATABASE_STATS
UPD: Jared Still gave link to registered bug id, which i couldn’t find in MOS before:
Bug 12754926 – DBMS_STATS.gather_schema_stats does not work with an obj_filter_list parameter specified [ID 12754926.8]
Modified:04-Jan-2012 Type:PATCH Status:PUBLISHED
This issue is fixed in 11.2.0.4 (Future Patch Set)
PS. Also there is a typo in the example from oracle documentation: ‘gather_stale’ instead of ‘gather stale’ – underline instead of blank space.
Just another version of Tom Kyte’s runstats (runstats_pkg)
I want to share my modifications of Tom Kyte’s runstats package, which include:
- Any number of runs sets for analyzing
- Standalone: No need to create other objects
- Ability to specify session SID for statistics gathering
- Ability to specify what to gather: latches, stats or both
- Separate mask filters for output by statname and latchname
- Ability to specify difference percentage for output separately for latches and statistics
- More accurate with some statistics because of avoiding global temporary table usage, but less in several others because of collections usage
Link to package: https://github.com/xtender/xt_runstats
Output example:
SQL> begin 2 -- little example which shows difference between "fast dual" and "full table scan dual": 3 -- http://docs.oracle.com/cd/E11882_01/server.112/e17118/queries009.htm#SQLRF20036 4 xt_runstats.init(p_latches => false); 5 -- 1: 6 for r in (select * from dual connect by level<=1e3) loop 7 null; 8 end loop; 9 xt_runstats.snap; 10 11 -- 2: 12 for r in (select 'X' dummy from dual connect by level<=1e3) loop 13 null; 14 end loop; 15 xt_runstats.snap; 16 xt_runstats.print(p_stats_mask => '%gets%'); 17 end; 18 / ################ Results: ################## Run # 01 ran in 0 hsecs Run # 02 ran in 0 hsecs ########################################################################### Statistics | Run # 1 | Run # 2 ########################################################################### consistent gets......................... | 3 | 0 consistent gets from cache.............. | 3 | 0 consistent gets from cache (fastpath)... | 3 | 0 no work - consistent read gets.......... | 1 | 0 ########################################################################### -
Some usage examples:
1. for own session:
begin
xt_runstats.init();
[some_code_1]
xt_runstats.snap();
[some_code_2]
xt_runstats.snap();
...
[some_code_N]
xt_runstats.snap();
-- result output:
xt_runstats.print();
end;
2. for session with sid = N
begin xt_runstats.init(N); end;
...[after a while]
begin xt_runstats.snap; end;
...[one more if needed...]
begin xt_runstats.snap; end;
-- result output:
begin xt_runstats.print(); end;
3. Latches only:
xt_runstats.init(p_stats=>false);
4. Print stats with name like ‘%gets%’:
xt_runstats.print(p_stats_mask=>'%gets%');
5. Print latches which differ by 30% or more and stats differ by 15% or more:
xt_runstats.print( p_lat_diff_pct=>30, p_sta_diff_pct => 15);
Differences between integer(int) in SQL and PL/SQL
Very simple example:
create table t_integer(i integer); insert into t_integer values(1e125); select * from t_integer; declare i1 integer; begin select i into i1 from t_integer; end; / declare i2 t_integer.i%type; begin select i into i2 from t_integer; end; /
Although variable and the column specified as “integer”, but in both blocks you will get error: ORA-06502: PL/SQL: numeric or value error: number precision too large.
Same error will be with “int“.
Describe shows wrong datatype:
> desc t_integer; Name Null? Type --------------------------- -------- ---------- I NUMBER(38)
Really it would be number without precision and scale=0. You can see it in dba_tab_columns.
sys.standard:
subtype INTEGER is NUMBER(38,0); subtype INT is INTEGER;
Also a couple simple good-known but often forgotten things:
1. integer as parameter type or return type:
SQL> create function f_integer(i integer)
2 return integer is
3 begin
4 return i;
5 end;
6 /
Function created.
SQL> select f_integer(1/3) from dual;
F_INTEGER(1/3)
--------------
.333333333
1 row selected.
2. Old finding from sql.ru – no casting in assignments (from 10.2 till 11.2, was found by Elic):
declare
numberVar number := 1/3;
numberVar2 number(20,10) := 1/3;
procedure Test(Label varchar2, Value int)
is
LocalVar int := Value;
begin
dbms_output.put_line(Label || ' : ' || Value);
dbms_output.put_line(Label || ' assigned: ' || LocalVar);
end Test;
begin
Test('| const', 1/3);
Test('| number var', numberVar);
Test('|constrained number var', numberVar2);
end;
/
| const : .3333333333333333333333333333333333333333
| const assigned: .3333333333333333333333333333333333333333
| number var : .3333333333333333333333333333333333333333
| number var assigned: .3333333333333333333333333333333333333333
|constrained number var : .3333333333
|constrained number var assigned: .3333333333
PL/SQL procedure successfully completed.
The change in “dump sort statistics” trace (event 10032)
Earlier “Event 10032 – Dump Sort Statistics” showed information about sorts only(11.2.0.1 incl.), but in 11.2.0.3 it also showing “Abridged” call stack. I have not tested it on 11.2.0.2, so I can’t tell from which version it works.
alter session set events '10032 trace name context forever, level 10';
From trace file:
*** 2012-10-30 23:14:34.627
*** SESSION ID:(41.15067) 2012-10-30 23:14:34.627
*** CLIENT ID:() 2012-10-30 23:14:34.627
*** SERVICE NAME:(orasql.org) 2012-10-30 23:14:34.627
*** MODULE NAME:(SQL*Plus) 2012-10-30 23:14:34.627
*** ACTION NAME:() 2012-10-30 23:14:34.627
soropn: opened (new) sort, sordef 0x7fc4679e2550, flags 0x802
maxkey 25, nflds 12, nkflds 1
*** 2012-10-30 23:14:34.678
----- Current SQL Statement for this session (sql_id=3ktacv9r56b51) -----
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
Abridged call stack trace:
ksedsts<-soropn<-qersoProcessULS<-qersoFetch<-opifch2<-opifch<-opiodr<-rpidrus<-skgmstack<-rpiswu2<-rpidrv<-rpifch<-kqllod<-kglobld<-kglobpn<-kglpim<-kglpin<-kglgob<-kgldpo0<-qcdlgpo<-qcsRslvPLSQLInvoc1<-qcsRslvPLSQLInvoc<-qcsRslvName<-qcsridn<-qcsraic<-qcspqbDescendents
<-qcspqb<-kkmdrv<-opiSem<-opiDeferredSem<-opitca<-kksFullTypeCheck<-rpiswu2<-kksLoadChild<-kxsGetRuntimeLock<-kksfbc<-kkspsc0<-kksParseCursor<-opiosq0<-kpooprx<-kpoal8<-opiodr<-ttcpip<-opitsk<-opiino<-opiodr<-opidrv<-sou2o<-opimai_real<-ssthrdmain<-main<-__libc_start_main
<-_start End of abridged call stack trace.
*** 2012-10-30 23:14:35.328
soreod: sorp 0x7fc4679e2550
---- Sort Parameters ------------------------------
sort_area_size 65536
sort_area_retained_size 65536
sort_multiblock_read_count 1
max intermediate merge width 3