External C procedures – how to setup and performance boost you can expect

In this post I’ll explain what you need to do to setup external C procedure call with Oracle 18c.

I’ll also show for a CPU intensive task, what performance boost you might expect.

For those of you who remember how difficult it was to configure LISTENER and TNSNAMES to work with C external procedures, will appreciate how easy it is to do the same task now.

To setup external C procedures, you need to ssh to the database server and navigate into the following directory:

cd $ORACLE_HOME/hs/admin/

open the extproc.ora file (e.g. with vi / nano or whatever text editor you have on server):

vi extproc.ora

and finally add a line at the end of file:

SET EXTPROC_DLLS=ANY

There are also other options you might set based on your security settings such as:

SET EXTPROC_DLLS=ONLY:DLL

(for maximum security) where only the specified DLL can be loaded.

To verify the extproc program is available and executable (especially if you receive ORA‐28575: unable to open RPC connection to external procedure agent error), execute the following:

cd $ORACLE_HOME/bin

./extproc 

Oracle Corporation --- 2020-02-20 17:33:15.793876000

Heterogeneous Agent Release 18.0.0.0.0 - Production 

To create a real C external procedure, create a file cpuIntensive.c and insert the following code:

#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <math.h>

int cpuIntensive()
{

    long f,j;
    long n = 1000000000;
    double x;

    clock_t t;
    t = clock();

    for(f = 1;f <= n; f++)
    {
        x = pow(round(f % 9 + sqrt(f)), 2);
    }

    t = clock() - t;
    double time_taken = ((double)t)/CLOCKS_PER_SEC;
}

Now you need to generate shared library you need to have gcc installed (it’s usually already installed in case of Linus server), and execute the following:

gcc -m64 -shared -fPIC -lm -o cpuIntensive.so cpuIntensive.c

Next you need to create directory where your Oracle database have full read/writes permissions, e.g.

mkdir /u01/proc

and copy generated shared object into the created directory:

cp cpuIntensive.so /u01/proc

Your DBA need to grant the following permission (I’m using a user SCOTT which is part of the sample schema):

GRANT CREATE LIBRARY TO SCOTT;

Log in as a SCOTT user and execute the following command to create a library:

CREATE LIBRARY cpuintensive_lib is '/u01/proc/cpuIntensive.so';

Final step is to create a PL/SQL wrapper to be able to call your external C procedure from the PL/SQL:

create or replace procedure cpuintensive_external
as external
   name "cpuIntensive"
   library cpuintensive_lib
   language C;

On my laptop when I call the wrapper:

exec cpuintensive_external;

it completes in 16 sec.

By tweaking gcc option flags I can get even a better result.

Now you can create stored procedure in PL/SQL language to check how it compares with external C procedures.

create or replace procedure cpu_speed_interpreted
as

x number := 0;

begin 
    for f in 1..1000000000
    loop
        x := power(round(mod(f, 9) + sqrt(f)), 2);
    end loop;
end;

From SQL Developer / TOAD / sql*Plus you can execute stored procedure:

exec cpu_speed_interpreted

783 sec

As you can see, for CPU intensive task PL/SQL is a poor choice.

That’s the main reason why the C external procedures was invented.

Still I don’t want to mislead you to start coding in external C procedures.

Where PL/SQL really shines is in data intensive tasks which is most common use case for your database.

Additionally, although to setup external C procedures all you need to do is to add just one line, the most tedious task is to pass and to receive variables, arrays, collections to and from external procedures.

Debugging is also nightmare, especially if you are getting errors only when many users are calling it.

As of now, the best option is to use Java Stored procedures which will provide almost the same performance as C external procedures for CPU intensive tasks, while debugging, monitoring and data mapping is really simple (or at least much simpler than in C).

Although the field of usage for external C procedures (as well as for Pro*C) is narrowed, there are still some specific cases where you might need it.

---