vsbabu.org

PHP & Oracle performance

PHP opens a connection every time a page that has queries is loaded. There is no connection pooling. With Oracle, when lot of people hit the site, this is a serious drawback. Usually, creating an Oracle connection takes up hell a lot more resources than actually executing normal queries (eg: every single connection takes up 5MB in NT4 for Oracle 8i 816. Thanks to memory management of either NT4 or Oracle, 500 people come to the site at the same time, it runs out of RAM).

I could think of the following options.

• If you use OCI8 functions in PHP, then use OCIPlogon, which is a persistent login to Oracle.
• SQLRelay is a middle layer connection pooler which might be useful. I’ve no expertise with this, so can’t comment on its performance. Specifically, this is also aimed at helping solve Oracle’s issues.
• If your PHP runs on Win2k, use ODBC instead of native Oracle libraries. In ODBC you can setup connection pooling. Please note that my experience is that Oracle’s own ODBC drivers are worse than Microsoft's Oracle drivers. Getting a PL/SQL procedure to work over ODBC might expose interesting and random problems.
• If it is a read-more, write-less, MySQL might be a better choice than Oracle. It is blazingly fast. Personally, I prefer PostgreSQL for feature set.
  Why switch DB? Because Oracle’s internet licensing is very expensive. If you opt for per-user license, be aware that Oracle considers each individual site visitor to be one user. It is not as if you share one Oracle user. If you want to make a web front-end for existing Oracle OLTP applications, I would seriously consider making a MySQL front end that synchs data back and forth with Oracle periodically.
• Switch from PHP. Zope is a simply superb OO application server that comes with excellent database interaction modules.

1. Hi ,
   Im getting the following error time and again
   'Warning: OCISessionBegin: ORA-24327: need explicit attach before authenticating a user'.
   PHP opens connections using the data abstraction call db_sql and also OCILogon.
   COuld anyone shed some light on this?
   Regards,
   KK.

   Posted by: kk on February 20, 2003 09:55 PM

2. Correct me if I'm wrong,
   OCIPlogon won't scale as well as a connection pool would.
   OCIPLogon will maintain a connection open for ONE same process but it will not be possible to share a connection between various process, wich would mean from an end user point of view, between various client connected.

   Would it be possible to consider the use of a Java connection pool through the php-java extension ?
   

   Posted by: luro on March 28, 2003 08:40 AM

3. Yes, what luro has said is right about OCIPlogon. It doesn't share connection between various processes - for example if you've (and usually you do) multiple Apache instances running.

   I have no experience with Java connection pooling used with PHP.

   Posted by: Babu on March 28, 2003 08:46 AM

4. excuse me, I am the one of new programer .I am in charge of managing HRMS.
   now,I have some requests so that I should to learning the programs of connecting DB and show data on the web.My company db enivornment is Oracle DB. But now,I saw this message that seem show it's not suit to learn PHP?
   could everyone tell me,should I use PHP to complete requests from my users?

   Posted by: TINALEE on January 7, 2004 08:51 AM

5. I am trying to use OCIPlogon and it is working fine, but I am not sure how to terminate the Oracle processes. I am issuing an OCILogoff, but it does not kill the database process/connection as I can still see it in the database being inactive. Do you know the best way to go about this?

   Posted by: Andrew Cooper on March 16, 2004 12:33 PM

6. quote: "PHP opens a connection every time a page that has queries is loaded"

   Correct me if I'm wrong, but that's true only if you do not make use of $_SESSION variables and/or do not pass the handle to your DB-class by reference.

   Posted by: patrikG on May 10, 2004 03:37 PM