Home » Database » DB » Bug #3241

[2005-01-19 12:26 UTC] dabl00d at msn dot com

Description:
------------
Using:
PEAR::DB 1.3.6 (latest today)
DATABASE: ORACLE 8.1.7 and 9.0.2
PHP SERVER: SOLARIS 5.6, APACHE 1.3.28, PHP 4.3.2 compiled with-oci8 & with-oracle OCI8 LIBS (8.1.7 and 9.0.2)
ARCHITECTURE: 2 servers (1 for oracle, 1 for PHP) Ping time from one to other : 64 bytes from x.x.x.x (x.x.x.x): icmp_seq=5. time=71. ms
CONTEXT: operational "pro" application (50.000 intranet users)

With a query returning more than 800 rows (3 columns, only text data < 150chars, for multilanguages purpose), oracle execution < 0.02s. PHP execute is OK but when using getAll() function, it makes more than 30s in order to return all rows (3s when using 2 servers which are on the same network).

I found where all the time is taken and it seams that all of 30s were in the oci8.php "fetchInto() function. To be more precise, it's in the OCIFetchInto() php function.

I see that the ocisetprefetch() php function is used to change the "results prefetch size" but it's only for a query statement (must be changed for each new query). After making some tests (when using '500' or '1000' in place of '1' for the prefetch size), the getAll() function was taking only 1s !

There is nothing now in the PEAR::DB package which could permit to use this function. I know i'm talking about Oracle but maybe there are others RDBMs which accept same parameters ? I know too there are other 'context' where this set (500 or 1000) isn't a good solution. At the same time I think that not to be able to modify this parameter without modifying the class code is not either a very good solution !

Moreover, we use this method in many applications in order to get all application's text labels which correspond to the user's language. Because of the lack of PHP to have a "per apache process" where we could store this data from application start to application stop, we must do this at each http query ! Does anyone has another solution ? (for information, labels could be changed when application runs.)

Reproduce code:
---------------
Here is our actual oci8.php PEAR::DB simpleQuery() source. Only modification is ++(161) : "$resPrefetch = ocisetprefetch($result, 1000);"

...
$result = @OCIParse($this->connection, $query);
if (!$result) {
return $this->oci8RaiseError();
}
$resPrefetch = ocisetprefetch($result, 1000);
if ($this->autoCommit) {
$success = @OCIExecute
...

Expected result:
----------------
The "$resPrefetch = ocisetprefetch($result, 1000);" code line would be integrated in the oci8.php/PEAR::DB package with a variable which could be set and used from the PEAR::DB object (as others parameters like autoCommit, ...).

Actual result:
--------------
Really a big response time difference.

Many thanks in advance !

[2005-01-25 16:46 UTC] dabl00d at msn dot com

Just giving updating Version & Os information.