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

原文地址：https://blogs.oracle.com/opal/entry/converting_ref_cursor_to_pipe

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;

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;

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