Monday, January 23, 2006

Yet another new...

YANS. Yet another new site. Howard Rogers set up an Oracle Wiki. Looks like an interesting idea (yet something else to germinate and see if it grows and becomes something, or simply whithers on the vine and dies).

You can see my first contribution to this here. This is such a frequently asked question, I’ll just publish it anywhere I can…

I learned something new at the Wiki today. I’ll have to update my SQL Techniques seminar with this new information. In 10gR2 – we have more support for deterministic functions (thanks to Jonathan Lewis for noting that). Now in SQL – if you call a deterministic function, Oracle will cache the results. This could be dramatic depending on how long your PLSQL function takes.

Here is what we could expect in Oracle 10gR1 and before:

ops$tkyte@ORA10GR1> create or replace function f( p_deptno in number )
2 return number
3 DETERMINISTIC
4 as
5 begin
6 dbms_application_info.set_client_info(
7 userenv('client_info')||','||p_deptno );
8 return p_deptno;
9 end;
10 /
Function created.

ops$tkyte@ORA10GR1> exec dbms_application_info.set_client_info('');
PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> select ename, f(deptno)
2 from scott.emp
3 /

ENAME F(DEPTNO)
---------- ----------
SMITH 20
...
MILLER 10
14 rows selected.

ops$tkyte@ORA10GR1> select ltrim(userenv('client_info'),',')
2 from dual
3 /

LTRIM(USERENV('CLIENT_INFO'),',')
--------------------------------------------------
20,30,30,20,30,30,10,20,10,30,20,30,20,10

Notice that the function F has been called 14 times (you can count them) – once for each of the DEPTNO values in the EMP table. Even though we promised to return the same answer for each one (the deterministic clause). Even if we do things “in order”:

ops$tkyte@ORA10GR1> exec dbms_application_info.set_client_info('');
PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> select ename, f(deptno)
2 from (select * from scott.emp order by deptno)
3 /

ENAME F(DEPTNO)
---------- ----------
CLARK 10
...
WARD 30
14 rows selected.

ops$tkyte@ORA10GR1> select ltrim(userenv('client_info'),',')
2 from dual
3 /

LTRIM(USERENV('CLIENT_INFO'),',')
--------------------------------------------------
10,10,10,20,20,20,20,20,30,30,30,30,30,30

It called our function over and over and over again. So, in 10gR1 and before I looked at using scalar subqueries and the fact that Oracle will cache them (see this book for details – excellent book all together). So, running the original example with a scalar subquery, we see:

ops$tkyte@ORA10GR1> exec dbms_application_info.set_client_info('');
PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR1> select ename, (select f(deptno) from dual) f_deptno
2 from scott.emp
3 /

ENAME F_DEPTNO
---------- ----------
SMITH 20
...
MILLER 10

14 rows selected.

ops$tkyte@ORA10GR1> select ltrim(userenv('client_info'),',')
2 from dual
3 /

LTRIM(USERENV('CLIENT_INFO'),',')
--------------------------------------------------
20,30,10

Now, this was “the best case”, the function was called but three times – it could be called more than that even with the scalar subquery due to collisions in the subquery cache and such.

In 10gR2 however, running just this:

ops$tkyte@ORA10GR2> exec dbms_application_info.set_client_info('');
PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select ename, f(deptno)
2 from scott.emp
3 /

ENAME F(DEPTNO)
---------- ----------
SMITH 20
...
MILLER 10

14 rows selected.

ops$tkyte@ORA10GR2> select ltrim(userenv('client_info'),',')
2 from dual
3 /

LTRIM(USERENV('CLIENT_INFO'),',')
--------------------------------------------------
20,30,20,10

Demonstrated that Oracle is caching the results – not “perfectly” – it called the function twice for DEPTNO 20, but 4 calls is a lot better than 14.
POST A COMMENT

16 Comments:

Anonymous Anonymous said....

Never knew about "DETERMINISTIC" :(
Q: Since which version is the "DETERMINISTIC" clause available ?

Mon Jan 23, 06:01:00 PM EST  

Anonymous Gabe said....

Tempted by the dark side? ... YANS?

At least it wasn't "Yet another Wiki new site" :)

Mon Jan 23, 06:40:00 PM EST  

Blogger Robert said....

Looks like XE gets it right:

LTRIM(USERENV('CLIENT_INFO'),'
----------------------------------------------------------------
20,30,10

Mon Jan 23, 08:47:00 PM EST  

Blogger Connor McDonald said....

"deterministic"

way back in 8.1.5

Mon Jan 23, 09:16:00 PM EST  

Blogger Thomas Kyte said....

tempted by the dark side

No, for whatever reason, when I hear "yet another", I'm always reminded of YACC and LEX. Tools I've used - but still don't know if I fully understood :)

So, yet another always seems to come out in letters for me...

That and using the abbreviations is OK as long as you SPELL IT OUT the first time you use it (like a book would/should)



Looks like XE gets it right:

No, it has to do with the order of rows in the table - the cache is affected by the way the data "comes"

ops$tkyte@XE> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta
PL/SQL Release 10.2.0.1.0 - Beta
CORE 10.2.0.1.0 Beta
TNS for Linux: Version 10.2.0.1.0 - Beta
NLSRTL Version 10.2.0.1.0 - Beta

ops$tkyte@XE> exec dbms_application_info.set_client_info('');

PL/SQL procedure successfully completed.

ops$tkyte@XE> select ename, f(deptno)
2 from scott.emp
3 /

ENAME F(DEPTNO)
---------- ----------
SMITH 20
...
MILLER 10

14 rows selected.

ops$tkyte@XE> select userenv( 'client_info' ) from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
,20,30,20,10

ops$tkyte@XE> exec dbms_application_info.set_client_info('');

PL/SQL procedure successfully completed.

ops$tkyte@XE> select ename, f(deptno)
2 from (select * from scott.emp order by deptno )
3 /

ENAME F(DEPTNO)
---------- ----------
CLARK 10
...
MARTIN 30

14 rows selected.

ops$tkyte@XE> select userenv( 'client_info' ) from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
,20,30,10

Mon Jan 23, 10:03:00 PM EST  

Blogger Q u a d r o said....

There is also emprovement for policy functions:


In previous releases, policies were dynamic, which means that the database runs the policy function for each query or DML statement. In addition to dynamic policies, the current release of Oracle Database provides static and context-sensitive policies.


Static policy function get called once and result are cached in the SGA - same "deterministic" behaviour.

Mon Jan 23, 11:14:00 PM EST  

Blogger Niall said....

Tom wrote
No, for whatever reason, when I hear "yet another", I'm always reminded of YACC and LEX. Tools I've used - but still don't know if I fully understood

Or indeed YAPP or for the /. crowd YAST.

Niall

now if someone can tell me what gbvcpsdm the word verification is :(

Tue Jan 24, 12:48:00 PM EST  

Blogger Robert said....


Robert said: Looks like XE gets it right...

Tom said:
No, it has to do with the order of rows in the table - the cache is affected by the way the data "comes"


How do you find the real "order of rows" ?
Can you tell if yours is different from mine from
My SQL output?

Tue Jan 24, 03:24:00 PM EST  

Anonymous John said....

Tom,

I was surprised you left out YAPP (Yet Another Performance Profiler) from Anjo Kolk in your list of "Y..."s ;0) [Thanks to Niall - he caught it before I did].

Now I wonder what happens if a certain "expert" (or his ever present cohorts!) take it upon themselves to enter into an editing war on Howard's YANS :( Should be interesting, to say the least!

Thu Jan 26, 08:18:00 PM EST  

Blogger Rich said....

Hi Tom,

Either the dizwell site is down or this site has come and gone already...

Rich Murnane

Thu Mar 30, 09:22:00 AM EST  

Blogger Fábio Oliveira said....

But will Oracle cache results for functions that access data from a table if we use the deterministic functions in 10gr2?

Sat May 10, 06:14:00 PM EDT  

Blogger Thomas Kyte said....

@Fabio -

only in a single sql statement call - eg:

select f(deptno) from emp;

if F is deterministic, Oracle will (in 10gr2) cache some of the results - but not perfectly. Meaning - not 100%

Sat May 10, 06:39:00 PM EDT  

Blogger Fábio Oliveira said....

Tom,
even if F access data from tables in an SQL call?
Wouldn't Oracle ignore the DETERMINISTIC hint if so?
It's so hard to find some clear info about the use of this hint. :/

Sat May 10, 08:13:00 PM EDT  

Blogger Thomas Kyte said....

@Fabio

deterministic is not a hint

deterministic is YOU telling ORACLE "if you give me X as an input, I will always tell you Y is the output, I will be constant, predicable, deterministic"

If you lie to Oracle, Oracle will lie to you. If you define a function as deterministic and IT IS NOT (because, it for example, queries its results from a TABLE) - then you will probably get the wrong answer...


deterministic is only used for function based indexes - in 10gr2, it can be used to cache some function results - which BY DEFINITION - would be SAFE (you said it was safe)....

Use at your own risk, is you define your function as deterministic and it is not... well - what can we do?

Sat May 10, 09:46:00 PM EDT  

Anonymous Dmitry Lipodat said....

Can I be sure that in next query (or DML) Oracle will recall my deterministic function for new value, or it can keep the result in cache for some queries?

Wed Dec 17, 08:35:00 AM EST  

Anonymous Dmitry Lipodat said....

One more question:

SQL> create or replace function x$f(atxt varchar2) return varchar2 deterministic as
2 begin
3 dbms_application_info.set_client_info(userenv('client_info') || ',' || atxt);
4 return atxt;
5 end;
6 /

Function created.

SQL> exec dbms_application_info.set_client_info('')

PL/SQL procedure successfully completed.

SQL> select x$f(0) from x$t
2 /

X$F(0)
--------------------------------------------------------------------------------
0
0
0
0
0
0
0
0
0
0

10 rows selected.

SQL> select userenv( 'client_info' ) from dual
2 /

USERENV('CLIENT_INFO')
----------------------------------------------------------------
,0,0


Why it called twice? not 1 or 10 times but exact 2?

Wed Dec 17, 08:50:00 AM EST  

POST A COMMENT

<< Home