Oracle Performance and Engineering

Yesterday we saw how a PL/SQL function would be called once per row, rather than once per query, when the function is supplied in WHERE clause.

Here's a really sample test case to prove that in the scott/tiger schema.

-- code starts

SQL> create table test(n number);

SQL> insert into test values (20);
SQL> commit;

SQL> create or replace function fun return number
as
t number;
begin
dbms_application_info.set_client_info(sys_context('userenv','client_info')+1 );

-- to increment the value by 1 everytime the function
-- is accessed

select n into t from test;
return t;

-- always returns 20
end;
/

SQL> exec dbms_application_info.set_client_info(0);
-- set it to 0. Otherwise would be NULL

SQL> var test_var number
SQL> exec :test_var:=fun;

SQL> select sys_context( 'userenv', 'client_info') sys_ctx from dual;

SYS_CTX
----------
1

SQL> select * from emp where deptno=:test_var;
-- using a bind variable rather than the function

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20

SQL> select sys_context( 'userenv', 'client_info') sys_ctx from dual;

SYS_CTX
----------
1

-- I am on 10g here. Single call to the function - during binding itself.

SQL > select * from emp where deptno=fun;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20


SQL> select sys_context( 'userenv', 'client_info') sys_ctx from dual;

SYS_CTX
----------
15

-- So a total of 14 calls to the function?

SQL> select count(*) from emp;

COUNT(*)
----------
14

-- Thus the function IS called once per row!

SQL > select * from emp where deptno=(select fun from dual);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20

SQL> select sys_context( 'userenv', 'client_info') sys_ctx from dual;

SYS_CTX
----------
16

-- One single call to the funtion for the execution. Optimizer treats it as "data" and not as "code"

SQL> with temp as (select fun from dual)
select * from emp where deptno = (select * from temp);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20


SQL> select sys_context( 'userenv', 'client_info') sys_ctx from dual;

SYS_CTX
----------
17

-- With WITH clause - 1 call as well!

DBMS_APPLICATION_INFO is a very useful Oracle supplied package to set session-level context variables. We will also use it to create "parameterized views" to deal with complex views where predicates cannot be pushed inside.