I’m sorry, that example was wrong! Thanks to Jefrey Kemp!
But previous is still relevant.
Category Archives: undocumented
Oracle 12c: behavior tests of the Inline functions, “Identities” and “defaults”
I have done several minitests:
1. SQL and PL/SQL engines: which functions will be executed if there are two functions with same name as in SQL, as in PL/SQL (like “USER”, LPAD/RPAD, etc..)
- PL/SQL.
2. Will there be any context switches if we call the inline functions which contain another pl/sql functions/procedures?
- Yes
3. How IDENTITY works?
For all identity columns Oracle creates a sequence with name like “ISEQ$$_XXX”, where “XXX” is the object_id of the table. All identities we can get through DBA_TAB_IDENTITY_COLS.
All Identity sequences:
select i.*
,tab.owner tab_owner
,tab.object_name tab_name
,sq.object_name sequence_name
from sys.idnseq$ i
,dba_objects tab
,dba_objects sq
where tab.object_id=i.obj#
and sq.object_id = i.seqobj#
And we can see usage of this sequence in plans:
4. When executes “default seq.nextval”?
Too many function executions in simple query
Often i see questions like “why function is executed too many times in query”.
Lets see simple example:
We have table with 10 rows:
SQL> select id from t10;
ID
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
And we have the query:
select *
from (
select xf(t10.id) a
from t10
)
where a*a >= 25
At first it may seem that the function should be executed as many times as rows in a table T10, i.e. 10 times.
Lets test it:
SQL> create or replace function xf(p int) return int as
2 begin
3 dbms_output.put_line('F fired!');
4 return p;
5 end;
6 /
Function created.
SQL> set serverout on;
SQL> select *
2 from (
3 select xf(t10.id) a
4 from t10
5 )
6 where a*a >= 25
7 /
A
----------
5
6
7
8
9
10
6 rows selected.
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired!
F fired!
F fired!
F fired!
F fired! -- 15
F fired!
F fired!
F fired!
F fired!
F fired! -- 20
F fired!
F fired!
F fired!
F fired!
F fired! -- 25
F fired!
As you see, there are more than 10 executions, so lets see the execution plan:
SQL> @xplan +projection
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2919944937
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T10 | 1 | 3 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T10"."ID"[NUMBER,22]
Now you see that inner view was merged, and the function was executed 20 times in the filter and 5 times on the fetch after filtering(plus 1 extra execution because of it was in SQL*plus: at first it fetches one row and after it fetches by “arraysize” rows).
I see that often in such cases “no_merge” hint is suggested, but let’s test it:
SQL> select *
2 from (
3 select/*+ no_merge */ xf(t10.id) a
4 from t10
5 )
6 where a*a >= 25
7 /
A
----------
5
6
7
8
9
10
6 rows selected.
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
F fired!
As you can see, the number of function calls wasn’t changed.
And if we look into the plan, we understood why:
SQL> @xplan +projection
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2027387203
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T10 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("XF"("T10"."ID")*"XF"("T10"."ID")>=25)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"[NUMBER,22]
2 - "T10"."ID"[NUMBER,22]
20 rows selected.
Now you see, that main problem is the “filter pushdown” transformation. Previously, if we were to disable the “filter pushdown” operation, we had to use a variety of tricks, such as “materialize” hint or adding the “rownum” in expession, etc. But all these solutions require rewriting the query.
But from 11.2.0.3 we can use “_optimizer_filter_pushdown” parameter, for example:
SQL> begin
2 dbms_sqltune.import_sql_profile(
3 sql_text => 'select * from (select xf(t10.id) a from t10) where a*a >= 25'
4 ,profile => sys.sqlprof_attr(
5 q'[NO_MERGE(@SEL$2)]'
6 ,q'[OPT_PARAM('_OPTIMIZER_FILTER_PUSHDOWN' 'FALSE')]'
7 )
8 ,category => 'DEFAULT'
9 ,name => 'TEST_PROFILE'
10 ,force_match => true
11 ,replace => true
12 );
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.16
SQL> set serverout on
SQL> select * from (select xf(t10.id) a from t10) where a*a >= 25;
A
----------
5
6
7
8
9
10
6 rows selected.
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
F fired! -- extra execution because of sql*plus
F fired! -- extra execution because of sql*plus
Elapsed: 00:00:00.17
-- there are no extra calls when we fetches by 100 rows in pl/sql:
SQL> exec for r in (select * from (select xf(t10.id) a from t10) where a*a >= 25) loop null; end loop;
F fired!
F fired!
F fired!
F fired!
F fired! -- 5
F fired!
F fired!
F fired!
F fired!
F fired! -- 10
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.22
PS. I found that about “_optimizer_filter_pushdown” parameter already wrote Randolf Geist
Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
Previously i showed not obvious example with hint “INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X, rows=Y)“. Strictly speaking i don’t know how exactly cbo calculates number of index leaf blocks in that case: in those examples they was 1981 for “blocks=1, rows=50″ and 49525 for “blocks=5, rows=10″.
But i know that with “INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X)” i can set exact blocks number.
Also those test-cases didn’t show when occurs decision changing. So todays my test will show it.
BTW, it is very interesting that a turning point was _very_large_object_threshold, but not 5 * _small_table_threshold. AFAIK, direct path reads decision depends on many factors (such as number of object blocks in buffer cache), and would be great to know all of them.
You can download script as file: controlling_direct_reads_with_profiles.sql
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.
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.