Oracle 12c: PRAGMA UDF – the truth


29.10.2015
by Kamil Stawiarski

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

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.

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) 

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.

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> 

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>

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

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

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>

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

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

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.

gdb shows two plsql_run hits – one for anonymous block and one for the "BY2″ function.


Contact us

Database Whisperers sp. z o. o. sp. k.
al. Jerozolimskie 200, 3rd floor, room 342
02-486 Warszawa
NIP: 5272744987
REGON:362524978
+48 508 943 051
+48 661 966 009
info@ora-600.pl

Newsletter Sign up to be updated