Sometimes it’s really hard even to create reproducible test case to send it to oracle support, especially in case of intermittent errors.
In such cases, I think it would be really great to have access to similar service requests or bugs of other oracle clients.
So while my poll about knowledge sharing is still active, I want to share a couple of bugs we have faced after upgrade to 12.2 (and one bug from Eric van Roon). I’m going to remove the bugs from this list when they become “public” or “fixed”.
If you want to add own findings into this list, you can add them into comments. To make this process easier, you can provide just symptomps, short description and the link to own post with details – I’ll add it just as a link.
Continue reading
Tag Archives: deterministic functions
Deterministic functions, result_cache and operators
In previous posts about caching mechanism of determinstic functions I wrote that cached results are kept only between fetch calls, but there is one exception from this rule: if all function parameters are literals, cached result will not be flushed every fetch call.
Little example with difference:
SQL> create or replace function f_deterministic(p varchar2)
2 return varchar2
3 deterministic
4 as
5 begin
6 dbms_output.put_line(p);
7 return p;
8 end;
9 /
SQL> set arrays 2 feed on;
SQL> set serverout on;
SQL> select
2 f_deterministic(x) a
3 ,f_deterministic('literal') b
4 from (select 'not literal' x
5 from dual
6 connect by level<=10
7 );
A B
------------------------------ ------------------------------
not literal literal
not literal literal
not literal literal
not literal literal
not literal literal
not literal literal
not literal literal
not literal literal
not literal literal
not literal literal
10 rows selected.
not literal
literal
not literal
not literal
not literal
not literal
not literal
As you can see, ‘literal’ was printed once, but ‘not literal’ was printed 6 times, so it was returned from cache 4 times.
Also i want to show the differences in consistency between:
1. Calling a function with determinstic and result_cache;
2. Calling an operator for function with result_cache;
3. Calling an operator for function with deterministic and result_cache;
In this example I will do updates in autonomouse transactions to emulate updates in another session during query execution:
Tables and procedures with updates
[sourcecode language=”sql”]
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
create table t1 as select 1 id from dual;
create table t2 as select 1 id from dual;
create table t3 as select 1 id from dual;
create or replace procedure p1_update as
pragma autonomous_transaction;
begin
update t1 set id=id+1;
commit;
end;
/
create or replace procedure p2_update as
pragma autonomous_transaction;
begin
update t2 set id=id+1;
commit;
end;
/
create or replace procedure p3_update as
pragma autonomous_transaction;
begin
update t3 set id=id+1;
commit;
end;
/
[/sourcecode]
[collapse]
Variant 1
[sourcecode language=”sql”]
create or replace function f1(x varchar2) return number result_cache deterministic
as
r number;
begin
select id into r from t1;
p1_update;
return r;
end;
/
[/sourcecode]
[collapse]
Variant 2
[sourcecode language=”sql”]
create or replace function f2(x varchar2) return number result_cache
as
r number;
begin
select id into r from t2;
p2_update;
return r;
end;
/
create or replace operator o2
binding(varchar2)
return number
using f2
/
[/sourcecode]
[collapse]
Variant 3
[sourcecode language=”sql”]
create or replace function f3(x varchar2) return number result_cache deterministic
as
r number;
begin
select id into r from t3;
p3_update;
return r;
end;
/
create or replace operator o3
binding(varchar2)
return number
using f3
/
[/sourcecode]
[collapse]
Test:
SQL> set arrays 2;
SQL> select
2 f1(dummy) variant1
3 ,o2(dummy) variant2
4 ,o3(dummy) variant3
5 from dual
6 connect by level<=10;
VARIANT1 VARIANT2 VARIANT3
---------- ---------- ----------
1 1 1
2 1 1
2 1 1
3 1 1
3 1 1
4 1 1
4 1 1
5 1 1
5 1 1
6 1 1
10 rows selected.
SQL> /
VARIANT1 VARIANT2 VARIANT3
---------- ---------- ----------
7 11 11
8 11 11
8 11 11
9 11 11
9 11 11
10 11 11
10 11 11
11 11 11
11 11 11
12 11 11
10 rows selected.
We can see that function F1 returns same results every 2 execution – it is equal to fetch size(“set arraysize 2”),
operator O2 and O3 return same results for all rows in first query execution, but in the second query executions we can see that they are incremented by 10 – it’s equal to number of rows.
What we can learn from that:
1. The use of the function F1 with result_cache and deterministic reduces function executions, but all function results are inconsistent with query;
2. Operator O2 returns consistent results, but function is always executed because we invalidating result_cache every execution;
3. Operator O3 works as well as operator O2, without considering that function is deterministic.
All tests scripts: tests.zip
Oracle 12c: Inconsistency of Inline “with” functions
I was hoping that if inline “with” functions are in the query, so their results will be consistent with it (as operators), but unfortunately such functions returns also inconsistent results as standalone pl/sql functions.
SQL> create table t as select 1 a from dual;
Table created.
SQL> declare
2 j binary_integer;
3 begin
4 dbms_job.submit( j
5 ,'begin
6 for i in 1..10 loop
7 dbms_lock.sleep(1);
8 update t set a=a+1;
9 commit;
10 end loop;
11 end;'
12 );
13 commit;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL> with
2 function f return int is
3 res int;
4 begin
5 dbms_lock.sleep(1);
6 select a into res from t;
7 return res;
8 end;
9 select
10 f
11 from dual
12 connect by level<=10;
13 /
F
----------
1
1
1
2
3
4
5
6
7
8
10 rows selected.
Interesting: Jonathan Lewis wrote that inline “deterministic” functions doesn’t use caching mechanism as standalone deterministic functions.
Deterministic function vs scalar subquery caching. Part 3
In previous parts 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.
- Oracle doesn’t keep last result of deterministic functions as it does for scalar subquery caching
- 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.
Upd 2015-02-19:
A couple additions about deterministic functions:
Today’s post is just addition to previous topics:
I wrote about turning caching off after many unsuccessfull attempts to get value from cache, but i didn’t say what it is the number. In fact caching of deterministic functions also depends on two another hidden parameters:
SQL> @param_ plsql%cach NAME VALUE DEFLT TYPE DESCRIPTION ------------------------------------ ------------ -------- ---------- ------------------------------------------------------------------ _plsql_cache_enable TRUE TRUE boolean PL/SQL Function Cache Enabled _plsql_minimum_cache_hit_percent 20 TRUE number plsql minimum cache hit percentage required to keep caching active
First parameter “_plsql_cache_enable” is just a parameter which enables/disables this caching mechanism.
But the second parameter – “_plsql_minimum_cache_hit_percent” – is responsible for the percentage of unsuccessful attempts which disables caching.
I will show their effects with the example from the previous post:
-- set this parameter to big value for maximizing caching:
alter session set "_query_execution_cache_max_size" = 131072;
-- log table clearing:
truncate table t_params;
-- test with percentage = 50
alter session set "_plsql_minimum_cache_hit_percent"=50;
select sum(f_deterministic(n)) fd
from
xmltable('1 to 10000,1 to 10000'
columns n int path '.'
);
select 10000-count(count(*)) "Count of cached results"
from t_params
group by p
having count(*)>1;
/*
Count of cached results
-----------------------
0
*/
-- now i change cache hit percentage parameter to 0:
alter session set "_plsql_minimum_cache_hit_percent"=0;
truncate table t_params;
select sum(f_deterministic(n)) fd
from
xmltable('1 to 10000,1 to 10000'
columns n int path '.'
);
select 10000-count(count(*)) "Count of cached results"
from t_params
group by p
having count(*)>1;
/*
Count of cached results
-----------------------
2039
*/
How works optimization of loops in PL/SQL in 11g: Part 1. Deterministic functions
As you know, since 10g Oracle can optimize PL/SQL loops with moving code out of loops for reducing number of identical code executions.
If you have not read yet about it, I advise you to first read these two articles:
1. “PLSQL_OPTIMIZE_LEVEL: The optimization strategy of Oracle” by Saurabh K. Gupta
2. “PL/SQL optimisation in 10g” by Adrian Billington
But since 11g Oracle also can optimize code with deterministic functions too. For this to happen, code must meet the following conditions:
1. PLSQL_OPTIMIZE_LEVEL greater or equal 2
2. Parameters should not be changed in the loop body. Strictly speaking, there should not be any assignment of parameters.
3. Should not be any implicit conversions of parameters in function call, i.e. all variables/literals must be the same type as input parameters declared.
4. Should not be any call of non-deterministic functions (except some standard sql functions like to_date, to_char, nvl) or procedures in the loop
Note that this rules concerns only same scope level as this loop and not inner loops or another blocks.
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.(You can read great articles about hash tables and hash collisions for scalar subquery caching by 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