Every time I discuss this topic with somebody I get the “what are you talking about?” look on his/her face so this is just one of those “repetita iuvant” posts to make sure we (including me first) don’t get burned by it 😀
Each SQL executed from PL/SQL can run at a different SCN thus “see” different data depending when the SQL is started. It is known, documented and easy to prove.
In session 1: declare l_sal number; begin select sal into l_sal from emp where empno = 7369; dbms_output.put_line('First salary: '||l_sal); dbms_lock.sleep(10); select sal into l_sal from emp where empno = 7369; dbms_output.put_line('Second salary: '||l_sal); end; / In session 2 while the previous block is still running: update emp set sal = 0 where empno = 7369; commit; Result will be First salary: 800 Second salary: 0
The same applies when a SQL calls a PL/SQL program with in turn calls SQLs.
This is documented and easy to prove as well plus a nice presentation from Bryn Llewellyn (PL/SQL Product Manager, I think) is available here. I’ll skip the steps to keep the post short.
So using a SQL calling PL/SQL calling SQL is a big NO if the table accessed by the recursive SQLs aren’t static (read only?) or if you are not willing to get weird results like the following where salary for the same employee is different within the same resultset (and I’ve no idea who would be willing 😀 )
create or replace function get_salary(n1 number) return number is l_sal number; begin dbms_lock.sleep(1); select sal into l_sal from emp where empno = n1; return l_sal; end; / select empno, get_salary(empno) sal from (select /*+ NO_MERGE */ * from emp where empno in (7369, 7499, 7521)) a, (select /*+ NO_MERGE */ rownum from dual connect by rownum <5) b; EMPNO SAL ---------- ---------- 7369 800 7499 1600 7521 1250 7369 0 7499 0 7521 0 7369 0 7499 0 7521 0 7369 0 7499 0 7521 0 Elapsed: 00:00:12.01
Scalar Subquery Caching is a caching mechanism to cache the result of a function call, thus potentially avoiding a call to the function for the same input values and so improving performance. The mechanism is explained in Bryn presentation, Tim Hall video on youtube and lots of other places so no need to add much here 😉
Would Scalar Subquery Caching make my recursive SQL read-consistent to its “parent” SQL?
To be honest I don’t see why it should but I thought about it up to the point of starting to make up weird hypothesis with no foundation so better cut it short and test!
In session 1: select empno, (select myfunc(empno) from dual) sal from (select /*+ NO_MERGE */ * from emp where empno in (7369, 7499, 7521)) a, (select /*+ NO_MERGE */ rownum from dual connect by rownum <5) b; In session 2 (need to be quick this time) update emp set sal = 0 where empno in (7369, 7499, 7521); commit; and the result in session 1: EMPNO SAL ---------- ---------- 7369 1000 7499 1000 7521 0 7369 1000 7499 1000 7521 0 7369 1000 7499 1000 7521 0 7369 1000 7499 1000 7521 0 Elapsed: 00:00:03.00
The answer is “of course NO, what were you (me) thinking?!?”.
The previous output shows how the scalar subquery caching indeed allowed to call get_salary less times (once per distinct value of EMPNO, 3 in this case) and the elapsed drop from 12 to 3 but the value cached is just whatever the first execution of the function calculated for that EMPNO.
August 27, 2015 at 2:24 am
Using OPERATOR would help, since it’s consistent within a query (uses SCN of the query beginning).
create or replace operator get_salary_op binding(number) return number using get_salary;
August 27, 2015 at 8:19 am
Thanks Victor! It’s a really nice idea if you have to use PL/SQL calling SQL from another SQL.
Personally I try to stay away from it as much as possible and do everything in a single SQL 🙂
Yesterday was one of those days when I had to hit my face on the wall to clear all the weird hypothesis I was making!
August 27, 2015 at 8:48 am
Mauro, you’re absolutely right! Pure SQL approach is preferable in real life.