Reducing Oracle LOB Memory Use in PHP, or Paul's Lesson Applied to Oracle
- by christopher.jones
Paul Reinheimer's PHP memory pro tip shows how re-assigning a
value to a variable doesn't release the original value until the new
data is ready. With large data lengths, this unnecessarily increases
the peak memory usage of the application.
In Oracle you might come across this situation when dealing with
LOBS. Here's an example that selects an entire LOB into PHP's memory.
I see this being done all the time, not that that is an excuse to code
in this style. The alternative is to remove OCI_RETURN_LOBS to
return a LOB locator which can be accessed chunkwise with LOB->read().
In this memory usage example, I threw some CLOB rows into a table.
Each CLOB was about 1.5M. The fetching code looked like:
$s = oci_parse ($c, 'SELECT CLOBDATA FROM CTAB');
oci_execute($s);
echo "Start Current :" . memory_get_usage() . "\n";
echo "Start Peak : " .memory_get_peak_usage() . "\n";
while(($r = oci_fetch_array($s, OCI_RETURN_LOBS)) !== false) {
echo "Current :" . memory_get_usage() . "\n";
echo "Peak : " . memory_get_peak_usage() . "\n";
// var_dump(substr($r['CLOBDATA'],0,10)); // do something with the LOB
// unset($r);
}
echo "End Current :" . memory_get_usage() . "\n";
echo "End Peak : " . memory_get_peak_usage() . "\n";
Without "unset" in loop, $r retains the current data value while
new data is fetched:
Start Current : 345300
Start Peak : 353676
Current : 1908092
Peak : 2958720
Current : 1908092
Peak : 4520972
End Current : 345668
End Peak : 4520972
When I uncommented the "unset" line in the loop, PHP's peak memory
usage is much lower:
Start Current : 345376
Start Peak : 353676
Current : 1908168
Peak : 2958796
Current : 1908168
Peak : 2959108
End Current : 345744
End Peak : 2959108
Even if you are using LOB->read(), unsetting variables in this
manner will reduce the PHP program's peak memory usage.
With LOBS in Oracle DB there is also DB memory use to consider.
Using LOB->free()
is worthwhile for locators. Importantly, the OCI8 1.4.1
extension (from PECL
or included in PHP 5.3.2) has a LOB fix to free up Oracle's locators
earlier. For long running scripts using lots of LOBS, upgrading to
OCI8 1.4.1 is recommended.
