2 Replies Latest reply: Aug 23, 2013 4:18 PM by michal.taborsky RSS

    Low performance when reading large LOB

    michal.taborsky

      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

        • 1. Re: Low performance when reading large LOB
          cj

            Hi Michal!

           

          Here are some comments:

           

           

            - For me, between computers on the same floor, an 80M CLOB is

              fetched in 3 seconds when the PHP script doesn't display output.

              Writing the CLOB to a file via shell redirection added another

              second.  My CLOB was created with default parameters.

              I used PHP 5.5 with Oracle 12c client libs & db server.

           

           

           

            - Character set conversion will slow down the transfer.

           

           

           

            - The $arr['CLOBDATA']->read(x) chunk size does impact performance.

              There is an internal upper limit which caps 'x' to 1M.

           

           

           

            - Using Oracle 10g or later client libraries allows the OCI

              OCILobRead2 call to be used by PHP OCI8.

           

           

           

            - Check chapter 12 "Performance Guidelines" of

              http://docs.oracle.com/cd/E16655_01/appdev.121/e17605.pdf

           

           

           

            - Do the DB LOB storage parameters affect performace?  E.g. the

              CHUNK size?  For generic queries in a live system, I've seen

              altering the table and turning on "(CACHE)" help.  I also have a

              note about some the high water mark enqueue once having contention

              (this was fixed in 11.1.0.7 and 10.2.0.4).  Again, this is

              unlikely to affect a simple benchmark script like the one you

              posted.

           

           

           

            - Check the Oracle Net session data unit (SDU) size.  An 8K size

              (the new default in Oracle 11g) is suitable for many applications

              but for LOBs a bigger value might be better.  It should be set the

              same value in both the database server sqlnet.ora and the PHP OCI8

              tnsnames.ora file

          • 2. Re: Low performance when reading large LOB
            michal.taborsky

            Hi Chris and thanks.

             

            You've pointed me in right direction with the last one, actually, because looking at the SDU I encountered this note:

             

            Note:

            Starting with Oracle Database 11g, Oracle Net Services optimized bulk data transfer for components, such as Oracle SecureFiles LOBs and Oracle Data Guard redo transport services. The SDU size limit, as specified in the network parameter files, does not apply to these bulk data transfers.


            I checked and, to my surprise, the LOB was defined as BASICFILE. When I rebuilt it as SECUREFILE, the performance immediately went up and I am seeing transfer rate of about 20MB/s and the script runs 3-4 seconds, which is good enough. Problem solved.


            Thanks for the kick,

            Michal