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
- 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 220.127.116.11 and 10.2.0.4). Again, this is
unlikely to affect a simple benchmark script like the one you
- 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
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:
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,