Due to the process-based design of PHP and the use of stateless requests, there isn't a connection pool API in the OCI8 extension. What you should use instead is persistent connections with the oci_pconnect()
call. This retains connections from one HTTP request to be usable by the next, so it is much faster.
(Internal to the OCI8 implementation, persistent connections do use Oracle's Call Interface session pool API with a fixed poolsize of only one connection. I.e. each PHP connection has its own pool. This is useful since the session pool is the technology that allows use of DRCP (see other comments), and also Oracle documentation on high availability recommends using session pools, e.g. for FAN events).
Make sure not to oversize the number of PHP processes that run. This will minimize the number of DB connections needed, allowing more reuse. It will also reduce the amount of memory required on each DB server to handle the connections, since they will remain open. Only if the DB servers run out of memory, would you then look at using DRCP pooling.
Reduce Apache process restarts, remove network firewall connection timeouts, and remove DB resource limits so idle connections are not killed. You want those 'persistent connections' to be persistent so they are immediately available for use when your app calls oci_pconnect()
. Otherwise OCI8 will have to (internally) recreate connections unnecessarily.
Next, you need to reduce 'round-trips' between PHP and the database for the application, since over a slow network they really have an impact on performance. Move as much work into the DB as possible, e.g with PL/SQL.
If you run statements like ALTER SESSION
after each oci_pconnect()
call, move them into a LOGON trigger. Or at least wrap them with a PL/SQL block:
begin
execute immediate
'alter session set nls_date_format = ''YYYY-MM-DD'' nls_language = AMERICAN';
-- other SQL statements could be put here
end;
This block only takes one round-trip if executed from PHP. If you did the same in a LOGON trigger it would take zero round-trips.
For normal statement execution, see if you can similarly put calls into a single anonymous PL/SQL block and use a single oci_parse()
call.
Next, tune queries that fetch multiple rows by adjusting prefetch size. Again, this can help reduce round-trips.
If do you data uploads, consider using oci_bind_array_by_name()
, or even calling out to a Python (with the cx_Oracle extension) and using executemany()
, since these (again) reduce round-trips.
These are basic tips. You might find others in the manual examples or The Underground PHP and Oracle Manual. Or you might want to consider an alternative architecture, perhaps something that batches up requests and executes them on the "remote" database, and then sends all the results back.
Not related to the performance of the app, but since you have 'remote' databases, you may want to make use of Oracle Net features to detect dead network connections and avoid connect 'hangs' waiting for TCP timeouts. Look at creating a sqlnet.ora file where you run your PHP app and using options like SQLNET.OUTBOUND_CONNECT_TIMEOUT
. If you can't stop firewalls from killing otherwise idle connections, you can use ENABLE=BROKEN
in a connect string.