Converting REF CURSOR to PIPE for Performance in PHP OCI8 and PDO_OCI
By cj on Nov 03, 2008
REF CURSORs are common in Oracle's stored procedural language PL/SQL. They let you pass around a pointer to a set of query results.
However in PHP, PDO_OCI doesn't yet allow fetching from them. And fetching from REF CURSORS in OCI8 is not as fast as doing a normal query. This is because of two architectural decisions: OCI8 doesn't use the preferred Oracle style of "array fetching". Instead it uses "prefetching", but prefetching from REF CURSORS isn't supported by Oracle for various reasons (including that array fetching is available and recommended....)
One workaround, when you can't rewrite the PL/SQL code to do a normal query, is to write a wrapper function that pipes the output.
For this example, the "fixed" procedure that you can't change is:
create or replace procedure myproc(p1 out sys_refcursor) as
open p1 for select last_name from employees;
The wrapper function follows the performance tip in 12-14 of Tuning PL/SQL Applications for Performance and uses a BULK COLLECT:
create or replace package myplmap as
type outtype is record ( -- structure of the ref cursor in myproc
type outtype_set is table of outtype;
function maprctopl return outtype_set pipelined;
create or replace package body myplmap as
function maprctopl return outtype_set pipelined is
rlim pls_integer := 20; -- fetch batches of 20 rows at a time
myproc(p_rc); -- call the original procedure
fetch p_rc bulk collect into outrow limit rlim;
exit when outrow.count = 0;
for i in 1 .. outrow.count loop
pipe row (outrow(i));
The PHP OCI8 code to query the pipelined function is:
$c = oci_connect('hr', 'hrpwd', '//localhost/XE');
$s = oci_parse($c, "select * from table(myplmap.maprctopl())");
You could use this query in PDO_OCI too.
I found it doubled the performance of smallish tests with a local database (from small time to even smaller time). The change is more dramatic from a distant, remote database. With a query returning a large number of rows it dropped the runtime to 35 seconds from about 24 minutes.
This tip will appear in the next edition of the Underground PHP and Oracle Manual.
Update: Prefetching from REF CURSORS is supported in Oracle 11gR2. See Oracle Database 11gR2 Enhancements for PHP