05 April, 2011

DETERMINISTIC Functions

In my opinion, DETERMINISTIC functions are not very well explained. Some DBAs and Developers know that a Function must be DETERMINISTIC if it is to be used to create a Function Based Index. Also, common understanding is that a Function can be DETERMINISTIC if it is guaranteed to return always return the same output value if the input value supplied is not changed.

Some explanation is in the documentation on the CREATE FUNCTION command.
"Indicates that the function returns the same result value whenever it is called with the same values for its parameters."

Yet, how does Oracle optimise the execution of a DETERMINISTIC function ? The documentation says "When the database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function."

Jonathan Lewis has also explored DETERMINSTIC functions here.

I decided to run my own tests here. I created a DETERMINISTIC function and had it executed once for each row in a 16-row result set :
SQL> create or replace function div_by_hundred (value_in number) return number DETERMINISTIC
2 as
3 divided_by_hundred number;
4 begin
5 select trunc(value_in/100) into divided_by_hundred from dual;
6 return divided_by_hundred;
7 end;
8 /

Function created.

SQL>
SQL>
SQL> drop table my_objects purge;

Table dropped.

SQL>
SQL> create table my_objects (obj_id number , obj_name varchar2(30), created date);

Table created.

SQL>
SQL> insert into my_objects
2 select object_id, object_name,created
3 from dba_objects
4 where owner in ('HEMANT','SCOTT')
5 /

16 rows created.

SQL>
SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> select obj_id, div_by_hundred(obj_id)
2 from my_objects
3 order by obj_id
4 /

OBJ_ID DIV_BY_HUNDRED(OBJ_ID)
---------- ----------------------
73179 731
73180 731
73181 731
73182 731
73183 731
73184 731
82748 827
82749 827
82751 827
85339 853
85340 853

OBJ_ID DIV_BY_HUNDRED(OBJ_ID)
---------- ----------------------
85341 853
85344 853
85346 853
85429 854
85433 854

16 rows selected.

SQL> exec dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL>

The trace file shows that the function is actually executed 16 times.
select obj_id, div_by_hundred(obj_id)
from my_objects
order by obj_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.04 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.01 0.01 0 7 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.03 0.06 0 8 0 16


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 184

Rows Row Source Operation
------- ---------------------------------------------------
16 SORT ORDER BY (cr=7 pr=0 pw=0 time=0 us cost=4 size=208 card=16)
16 TABLE ACCESS FULL MY_OBJECTS (cr=7 pr=0 pw=0 time=120 us cost=3 size=208 card=16)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.08 0.10
********************************************************************************

SQL ID: dd7zz37sxfcsc
Plan Hash: 1388734953
SELECT TRUNC(:B1 /100)
FROM
DUAL


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 16 0.00 0.00 0 0 0 0
Fetch 16 0.00 0.00 0 0 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 33 0.00 0.00 0 0 0 16

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 184 (recursive depth: 1)


Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

We know that every time we pass the same value to "trunc(:b1/100)", the result will always be the same. This function had to be executed 16 times because each value passed to the function was different -- each time a new OBJ_ID was being passed.

So, what happens if the same OBJ_ID is passed to the function each time ?
SQL> UPDATE my_objects set obj_id = 1000000;

16 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL> pause Press ENTER to proceed with new session
Press ENTER to proceed with new session

SQL> connect hemant/hemant
Connected.
SQL>
SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> select obj_id, div_by_hundred(obj_id)
2 from my_objects
3 order by obj_id
4 /

OBJ_ID DIV_BY_HUNDRED(OBJ_ID)
---------- ----------------------
1000000 10000
1000000 10000
1000000 10000
1000000 10000
1000000 10000
1000000 10000
1000000 10000
1000000 10000
1000000 10000
1000000 10000
1000000 10000

OBJ_ID DIV_BY_HUNDRED(OBJ_ID)
---------- ----------------------
1000000 10000
1000000 10000
1000000 10000
1000000 10000
1000000 10000

16 rows selected.

SQL> exec dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL>

This appears in the trace file :
select obj_id, div_by_hundred(obj_id)
from my_objects
order by obj_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.04 0 7 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.05 0 7 0 16

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 184

Rows Row Source Operation
------- ---------------------------------------------------
16 SORT ORDER BY (cr=7 pr=0 pw=0 time=0 us cost=4 size=208 card=16)
16 TABLE ACCESS FULL MY_OBJECTS (cr=7 pr=0 pw=0 time=360 us cost=3 size=208 card=16)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
Disk file operations I/O 1 0.00 0.00
SQL*Net message from client 3 0.05 0.08
********************************************************************************

SQL ID: dd7zz37sxfcsc
Plan Hash: 1388734953
SELECT TRUNC(:B1 /100)
FROM
DUAL


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1


Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 184 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************


Now, the function is executed only once ! Apparently, Oracle actually "peeks" (for want of a better word) at the value passed to the function. If it is the same as the last value (or one of the last four values ?-- see Jonathan Lewis's last paragraph in his note), it decides to not re-execute the function. That also means, that it holds the return value from the previous execution and re-uses that value -- to be returned again for the next row.


Will this behaviour hold true with DATEs ?
Stay tuned. .......

.
.
.


3 comments:

Anonymous said...

Nice article. Thank you

Anonymous said...

Hi Hermant!
Thank you for the article.
Could you please tell me how did you get such a nicely formated trace file?
I found this in my trace file:

PARSING IN CURSOR #1 len=53 dep=0 uid=0 oct=3 lid=0 tim=18397882250 hv=2716869094 ad='69633ac4'
select hr.fnc_dt(id) from hr.dt
where rownum < 15
END OF STMT
PARSE #1:c=15625,e=12265,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,tim=18397882237
EXEC #1:c=0,e=122,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=18397884488
WAIT #1: nam='SQL*Net message to client' ela= 11 driver id=1952673792 #bytes=1 p3=0 obj#=72 tim=18397884878
FETCH #1:c=0,e=794,p=0,cr=3,cu=0,mis=0,r=14,dep=0,og=1,tim=18397886096
WAIT #1: nam='SQL*Net message from client' ela= 20067 driver id=1952673792 #bytes=1 p3=0 obj#=72 tim=18397906702
WAIT #0: nam='SQL*Net message to client' ela= 11 driver id=1952673792 #bytes=1 p3=0 obj#=72 tim=18397907283
WAIT #0: nam='SQL*Net message from client' ela= 2128156 driver id=1952673792 #bytes=1 p3=0 obj#=72 tim=18400035851
STAT #2 id=1 cnt=5 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=3 pr=0 pw=0 time=283 us)'
STAT #2 id=2 cnt=5 pid=1 pos=1 obj=52796 op='TABLE ACCESS FULL DT (cr=3 pr=0 pw=0 time=138 us)'


And it's kind of hard for me to find out how many calls actually accured.
Thank you in advance.

Hemant K Chitale said...

Anonymous,

I run the tkprof utility on the raw trace file.

Hemant