Help: Hundreds of connections PHP->Oracle

[Keith E. Sauvant]

Hi,

we got a strange problem connecting to an Oracle database onsite with
our PHP application: During CSV import job handled by our application,
PHP opens up to some hundred connections to the database. It then
eventually fails to connect to the database due to the high number of
open connections.

We are not able to reproduce this behaviour on our internal
installations, we get up to 30 connections until the job is finished.

The environment:
SuSe/Novell SLES9
Oracle 10.2.0
PHP 4.3.11
OCI8 1.274 (PEAR OCI8 1.1.1 beta)
Zend Optimizer 2.6.0

Does it make any difference if oracle is installed locally or on another
machine? Is it relevant if we connect to the database using a
service_name or a SID?

Any comments, hints, help?

Best regards from Aachen, Germany
Keith Sauvant

[jonathan.beckett]

I've seen something about this in the past but can't remember exactly
why it happened (Oracle not closing connections).

It was something to do with Auto Commit - the behaviour changed when
you switched it on or off... I don't have any code examples to hand
though.

[Mladen Gogala]

On Wed, 15 Feb 2006 15:31:27 +0100, Keith E. Sauvant wrote:

Does it make any difference if oracle is installed locally or on another
machine? Is it relevant if we connect to the database using a
service_name or a SID?

Any comments, hints, help?

You are probably using persistent connections?

--
http://www.mgogala.com

[Mladen Gogala]

On Wed, 15 Feb 2006 07:44:19 -0800, jonathan.beckett wrote:

I've seen something about this in the past but can't remember exactly
why it happened (Oracle not closing connections).

It's persistent connections problem. I believe that this was resolved in
5.1.2, not before. The problem is that whenever one uses oci_pconnect,
httpd process opens session. For some reason, when a new HTTP request
is issued, new httpd process is created and logged into the database.
When the number of httpd threads is high enough, "sessions" parameter
is exhausted and nobody can connect any more. There are nuances, I believe
that the problem only manifests itself with shared servers or something
like that. If you need it for the commercial use, I wholeheartedly
recommend Zend Core for Oracle. It's beautiful and most of the OCI bugs
are fixed.

--
http://www.mgogala.com

[Keith E. Sauvant]

Mladen Gogala schrieb:

On Wed, 15 Feb 2006 15:31:27 +0100, Keith E. Sauvant wrote:

Does it make any difference if oracle is installed locally or on
another machine? Is it relevant if we connect to the database using
a service_name or a SID?

Any comments, hints, help?
You are probably using persistent connections?

Yes, we do. Means: we were ;-) I tried without persistent connections
yesterday and it had far more comprehensible behaviour. Still waiting
for the customer test results.
It's persistent connections problem. I believe that this was resolved
in 5.1.2, not before. The problem is that whenever one uses
oci_pconnect, httpd process opens session. For some reason, when a
new HTTP request is issued, new httpd process is created and logged
into the database.
But: In our case it is ONE PROCESS that starts opening connections. We
start a script that runs for some hours. It runs fine for one hour and
then it starts opening additional connections to the database. Strange
behaviour.
If you need it for the commercial use, I wholeheartedly recommend
Zend Core for Oracle. It's beautiful and most of the OCI bugs are
fixed.

I like the idea of using Zend Core for Oracle. But: PHP5 is still not
certified for some of our customers.

We use the latest PECL OCI8-Extension. That should be exactly the one
Zend Core uses. Am I right?

Best regards
Keith

[Mladen Gogala]

On Thu, 16 Feb 2006 09:39:11 +0100, Keith E. Sauvant wrote:

We use the latest PECL OCI8-Extension. That should be exactly the one
Zend Core uses. Am I right?

I'm not sure about that. I believe it is a separate branch.

--
http://www.mgogala.com

[Andy Hassall]

On Thu, 16 Feb 2006 11:44:03 GMT, Mladen Gogala <go****@sbcglobal.net> wrote:

On Thu, 16 Feb 2006 09:39:11 +0100, Keith E. Sauvant wrote:

We use the latest PECL OCI8-Extension. That should be exactly the one
Zend Core uses. Am I right?

I'm not sure about that. I believe it is a separate branch.

The OCI8 extension bundled with Zend Core, the one from PECL and the one in
PHP 5.1.2 are all the same branch.

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[boozkachu]

I would use twisted and cherrypy instead. You wont need nearly as many
connections to oracle.

[Mladen Gogala]

On Fri, 17 Feb 2006 15:34:29 -0800, boozkachu wrote:

I would use twisted and cherrypy instead.

I fail to see how could bakery products help him. On the other
hand, blessed are the cheesemakers, so why not try with cherry
pies? Always look on the bright side of life. For life is quite absurd,
and death's the final word, you must always face the curtain with a bow.
Forget about your sin, give the audience a grin, enjoy it's the last
chance of the hour, so always look on the bright side ....

--
http://www.mgogala.com

[Christopher.Jones]

"Keith E. Sauvant" <ne********@spamgourmet.com> writes:

I like the idea of using Zend Core for Oracle. But: PHP5 is still not
certified for some of our customers.
Coming late to the party but FWIW, the re-factored OCI8 driver (aka
OCI8 1.1 onwards) was designed to be usable with PHP 4.
We use the latest PECL OCI8-Extension. That should be exactly the one
Zend Core uses. Am I right?

ZCO picks up the latest PECL build. The release schedules are not
completely syncronized but there was a new PECL OCI8 1.2 bundle on 16
March at http://pecl.php.net/package/oci8 that matches ZCO 1.3.1. (It
will take a short while for the pre-built Windows stack on
http://pecl4win.php.net/ext.php/php_oci8.dll to complete builds of
1.2.)

Another advantage of the re-factored OCI8 driver (aka OCI8 1.1
onwards) is that the the oci_close/ocilogoff function is no-longer a
no-op. You can now force a connection to close. This may be useful
in scripts that only access Oracle for a short time relative to other
PHP processing because resources can be freed for other Oracle users.

Chris