Let’s check, why PRAGMA UDF makes execution faster, then regular function.
C function responsible for calling a PL/SQL code from SQL is called "plsql_run":
[oracle@rico bin]$ nm oracle | grep plsql_run
000000000ce1c090 T plsql_run
3 LINESTEXT / UTF-8
Now let’s try to create a new HR session and create a simple function which will multiple values by 2:
SQL> conn hr/hr
Connected.
SQL> get by2
1 create or replace
2 function by2(x number) return number is
3 begin
4 return x*2;
5* end;
SQL> /
Function created.
12 LINESTEXT / UTF-8
From other terminal I will connect to the HR session with GDB:
[root@rico ~]# gdb -p 10330
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-83.el6)
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Attaching to process 10330
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle...(no debugging symbols found)...done.
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libodm12.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libodm12.so
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libcell12.so...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libcell12.so
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libskgxp12.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libskgxp12.so
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libskjcx12.so...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libskjcx12.so
Reading symbols from /lib64/librt.so.1...Reading symbols from /usr/lib/debug/lib64/librt-2.12.so.debug...done.
done.
Loaded symbols for /lib64/librt.so.1
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libmql1.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libmql1.so
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libipc1.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libipc1.so
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libclsra12.so...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libclsra12.so
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libdbcfg12.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libdbcfg12.so
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libhasgen12.so...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libhasgen12.so
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libskgxn2.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libskgxn2.so
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libocr12.so...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libocr12.so
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libocrb12.so...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libocrb12.so
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libocrutl12.so...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libocrutl12.so
Reading symbols from /lib64/libaio.so.1...Reading symbols from /usr/lib/debug/lib64/libaio.so.1.0.1.debug...done.
done.
Loaded symbols for /lib64/libaio.so.1
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libons.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libons.so
Reading symbols from /lib64/libdl.so.2...Reading symbols from /usr/lib/debug/lib64/libdl-2.12.so.debug...done.
done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...Reading symbols from /usr/lib/debug/lib64/libm-2.12.so.debug...done.
done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libpthread.so.0...Reading symbols from /usr/lib/debug/lib64/libpthread-2.12.so.debug...done.
[Thread debugging using libthread_db enabled]
done.
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libnsl.so.1...Reading symbols from /usr/lib/debug/lib64/libnsl-2.12.so.debug...done.
done.
Loaded symbols for /lib64/libnsl.so.1
Reading symbols from /lib64/libc.so.6...Reading symbols from /usr/lib/debug/lib64/libc-2.12.so.debug...done.
done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...Reading symbols from /usr/lib/debug/lib64/ld-2.12.so.debug...done.
done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /usr/lib64/libnuma.so.1...Reading symbols from /usr/lib/debug/usr/lib64/libnuma.so.1.debug...done.
done.
Loaded symbols for /usr/lib64/libnuma.so.1
Reading symbols from /lib64/libnss_files.so.2...Reading symbols from /usr/lib/debug/lib64/libnss_files-2.12.so.debug...done.
done.
Loaded symbols for /lib64/libnss_files.so.2
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libnque12.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libnque12.so
Reading symbols from /u01/app/oracle/product/12.1.0/dbhome_1/lib/libshpkavx12.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/12.1.0/dbhome_1/lib/libshpkavx12.so
warning: no loadable sections found in added symbol-file system-supplied DSO at 0x7ffc971db000
0x000000390c00e7c0 in __read_nocancel () at ../sysdeps/unix/syscall-template.S:82
82 T_PSEUDO (SYSCALL_SYMBOL, SYSCALL_NAME, SYSCALL_NARGS)
(gdb)
81 LINESTEXT / UTF-8
and I will set the breakpoint for plsql_run function
(gdb) b plsql_run
Breakpoint 1 at 0xce1c090
(gdb) info breakpoint
Num Type Disp Enb Address What
1 breakpoint keep y 0x000000000ce1c090 <plsql_run>
(gdb) command 1
Type commands for breakpoint(s) 1, one per line.
End with a line saying just "end".
>c
>end
(gdb) info b
Num Type Disp Enb Address What
1 breakpoint keep y 0x000000000ce1c090 <plsql_run>
c
(gdb) set pagination off
(gdb) c
Continuing.
18 LINESTEXT / UTF-8
In the HR session, I will use my function on the table "EMPLOYEES".
SQL> select by2(salary) from employees;
(...output removed...)
107 rows selected.
SQL>
6 LINESTEXT / UTF-8
In the gdb we can see, that the breakpoint was hit 107 times – this is related to the number of rows in the table.
And what will happen if we add "PRAGMA UDF" syntax to our little function?
SQL> ed
Wrote file afiedt.buf
1 create or replace
2 function by2(x number) return number is
3 pragma udf;
4 begin
5 return x*2;
6* end;
SQL> /
Function created.
SQL> select by2(salary) from employees;
(...output removed...)
107 rows selected.
SQL>
19 LINESTEXT / UTF-8
The gdb shows a funny thing:
(gdb) info b
Num Type Disp Enb Address What
2 breakpoint keep y 0x000000000ce1c090 <plsql_run>
breakpoint already hit 9 times
5 LINESTEXT / UTF-8
So why, the function was called only 9 times?
The tkprof shows something that can be interesting:
select by2(salary)
from
employees
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 9 0.00 0.00 0 15 0 107
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.01 0 15 0 107
13 LINESTEXT / UTF-8
There was 9 fetches! Is it possible that the function was called once for each fetch? We can make sure by changing the arraysize in SQL*Plus
SQL> set arraysize 66
SQL> select by2(salary) from employees;
(...output removed...)
107 rows selected.
SQL>
7 LINESTEXT / UTF-8
The gdb shows three plsql_run executions:
(gdb) info b
Num Type Disp Enb Address What
3 breakpoint keep y 0x000000000ce1c090 <plsql_run>
breakpoint already hit 3 times
c
6 LINESTEXT / UTF-8
And the tkprof also:
select by2(salary)
from
employees
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.00 0 9 0 107
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 9 0 107
13 LINESTEXT / UTF-8
So the fetch size can have actual impact on PRAGMA UDF functionality.
Let’s see what will happen from inside of the PL/SQL – without throwing the results to the client:
SQL> ed
Wrote file afiedt.buf
1 declare
2 type t_nums is table of number index by pls_integer;
3 v_nums t_nums;
4 begin
5 select by2(salary) bulk collect into v_nums
6 from employees;
7* end;
SQL> /
PL/SQL procedure successfully completed.
14 LINESTEXT / UTF-8
gdb shows two plsql_run hits – one for anonymous block and one for the "BY2″ function.