Speeding up DBMS_OUTPUT in PHP OCI8

Immediately after OOW, Alison and I have got straight into updating the Underground PHP & Oracle Manual. We've had our heads right down. Stay tuned. We've still got a few items on our ToDo list but the new, improved version will be available free on OTN soon at the normal location: http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html.

When reviewing the DBMS_OUTPUT section I tried using a PIPELINED PL/SQL function and found a significant speed improvement over the basic method:

In SQL*Plus, run do.sql to create the function "mydofetch":


create or replace type dorow as table of varchar2(4000);
/
show errors;

create or replace function mydofetch
return dorow pipelined is
line varchar2(4000);
status integer;
begin
loop
dbms_output.get_line(line, status);
exit when status = 1;
pipe row (line);
end loop;
return;
end;
/
show errors;

The file do.php compares the time taken to fetch using dbms_output.get_line() with calling the pipelined function:


<?php

define('COUNT', 100000);

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

// Turn DBMS_OUTPUT on
function setserveroutputon($c)
{
$s = oci_parse($c, "begin dbms_output.enable(null); end;");
oci_execute($s);
}

// Create some output
function createoutput($c)
{
$s = oci_parse($c,
"call dbms_output.put_line('Hello, world! Lots and lots and ... of text')");
for ($i = 0; $i < COUNT; ++$i) {
oci_execute($s);
}
}

// Call dbms_output.get_line()
// Returns an array of DBMS_OUTPUT lines, or false.
function getdbmsoutput_do($c)
{
$s = oci_parse($c, "begin dbms_output.get_line(:ln, :st); end;");
oci_set_prefetch($s, 1);
oci_bind_by_name($s, ":ln", $ln, 100);
oci_bind_by_name($s, ":st", $st, null, SQLT_INT);
$res = false;
while (($succ = oci_execute($s)) && !$st) {
$res[] = $ln; // append each line to the array
}
return $res;
}

// Pipelined method
// Returns an array of DBMS_OUTPUT lines, or false.
function getdbmsoutput_pl($c)
{
$s = oci_parse($c, "select * from table(mydofetch())");
oci_execute($s);
$res = false;
while ($row = oci_fetch_array($s, OCI_ASSOC)) {
$res[] = $row['COLUMN_VALUE']; // append each line to the array
}
return $res;
}

setserveroutputon($c); // Turn output buffering on

// Call dbms_output.get_line()
createoutput($c);
$t = microtime(TRUE);
$output = getdbmsoutput_do($c);
$t = microtime(TRUE) - $t;
echo "Fetching using dbms_output.get_line(): " . round($t,3)." seconds\n";
//var_dump($output);


// Pipelined fetch
createoutput($c);
$t = microtime(TRUE);
$output = getdbmsoutput_pl($c);
$t = microtime(TRUE) - $t;
echo "Fetching from pipelined function: " . round($t,3)." seconds\n";
//var_dump($output);

?>


My results were of the order of:

$ php do.php
Fetching using dbms_output.get_line(): 14.674 seconds
Fetching from pipelined function: 1.162 seconds
$ php do.php
Fetching using dbms_output.get_line(): 14.714 seconds
Fetching from pipelined function: 1.44 seconds
$ php do.php
Fetching using dbms_output.get_line(): 14.385 seconds
Fetching from pipelined function: 1.131 seconds

The pipelined version was significantly faster.

Update: I edited the example and reduced the line size to keep memory small. Since this example fetches via a SQL query, the maximum data size returnable to PHP is 4K anyway. Also Gennady Feldman wanted to point out that oci_fetch_all() can be faster than calling oci_fetch_array() in a loop. The difference is only noticeable for large numbers of rows.

Comments:

Maybe a dumb question as I haven't got into PHP yet, but why not use DBMS_OUTPUT.GET_LINES to fetch a bunch of lines at once ?

Posted by Gary on October 25, 2008 at 05:38 AM PDT #

It's a very fair question. The answer is that is you can do this, calling dbms_output.get_lines() using oci_bind_array_by_name() to bind the multiple lines of output. I've found performance much more variable, depending on the number of lines being fetched in each batch, how many lines in total are returned, and whether results are "streamed" after being fetched or appended to an array. It never seems to be more than marginally better than the PIPELINED solution in the best case. In some cases is much worse than the simple get_line() code.

Posted by Chris Jones on October 28, 2008 at 08:14 AM PDT #

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