Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Low performance when reading large LOB

michal.taborskyAug 22 2013 — edited Aug 23 2013

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

Comments

vermio
Answer

The isssue was solved by Oracle Support

Marked as Answer by vermio · Sep 27 2020
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 20 2013
Added on Aug 22 2013
2 comments
2,462 views