Reducing Oracle LOB Memory Use in PHP, or Paul's Lesson Applied to Oracle

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.

Comments:

Post a Comment:
Comments are closed for this entry.
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today