Unanswered: PHP page loads forever when trying UPDATE to Oracle db
I have a strange problem connecting to Oracle database with php:
SELECT statements function OK and very fast between my php scripts and Oracle database. But UPDATE satements take forever to run, even a small query updating one row leaves the web browser loading the page for minutes, before the page is ready. Trying to make several update queries in the same php script takes forever, and the website finally gives up. Yet the queries take at least some effect on the database, one UPDATE statement updated 90,000 rows OK to the db, even though the page never became ready in the web browser. In another case a php script performed at least 2000 successful update commands, which were committed one after the other in a loop, but the page loaded forever and finally crashed in web browser.
When I run the same update queries in sqlplus directly on the Oracle server (in ssh session), it works OK and fast. One sqlplus UPDATE query affecting 90,000 rows takes 1 or 2 seconds. Updating 5000 rows with 5000 separate UPDATE commands (each affecting one row only) with sqlplus and a batch file takes minutes but functions OK.
In php I have tried oci_execute with and without OCI_NO_AUTO_COMMIT, and with or without actually committing the query to database. Same problem in all cases, also when doing oci_execute with OCI_NO_AUTO_COMMIT and immediately oci_rollback.
I have php Oracle Database 10g Release 10.2.0.3.0 - 64bit Production, running on SunOS 5.10 = SPARC / Solaris 10.
I am connecting to the database with php 5.3.3-7+squeeze13, which is on a different computer than the Oracle, in the same local network.
which were committed one after the other in a loop
Ouch! That could be part of the problem... committing too often.
finally crashed in web browser
Did you receive a server error back to the web browser, or did you just get a "Page cannot be displayed" kind of error. Understand that your browser will likely timeout on pages that do not respond within 20 seconds or so (depending on your browser and other settings), but the server process still continues to completion.
When I run the same update queries in sqlplus directly on the Oracle server (in ssh session), it works OK and fast
Then perhaps the problem is your network latency between Web Server and Database?
First thing, make sure you are following all the good practices:
Use connection pooling. Oracle does not like connect/disconnect at all. It takes a lot of effort to do this. Connect once, use many.
Bind variables, bind variables, bind variables! If you don't use binds, your app is doomed to fail (performance wise and security wise). And if you don't know what bind variables are, step away from the keyboard.
Secondly, make sure you know exactly what it is that is taking a long time:
Is it the connect time?
Is it the SQL execution?
Is it the returning of data?
Is it the commit?
To analyse the performance of the SQL, you should use SQL Trace and TKPROF. This will give you the info you need regarding SQL performance.