Hello everyone,
we need to read a file of about 70M from from database LOB and send it to clients. Dead simple:
<?php
$id = 'filename';
$c = oci_connect('scott', 'tiger', 'MYCONN');
$sql = "SELECT CONTENT FROM MYTABLE WHERE ID=:1";
$s = oci_parse($c, $sql);
oci_bind_by_name($s, ':1', $id);
$r = oci_execute($s);
$arr = oci_fetch_array($s, OCI_ASSOC);
if (is_object($arr['CONTENT'])) {
while (!$arr['CONTENT']->eof()) {
echo $arr['CONTENT']->read(1000000);
}
}
All works fine, only performance sucks. This script, for the 70 meg file, takes 40 seconds(!) and the transfer speed is below 2MB/s. The app server is connected to the database with gigabit ethernet, tested transfer speed (tested with scp) between app server and database is over 100MB/s, as is to be expected. The database and the app server are both underutilized beefy machines. I tried various chunk sizes for the read method, but the variations in speed are insignificant. I tried SQL*Net tracing on the client, there are no errors reported. There is no special SQL*Net configuration either on the client, nor server.
Anyone has any experience with tuning this? I am sure we can do much better.
Thanks for any hints,
Michal