Converting REF CURSOR to PIPE for Performance in PHP OCI8 and PDO_OCI

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
begin
open p1 for select last_name from employees;
end;
/
show errors

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
last_name varchar2(25)
);
type outtype_set is table of outtype;
function maprctopl return outtype_set pipelined;
end;
/
show errors

create or replace package body myplmap as
function maprctopl return outtype_set pipelined is
outrow outtype_set;
p_rc sys_refcursor;
rlim pls_integer := 20; -- fetch batches of 20 rows at a time
begin
myproc(p_rc); -- call the original procedure
loop
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));
end loop;
end loop;
end maprctopl;
end myplmap;
/
show errors

The PHP OCI8 code to query the pipelined function is:


<?php

$c = oci_connect('hr', 'hrpwd', '//localhost/XE');

$s = oci_parse($c, "select * from table(myplmap.maprctopl())");
oci_execute($s);
oci_fetch_all($s, $res);
var_dump($res);

?>

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

Comments:

Hello Chris, interesting info thanks for all, the only question i had is regard the row limit fact you place in your code. does it constraint to just fetch 20 rows of the cursor or it will fetch all of the rows or to do so is necessary to excecute the proc again and retrieve the rest of them?

Posted by Diego Avila on December 05, 2008 at 12:12 AM PST #

All rows are fetched. The outer loop fetches all rows in batches. I've arbitrarily set the batch size to 20.

Posted by Chris Jones on December 06, 2008 at 01:53 AM PST #

Post a Comment:
Comments are closed for this entry.
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today