A reader, April 28, 2002 - 9:22 pm UTC
how it work
A reader, April 29, 2002 - 2:16 am UTC
if i have table t with
10
20
30
40
how index affect can explain more
Sagi, April 29, 2002 - 11:08 am UTC
Hi Tom!
I saw this DETERMINISTIC for the first time. Can you tell us more about this. Please
Thank you in Advance.
Regards,
April 29, 2002 - 12:52 pm UTC
A reader, May 28, 2003 - 10:35 am UTC
tom, our application uses several lookups. the items (distributed across several lookup tables) are always of type number and are always accessed through their names. we want functions with "meaningful" names (instead of scalar sub-queries) to make use of those entries from within SQL.
we could have created one (packaged) function for each and every lookup table entry such as:
select <function_for_lookup_entry_1> from dual;
.
.
.
select <function_for_lookup_entry_n> from dual;
but in order to be more flexible we are thinking of putting ALL these entries into ONE "packaged" associative array instead. item names serve as indexes for the entries which then contain the numbers to be used.
the array is initialized when the package is called for the first time in a session. its data (name and value for each item) is read from the lookup tables. thus our lookup function interface is always "up-to-date", and there is no hard-coded "link" left anymore between the entries in the lookup tables (item/value) and the code (function-name/value according to the lookup table entries).
we would just have to issue:
select <...>.GetCodeForItem('<item_name>') from dual
I know you sometimes use to say "don't be TOO generic - be explicit!". but would this apply for what we are trying to achieve? what do you think about our approach?
and in addition: would you think we would take a risk if we declare that GetCodeForItem to be DETERMINISTIC to improve performance?
I'm very, very curious about your comments on that ...
May 28, 2003 - 6:59 pm UTC
I don't like it at all. Calling PLSQL from SQL should not be done "as a matter of fact"
VIEWS = way to hide constructs like scalar subqueries
you'll find calling plsql from sql has a high cost that becomes very noticable if you do it hundreds of thousands of times.
A reader, May 31, 2003 - 11:14 am UTC
in our projects it became very "common" to use the return values of those packaged functions as filters in WHERE clauses WITHIN views. thus the package state provides some kind of "adjustable environment" for SQL statement inside the views.
isn't this achieved best by using contexts at all? was there any reason in former releases (back to 7.3.x) to prefer this "package approach" rather then using contexts?
May 31, 2003 - 11:56 am UTC
contexts would be infinitely more efficient performance wise.
7.3, you had but one "context" variable -- dbms_application_info.set_client_info and then 'select * from ... where x = userenv('client_info')
j., June 02, 2003 - 5:01 am UTC
tom, I've three additional questions:
since all values are stored as varchar2 in contexts but in most cases the values we want/have to store are for (small) enumerations and of type number we would have to convert from varchar2 to number all the time - or otherwise change these datatypes within our data model from number to varchar2 to avoid the mismatch in type between the table columns and the return value of SYS_CONTEXT.
1) would you now think of changing an already existing model in order to store '1', '2', '3' instead of 1, 2, 3 to make the datatypes fit without any conversion?
2) is the number of entries for contexts limited?
3) how can one "search" for certain entries within a given context (without trial and error via SYS_CONTEXT)?
June 02, 2003 - 7:35 am UTC
you would simply use to_number(sys_context()) in the query (or to_date when appropriate)
do NOT change the model. you have numbers, nothing wrong with using a to_number on sys_context to do the right thing.
2) only by memory (no, not really)
3) select * from session_context
j., June 02, 2003 - 3:56 pm UTC
tom, is SYS_CONTEXT executed once per SQL statement (you wrote somewhere else ORACLE treats it as if bind variables were used) or for each row?
June 02, 2003 - 4:16 pm UTC
it is a bind, once per query in effect.
j., June 04, 2003 - 3:57 am UTC
now there 's only one problem left. we want to raise an exception if an attempt is made from within SQL on an entry that does NOT exist in a given context.
in order to achieve this we created the following function:
create or replace function RaiseError
return varchar2
is
begin
Raise_Application_Error(-20001, 'Something went wrong.');
end;
/
and used it that way:
select nvl(SYS_CONTEXT('<our_context>', '<some_entry>'), RaiseError)
from dual
but got the exception we expected to get only in case we would have tried to access an NON-EXISTENT entry.
we thought expr2 for nvl would have been evaluated only if expr1 was null. why is our function invoked at all if expr1 is NOT null?
sure, we could do:
select case when SYS_CONTEXT('<our_context>', '<some_entry>') is not null
then SYS_CONTEXT('<our_context>', '<some_entry>')
else RaiseError
end
from dual
or - even worse - bring PL/SQL back into play (just to "wrap" and "hide" all this case stuff).
how would you solve this kind of problem?
btw: how would ORACLE in general treat IDENTICAL expressions within the SQL statement - evaluate them once per statement (no matter how often they occur) or once per occurrence of the expression within that statement? would SYS_CONTEXT in our case be called once or twice?
June 04, 2003 - 8:09 am UTC
You can use DECODE or CASE to short circuit the evaluation. Apparently, NVL(a,b) evaluates both a and b each time regardless.
ops$tkyte@ORA920> create or replace function foo return varchar2
2 as
3 begin
4 raise program_error;
5 end;
6 /
Function created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> column d format a20
ops$tkyte@ORA920>
ops$tkyte@ORA920> select nvl( sys_context('userenv','session_user'), foo ) d from dual;
select nvl( sys_context('userenv','session_user'), foo ) d from dual
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.FOO", line 4
ops$tkyte@ORA920> select decode( sys_context('userenv','session_user'), null, foo, sys_context('userenv','session_user') ) d
2 from dual;
D
--------------------
OPS$TKYTE
ops$tkyte@ORA920> select case
2 when sys_context('userenv','session_user') is null then foo
3 else sys_context('userenv','session_user')
4 end d
5 from dual;
D
--------------------
OPS$TKYTE
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace function foo return varchar2
2 as
3 begin
4 dbms_application_info.set_client_info( userenv('client_info')+1 );
5 return userenv('client_info');
6 end;
7 /
Function created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec dbms_application_info.set_client_info( 0 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select nvl( comm, foo ) from emp;
NVL(COMM,FOO)
-------------
1
300
500
4
1400
6
7
8
9
0
11
12
13
14
14 rows selected
<b>see how the function is called 14 times...but</b>
ops$tkyte@ORA920> exec dbms_application_info.set_client_info( 0 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select decode( comm, null, foo, comm ) d from emp;
D
--------------------
1
300
500
2
1400
3
4
5
6
0
7
8
9
10
14 rows selected.
<b>decode does it 10</b>
j., June 12, 2003 - 3:52 pm UTC
our current approach is to use packaged deterministic functions in where clauses within our views in order to get these views "configured" based on the users "environment" contained in package variables.
would you think that it could - in general - be worth it to change the EXISTING application from the common "procedural" solution to a context centric one?
to be honest: comparing these two approaches I was unable to measure that huge performance gain I expected from contexts treated as bind variables and processed without those switches between SQL and PL/SQL.
but, on the other hand, wouldn't DETERMINISTIC functions minimize this overhead too (and sometimes be more handy than contexts)?
June 12, 2003 - 4:11 pm UTC
depends on the query plans here. if you were doing indexed keyed reads then:
select * from emp where ename = pkg.f(x);
will call pkg.f(x) just to get the key value to be used against the index (deterministic, whatever).
on the other hand, kill the index access and pkg.f(x) will be called once per row (deterministic, whatever)..
deterministic doesn't come into play here -- only with function based indexes and MVs. If you did not measure any difference, then you were already calling the functions just once (i many times used tricks like:
where column = ( select f(x) from dual where rownum > 0 )
to get this function called ONCE per query instead of once per row per query)
Consider:
ops$tkyte@ORA920> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace function f( x in number ) return varchar2
2 deterministic
3 as
4 begin
5 dbms_application_info.set_client_info( userenv('client_info')+1 );
6 return 'KING';
7 end;
8 /
Function created.
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec dbms_application_info.set_client_info( 0 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> select empno, ename from emp where ename = f(5);
EMPNO ENAME
---------- ----------
7839 KING
ops$tkyte@ORA920> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
14
<b>once per row</b>
ops$tkyte@ORA920> exec dbms_application_info.set_client_info( 0 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> select empno, ename from emp where ename = ( select f(5) from dual where rownum > 0 );
EMPNO ENAME
---------- ----------
7839 KING
ops$tkyte@ORA920> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
1
<b>once per query</b>
ops$tkyte@ORA920> exec dbms_application_info.set_client_info( 0 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create index ename_idx on emp(ename);
Index created.
ops$tkyte@ORA920> select empno, ename from emp where ename = f(5);
EMPNO ENAME
---------- ----------
7839 KING
ops$tkyte@ORA920> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
2
<b>index access for whatever reason calls it twice, but only twice -- to get the key to scan the index with</b>
So, you must already have been using it in a fashion whereby it wasn't called much.
j., June 12, 2003 - 6:26 pm UTC
very smart answer. for me another lesson learnt: the index makes the difference: its existence causes the function to be invoked just to get the return values in order to use them to scan the index entries, right? this seems to hold true no matter if the function is deterministic or not, right?
but how can calls be skipped at all in case the function is NOT deterministic? the results returned by the function may differ on each and every call and have therefore to be calculated per row?
I don't understand the reasons why:
1st: the NON-DETERMINISTIC function is NOT called per row
2nd: an index is needed to prevent ORACLE from calling the DETERMINISTIC function MULTIPLE times.
3rd: your (very neat) rownum trick isn't applied in general to queries where "patterns" like the above appear?
4th: a function like that:
create or replace function f return number
deterministic
is
begin
dbms_application_info.set_client_info(userenv('client_info') +1);
return 10;
end f;
/
is NOT called only ONCE in a statement like that:
select f from all_objects;
please tell me: what did I miss?
June 12, 2003 - 8:08 pm UTC
correct -- deterministic does not come into play here at all.
deterministics comes into play in 2 cases
o function based indexes. you can index deterministic functions. you cannot index other kinds
o MV's -- there are query rewrites that can happen with deterministic functions, they will not happen otherwise.
1) the non-deterministic function would be called exactly the same number of times as the deterministic
2) it is the manner in which the result is processed. for index range scans you
a) get a value
b) scan for it
in full tablescans you
a) read every row
b) process the predicate (which calls a function)
3) yes it is. by the definition of rownum it is...
4) correct, it is not
you didn't miss anything?
j., June 13, 2003 - 7:23 am UTC
as for 3: tom, I just wanted to know WHY ORACLE needs yor that rownum trick in order to get the query executed that way (if the function involved is defined to be deterministic)?
as for 4: yes, I know/see it is, but the question is (once again) WHY?
today I've got the following reply on a TAR (I opened a few weeks ago) which is related to that deterministic issue. they speak of a "non public" bug:
<quote>
A deterministic function is one that with a given input value always returns the same output value.
For example:
Create or replace function square(a number) return number deterministic is
begin
return a * a;
end;
For a given value of 'a' the result will be always the same: a^2.
This means that if we use this function in a query like this:
select sum(square(deptno)) from emp;
square function could be called only 3 times, because there are only 3 different values of deptno. If you define the function without deterministic keyword, the function must be called 14 times, for each row of the emp table.
This is the expected behavior, but THERE IS A BUG (a NON PUBLIC ONE, you cannot see it on Metalink) about this optimization is not considered, hence the function is called 14 times regardless how was defined.
This bug is still on development and we don't have information about when the fix will be ready.
</quote>
so from my point of view it 's now clear that I can expect a deterministic function to be invoked "on demand" instead of per row in a future release.
do YOU have/can YOU get any additional information on the bug mentioned above?
June 13, 2003 - 8:33 am UTC
because deterministic works in exactly two places right now -- function based indexes and mv's. We used neither here.
Not all bugs are bugs. This is not a bug. Think about it in this case. Use that square example above. Now, assume that dept has 1,000,000 rows. Assume further that deptno is just a number (38 digits of precision). Assume further that deptno is in fact unique in this table. now what? you want that query to run *doggedly slow* when you query:
select sum(square(deptno)) from emp;
because each time you call square, Oracle must first check some magical cache of "function + inputs -> answer" to see if the question was already asked or not and then, upon not finding it -- compute the answer and put it into this cache? The very act of looking it up, not finding it and putting it in there probably takes longer in this case then running the function in the first place.
And now I have this million element cache consuming gobs of ram -- doing basically nothing.
Where there is room for an enhancement (and this would be an enhancement, not a bug) would be:
select * from emp where ename = f('CONSTANT');
and f is deterministic. Here, it would be very nice if Oracle coded:
select * from emp where ename = :x
and transparently bound in the value for :x for us -- just like it does with SYS_CONTEXT() -- but that leads us to - if you have such a function, and you want to ensure it is called but once, SYS_CONTEXT is the tool you were looking for.
j., June 13, 2003 - 9:47 am UTC
yes, I totally agree (although I expect things to work as described in ORACLE's documentation: they did not only mention fbis and mvs in this context but "normal" queries too)
but nevertheless my case is more like your sample - or even more simple: a function without any argument at all (i.e. just returning a package variable used as a some kind of "context" as described in my question from june 12, 2003).
back to the roots of this question - I just wanted to ask you if you would think that switching from deterministic functions to contexts would bring a performance gain which makes the work that has to be done (for an existing application) worth it - given that deterministic functions work the way one can expect ...
June 13, 2003 - 10:37 am UTC
If the function is being called many times in the execution of a query
then sys_context would be very very appropriate and would in all likelyhood increase performance by removing the context switch from sql to plsql and back that is occuring many times.
If the function is being called once already, you will probably not notice any appreciable changes in query response time.
What do you think of that?
j, July 12, 2003 - 1:35 pm UTC
IMHO there are two contradictory goals: avoid context switches between PL/SQL and SQL by using contexts AND get PL/SQL and SQL code robust by using symbols instead of literals.
goals: assure that context entries in code do exist at runtime and getting their names/contents constrained in addition (e.g. for uniqueness).
solution:
1) put entries into database table(s) in order to get entries "registered", their names/contents "restricted" and context(s) "loaded" at logon time.
2) generate interface package(s) providing the symbols for those entries needed in PL/SQL and SQL code.
3) use package entries instead of literals for accessing context(s) in order to get verified whether a referenced context entry exists or not.
using deterministic packaged functions, generated from table contents and returning names of context entries one can make use of contexts to minimize overhead AND rely on the existence of context entries referenced by code (through package entries) at compile time.
what do YOU consider pros & cons of this infrastructure?
July 12, 2003 - 2:45 pm UTC
#3 brings you all of the way back to the original problem.
?
j., July 12, 2003 - 4:55 pm UTC
but what is wrong with SYS_CONTEXT('MyCtx', MyPackage.MyFunctionReturningMyEntryName) instead of SYS_CONTEXT('MyCtx', 'MyEntryName')?
July 12, 2003 - 6:44 pm UTC
ops$tkyte@ORA920> create or replace package my_pkg
2 as
3 function f return varchar2 deterministic;
4 end;
5 /
Package created.
ops$tkyte@ORA920> show err
No errors.
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace package body my_pkg
2 as
3 function f return varchar2
4 deterministic
5 is
6 begin
7 dbms_application_info.set_client_info( userenv('client_info')+1 );
8 return 'x';
9 end;
10 end;
11 /
Package body created.
ops$tkyte@ORA920> create or replace context myCtx using my_pkg;
Context created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec dbms_application_info.set_client_info( 0 );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> select * from emp where ename = sys_context( 'myCtx', my_pkg.f );
no rows selected
ops$tkyte@ORA920> select sys_context( 'userenv', 'client_info') sc from dual;
SC
----------
14
ops$tkyte@ORA920>
<b>we don't want the function called 14 times. back to square one</b>
I agree - we don't ...
j., July 13, 2003 - 3:59 am UTC
I didn't even know/expect that contexts are treated as if bind variables were only in case there is no PL/SQL involved.
this leads (back) to the question: what other opportunities does one have to achieve what I wanted to do - avoid any attempt to access non-existent context entries in any piece of code (handling this at runtime is second best only)?
July 13, 2003 - 9:44 am UTC
I've gone as far as I can go with this one. Short of good coding practices there aren't any.
the "slight" difference
j., August 01, 2003 - 1:12 pm UTC
why are user defined contexts NOT treated like the predefined context USERENV?
select SYS_CONTEXT('USERENV', 'NON_EXISTENT_ENTRY') from dual
works the way I wanted but
select SYS_CONTEXT('MYCTX', 'NON_EXISTENT_ENTRY') from dual
does NOT ...
August 01, 2003 - 1:38 pm UTC
q) how we we konw that 'NON_EXISTENT_ENTRY' isn't valid?
a) we don't
j., August 01, 2003 - 3:42 pm UTC
raising an exception in case
select * from session_context
where namespace = 'MYCTX'
and attribute = 'NON_EXISTING_ENTRY'
returns no rows would fit the needs ...
btw: the statement
select SYS_CONTEXT('USERENV', 'XXX') from dual
raises ORA-20003 under 8.1.7.1.0 but NO exception with 9.2.0.1.0.
August 01, 2003 - 4:30 pm UTC
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ops$tkyte@ORA920> select SYS_CONTEXT('USERENV', 'XXX') from dual;
select SYS_CONTEXT('USERENV', 'XXX') from dual
*
ERROR at line 1:
ORA-02003: invalid USERENV parameter
very helpful!
A reader, August 01, 2003 - 4:11 pm UTC
Tom
Could you kindly explain the rownum trick - what it
does?
select empno, ename from emp where ename = ( select f(5) from dual where rownum > 0 );
usually, I have seen queries with rownum < somthing...
Thank you!
August 01, 2003 - 4:32 pm UTC
it materialized the subquery -- in order to stabilize the result set (subquery) with rownum -- we actually have to "build it" since rownum is dynamically assigned. So, the subquery is executed into temp and the result of that is used. since the result set just calls the function once -- there we go.
*** but it 's different in (my) 9.2.0.>>>1<<<.0 ***
j., August 01, 2003 - 4:38 pm UTC
I've checked this in two stand-alone instances ...
sorry could not understand
A reader, August 01, 2003 - 5:41 pm UTC
"it materialized the subquery -- in order to stabilize the result set (subquery)
with rownum -- we actually have to "build it" since rownum is dynamically
assigned. So, the subquery is executed into temp and the result of that is
used. since the result set just calls the function once -- there we go."
Hi Tom
Sorry could not understand - I know what materialized
views are but what do you mean by "materialized the query".
I know how rownum works but what is the significance of
"dynamic assignment" of rownum?
Sorry - if these are too basic questions!!
Thanx!!
August 01, 2003 - 7:08 pm UTC
if you had:
select *
from t
where x in ( select max(x) from t2 group by y )
you understand that we would have to "materialize" the subquery -- make it "exist" -- to have the set of max(x)'s by y. We would put that into temp and then use it to process further.
rownum in a subquery does the same thing -- since rownum is so "fluid", in order to get a stable result set to look at (one that would NOT return ambigous, non-reproducible answers to us depending on how the query was processed) we must "materialize" (produce, actually get) the subquery and then use its results.
Thanx Tom!
A reader, August 03, 2003 - 8:39 pm UTC
So can I say that materializing a query simply means
that you evaluate the query (or subquery) and have the results stored in temp to be used by the enclosing
query (in our case)?
Thank you - You are just amazing!!!
Is sys_context deterministic?
Piotr Jarmuz, November 10, 2003 - 7:02 am UTC
I must use the sys_context-instead-of-bind-variable trick and timings shows it is 3-4 times slower than pure binds. So I assume it is not deterministic and called many times. But I can't prove it.
Can you clarify?
Thanks in advance.
November 10, 2003 - 8:11 am UTC
time to look elsewhere for "why":
ops$tkyte@ORA920PC> @test
ops$tkyte@ORA920PC> /*
DOC>drop table t;
DOC>create table t as select * from all_objects;
DOC>*/
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace procedure p1
2 as
3 l_str varchar2(25) := '%';
4 begin
5 for x in ( select * from t where object_name like l_str )
6 loop
7 null;
8 end loop;
9 end;
10 /
Procedure created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace context myCtx using p2
2 /
Context created.
ops$tkyte@ORA920PC> create or replace procedure p2
2 as
3 begin
4 dbms_session.set_context( 'myCtx', 'myBind', '%' );
5 for x in ( select * from t where object_name like sys_context( 'myCtx', 'myBind' ) )
6 loop
7 null;
8 end loop;
9 end;
10 /
Procedure created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> exec runstats_pkg.rs_start
PL/SQL procedure successfully completed.
ops$tkyte@ORA920PC> exec p1
PL/SQL procedure successfully completed.
ops$tkyte@ORA920PC> exec runstats_pkg.rs_middle
PL/SQL procedure successfully completed.
ops$tkyte@ORA920PC> exec p2
PL/SQL procedure successfully completed.
ops$tkyte@ORA920PC> exec runstats_pkg.rs_stop(1000000)
Run1 ran in 126 hsecs
Run2 ran in 124 hsecs
run 1 ran in 101.61% of the time
Name Run1 Run2 Diff
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
64,468 64,092 -376 100.59%
PL/SQL procedure successfully completed.
show YOUR test case (a 100% fully complete one please -- tables, inserts, etc so we can all run it)
Got it
Piotr Jarmuz, November 10, 2003 - 7:57 am UTC
My "Got it" was before your answer
Piotr Jarmuz, November 10, 2003 - 12:50 pm UTC
It was something else completely unrelated.
The query I have told about has to process ~300000 calls divided by 6 call places = ~50000 rows (I checked it with dbms_application_info and another packaged function) but it returns only few.
Query looks something like this:
select * from phonenumber_x where address_id in (
select a.id from address_x a, store_mission_x sm where
a.rev_state=10 and sys_context('COOS_CONTEXT','date')>=a.rev_start and sys_context('COOS_CONTEXT','date')<a.rev_end and
sm.rev_state=10 and sys_context('COOS_CONTEXT','date')>=sm.rev_start and sys_context('COOS_CONTEXT','date')<sm.rev_end and
sm.address_id=a.id and sm.ext_id=:b_ext_id
) and id in (select phonenumber_id from phonetype pt, phonenumber_type_x pnt
where rev_state=10 and sys_context('COOS_CONTEXT','date')>=rev_start and sys_context('COOS_CONTEXT','date')<rev_end and pt.id=pnt.phonenumber_id and name=:b_name)
where *_x are views on other tables all in all 8 tables, 6 indices and explain plan with 30 steps. Dates are for revisioned objects and revisioned associations. They (dates) are implicit so I wanted to save the need to bind them explicitly in "using clause". Explicit binds correspond to the stored procedure parameters.
While this query was going on another test application was running too and disturbed my timings. That's it. So I guess there is no need to show you 100% test case, is it?
You are right. As always.
But I'd realized my mistake before your answer appeared. Thank you anyway.
Regards,
Piotr
Yikes - gotta stop using NVL
Kim Berg Hansen, February 25, 2004 - 5:29 am UTC
Hi, Tom
You wrote sometime in this discussion :
"Apparently, NVL(a,b) evaluates both a and b each time regardless."
That's a big surprise - but your test shows it and I have tested it also, so it's true...
I think that's not very logical of Oracle to do so.
Of course it doesn't matter if you use constants or simple columns (NVL(a,' ') for example), but it's quite different if you use a complex function/expression for the "b" value.
And I have a few such cases I now should rewrite to use DECODE - happily the workaround is easy :-)
Contexts and database links
Alex, June 13, 2021 - 5:36 am UTC
Hi
Please tell if contexts behave the same as binds with db links?
I've noticed that in my system sometimes it takes longer time if I'm using queries with db links and contexts:
Here's a test case (11.2.0.4 EE Linux x86) with a local db link pointing to the same database - I need it that way in my application:
create or replace context TEST_CONTEXT using set_test_context;
create or replace procedure set_test_context(p_attr in varchar2, p_val in varchar2) is
begin
dbms_session.set_context ('TEST_CONTEXT', p_attr, p_val);
end;
create database link local connect to test identified by test using 'LOCAL';
create table test_bind
(
id number,
value varchar2(5)
);
/
insert into test_bind
SELECT LEVEL just_a_column,
'test'
FROM dual
CONNECT BY LEVEL <= 1000000;
commit;
--Setting only context here as bind values are prompted by SQL developer in my case.
exec set_test_context ('id_bind', 500000);
--Not using db links. Queries execution speed is almost the same. Execution plans are the same.
select avg(tb1.id) /*test local query using binds*/ from test_bind tb1 where tb1.id>:id_bind;
select avg(tb1.id) /*test local query using context*/ from test_bind tb1 where tb1.id>sys_context('TEST_CONTEXT','id_bind');
--Using db links. The second query execution speed is approximately twice larger on average of 10 executions.
--Plan hash value is 0 for the first query for some reason, so I can't compare what's wrong.
select avg(tb1.id) /*test remote query using binds*/ from test_bind@local tb1 where tb1.id>:id_bind;
select avg(tb1.id) /*test remote query using context*/ from test_bind@local tb1 where tb1.id>sys_context('TEST_CONTEXT','id_bind');
Thank you
June 15, 2021 - 9:36 am UTC
The problem is the database has to go back across the database link to fetch the context value. Sadly you can't directly reference context on remote sites.
You can work around this by:
- Creating functions set/get the context on the remote site
- Call the get context function on the remote site in a scalar subquery. This is so you take advantage of scalar subquery caching to avoid the context switches for the function
e.g.:
exec set_test_context@loopback ('id_bind', 500000);
create or replace function get_context
return int deterministic as
begin
return sys_context('TEST_CONTEXT','id_bind');
end get_context;
/
set timing on
select avg(tb1.id) /*test remote query using remote context*/
from test_bind@loopback tb1
where tb1.id> ( select get_context@loopback from dual@loopback );
AVG(TB1.ID)/*TESTREMOTEQUERYUSINGREMOTECONTEXT*/
750000.5
Elapsed: 00:00:00.106For more on tuning distributed queries & getting their plans, see the Office Hours session I did on this some time ago
https://asktom.oracle.com/pls/apex/asktom.search?oh=5684
Thank you for the reply
Alex, June 18, 2021 - 5:15 pm UTC
Thank you for the reply and a nice example
June 21, 2021 - 1:12 pm UTC
You're welcome