In my previous Fancy Code blog entry, I received a comment from Rick saying:Maybe the original author specified DETERMINSTIC so that the function wouldn't have to logically derive the return value if/whenever it was called again with the same parameter value. Since it could just return previously calculated results, then maybe he could save a fraction of a second of CPU time within a year or two if it gets called enough.
And since the answer to that question will be lengthy I decided to make it a separate post. According to the documentation this comment is certainly valid. Here is a snippet from the documentation about the deterministic clause:DETERMINISTIC Clause
Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments.
You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE. When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function. If you subsequently change the semantics of the function, then you must manually rebuild all dependent function-based indexes and materialized views.
Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function. The results of doing so will not be captured if Oracle Database chooses not to reexecute the function.
The following semantic rules govern the use of the DETERMINISTIC clause:
This part of the documentation hasn't changed from 9iR2 to 11gR1, but there certainly was a difference, concerning this sentence:When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function.
This has never been true for a long time, as I will show below. Or should I say the "attempts" have all been unsuccessful? To show this, I used the adjusted piece of fancy code and create two variants of the strip_transaction_rolled_back function. The one with the "2" suffix is the deterministic one. The code was executed on a 9.2.0.8 database. Notice that I ran the test at least three times to be able to safely use the simple "set timing on" to measure elapsed times. The elapsed times were consistent in all runs.
SQL> CREATE FUNCTION strip_transaction_rolled_back
2 ( p_sqlerrm IN VARCHAR2
3 ) RETURN VARCHAR2
4 IS
5 BEGIN
6 RETURN
7 CASE
8 WHEN p_sqlerrm LIKE 'ORA-02091%ORA-%'
9 THEN
10 SUBSTR(p_sqlerrm,INSTR(p_sqlerrm,'ORA-',1,2))
11 ELSE
12 p_sqlerrm
13 END
14 ;
15 END strip_transaction_rolled_back;
16 /
Function created.
SQL> CREATE FUNCTION strip_transaction_rolled_back2
2 ( p_sqlerrm IN VARCHAR2
3 ) RETURN VARCHAR2 DETERMINISTIC
4 IS
5 BEGIN
6 RETURN
7 CASE
8 WHEN p_sqlerrm LIKE 'ORA-02091%ORA-%'
9 THEN
10 SUBSTR(p_sqlerrm,INSTR(p_sqlerrm,'ORA-',1,2))
11 ELSE
12 p_sqlerrm
13 END
14 ;
15 END strip_transaction_rolled_back2;
16 /
Function created.
SQL> set timing on
SQL> declare
2 t varchar2(100);
3 begin
4 for i in 1..1000000
5 loop
6 t := strip_transaction_rolled_back2
7 ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
8 );
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.93
SQL> declare
2 t varchar2(100);
3 begin
4 for i in 1..1000000
5 loop
6 t := strip_transaction_rolled_back
7 ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
8 );
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.81
All runs on 9.2.0.8 gave approximately equal elapsed times.
Now the same on 10.2.0.1 on my laptop:
rwijk@ORA10GR2> set timing on
rwijk@ORA10GR2> declare
2 t varchar2(100);
3 begin
4 for i in 1..1000000
5 loop
6 t := strip_transaction_rolled_back2
7 ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
8 );
9 end loop;
10 end;
11 /
PL/SQL-procedure is geslaagd.
Verstreken: 00:00:02.23
rwijk@ORA10GR2> declare
2 t varchar2(100);
3 begin
4 for i in 1..1000000
5 loop
6 t := strip_transaction_rolled_back
7 ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
8 );
9 end loop;
10 end;
11 /
PL/SQL-procedure is geslaagd.
Verstreken: 00:00:02.23
Still no difference. Let's try on 11.1.0.6 on my laptop:
rwijk@ORA11G> declare
2 t varchar2(100);
3 begin
4 for i in 1..1000000
5 loop
6 t := strip_transaction_rolled_back2
7 ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
8 );
9 end loop;
10 end;
11 /
PL/SQL-procedure is geslaagd.
Verstreken: 00:00:00.14
rwijk@ORA11G> declare
2 t varchar2(100);
3 begin
4 for i in 1..1000000
5 loop
6 t := strip_transaction_rolled_back
7 ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
8 );
9 end loop;
10 end;
11 /
PL/SQL-procedure is geslaagd.
Verstreken: 00:00:02.29
So Oracle11g has implemented the optimization. It now really "attempts to use previously calculated results when possible rather than reexecuting the function".
But in 11g, why not use the PL/SQL function result cache for this?
Let's compare by creating a third function using the PL/SQL function result cache:
rwijk@ORA11G> CREATE FUNCTION strip_transaction_rolled_back3
2 ( p_sqlerrm IN VARCHAR2
3 ) RETURN VARCHAR2 RESULT_CACHE
4 IS
5 BEGIN
6 RETURN
7 CASE
8 WHEN p_sqlerrm LIKE 'ORA-02091%ORA-%'
9 THEN
10 SUBSTR(p_sqlerrm,INSTR(p_sqlerrm,'ORA-',1,2))
11 ELSE
12 p_sqlerrm
13 END
14 ;
15 END strip_transaction_rolled_back3;
16 /
Functie is aangemaakt.
The result was this:
rwijk@ORA11G> declare
2 t varchar2(100);
3 begin
4 for i in 1..1000000
5 loop
6 t := strip_transaction_rolled_back3
7 ( 'ORA-02091: Transaction rolled back ORA-12345: bla bla'
8 );
9 end loop;
10 end;
11 /
PL/SQL-procedure is geslaagd.
Verstreken: 00:00:02.35
Back at the old elapsed times. Was the result cache indeed used? The "Find Count" in v$result_cache_statistics said so, so yes, the results came out of the result cache. But why is returning the results from a deterministic function so much faster than returning the results from the result cache? First obvious reason I could think of, is that the result cache benefits all sessions, where you can only benefit from the deterministic clause when your session executes the function twice or more. Let's compare the two with Tom Kyte's runstats_pkg:
rwijk@ORA11G> exec runstats_pkg.rs_start
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> declare
2 t varchar2(100);
3 begin
4 for i in 1..1000000
5 loop
6 t := strip_transaction_rolled_back2
7 ( 'ORA-02091: Transaction rolled back ORA:12345 bla bla'
8 );
9 end loop;
10 end;
11 /
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> exec runstats_pkg.rs_middle
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> declare
2 t varchar2(100);
3 begin
4 for i in 1..1000000
5 loop
6 t := strip_transaction_rolled_back3
7 ( 'ORA-02091: Transaction rolled back ORA:12345 bla bla'
8 );
9 end loop;
10 end;
11 /
PL/SQL-procedure is geslaagd.
rwijk@ORA11G> exec runstats_pkg.rs_stop(100)
Run1 draaide in 17 hsecs
Run2 draaide in 249 hsecs
Run1 draaide in 6,83% van de tijd
Naam Run1 Run2 Verschil
STAT.DB time 18 244 226
STAT.CPU used when call started 17 245 228
STAT.CPU used by this session 14 245 231
LATCH.Result Cache: Latch 0 2,000,002 2,000,002
Run1 latches totaal versus run2 -- verschil en percentage
Run1 Run2 Verschil Pct
441 2,000,638 2,000,197 .02%
PL/SQL-procedure is geslaagd.
Two million result cache latches. Acquiring data from memory, whether it is from SGA, PGA or UGA won't differ much in speed. Getting an extra latch however can make a difference when being executed this many times. So most likely this is the reason why the deterministic clause is faster here.
In short, the deterministic clause in a top-level subprogram should certainly be specified if a function truly is deterministic, because the optimization suggested by the documentation finally got implemented in 11g. If you are on an older version right now, you will benefit in the future when the system is upgraded.
The deterministic clause used in a subprocedure inside a procedure in a package body, still won't have any effect, as documented. So I still believe it only leads to confusion, and is best avoided. A final test to show that the code as it was written in the "Fancy Code" post won't even benefit when upgraded to 11g:
rwijk@ORA11G> create package pck
2 as
3 procedure p1 (p_sqlerrm IN VARCHAR2);
4 procedure p2 (p_sqlerrm IN VARCHAR2);
5 end pck;
6 /
Package is aangemaakt.
rwijk@ORA11G> create package body pck
2 as
3 procedure p1 (p_sqlerrm IN VARCHAR2)
4 is
5 t varchar2(100)
6 ;
7 function strip_transaction_rolled_back
8 ( p_sqlerrm IN VARCHAR2
9 ) RETURN VARCHAR2
10 IS
11 BEGIN
12 RETURN
13 CASE
14 WHEN p_sqlerrm LIKE 'ORA-02091%ORA-%'
15 THEN
16 SUBSTR(p_sqlerrm,INSTR(p_sqlerrm,'ORA-',1,2))
17 ELSE
18 p_sqlerrm
19 END
20 ;
21 END strip_transaction_rolled_back
22 ;
23 begin
24 t := strip_transaction_rolled_back(p_sqlerrm);
25 end p1
26 ;
27 procedure p2 (p_sqlerrm IN VARCHAR2)
28 is
29 t varchar2(100)
30 ;
31 function strip_transaction_rolled_back2
32 ( p_sqlerrm IN VARCHAR2
33 ) RETURN VARCHAR2 DETERMINISTIC
34 IS
35 BEGIN
36 RETURN
37 CASE
38 WHEN p_sqlerrm LIKE 'ORA-02091%ORA-%'
39 THEN
40 SUBSTR(p_sqlerrm,INSTR(p_sqlerrm,'ORA-',1,2))
41 ELSE
42 p_sqlerrm
43 END
44 ;
45 END strip_transaction_rolled_back2
46 ;
47 begin
48 t := strip_transaction_rolled_back2(p_sqlerrm);
49 end p2
50 ;
51 end pck;
52 /
Package-body is aangemaakt.
rwijk@ORA11G> set timing on
rwijk@ORA11G> declare
2 t varchar2(100);
3 begin
4 -- the deterministic variant
5 for i in 1..1000000
6 loop
7 pck.p2('ORA-02091: Transaction rolled back ORA-12345: bla bla');
8 end loop;
9 end;
10 /
PL/SQL-procedure is geslaagd.
Verstreken: 00:00:02.79
rwijk@ORA11G> declare
2 t varchar2(100);
3 begin
4 -- the non-deterministic variant
5 for i in 1..1000000
6 loop
7 pck.p1('ORA-02091: Transaction rolled back ORA-12345: bla bla');
8 end loop;
9 end;
10 /
PL/SQL-procedure is geslaagd.
Verstreken: 00:00:02.79
DETERMINISTIC functions *can* indeed allow Oracle database to use some kind of "function result cache" prior to Oracle11, but only under certain circumstances. In particular, it is implemented only in 10gRel2 and only when function is used inside the "implicit" SQL cursor (i.e. it can't benefit result cache when doing row-by-row processing via OPEN - LOOP - FETCH - CLOSE). Quick example using infamous SCOTT.EMP table:
ReplyDeletecreate or replace function foo (p_deptno number) return number
/*DETERMINISTIC*/
as
l_sal number;
begin
select sum(sal) into l_sal from emp where deptno = p_deptno;
dbms_lock.sleep(1);
DBMS_OUTPUT.PUT_LINE(p_deptno);
return l_sal;
end;
/
SET SERVEROUTPUT ON
select ename, deptno, foo(deptno) dept_sal from emp;
Returning the original 14 rows from that table, the above query will run approx. 14 seconds and DBMS_OUTPUT.PUT_LINE will list one deptno for each of those 14 rows.
Now uncomment the DETERMINISTIC reserved word from the function, recompile it and rerun the above query. It will finish in about 4 seconds and the DBMS_OUTPUT listing will show you that function was executed only once (or twice) for each department. For all the "missing" occurences the function's results were obtained from sime kind of result cache.
Cheers, Jurij
Thanks for the excellent example and detailed explanation, Jurij!
ReplyDeleteIt's good to see there are circumstances under which the optimization works in 10gR2 as well.
And so the above only shows that in 11g it works more often than it did in 10gR2.
Regards,
Rob.