My customer has a problem because web page that is done in PHP 5.2.x and uses Oracle OCI8 driver, is performing very very slow .... At first we tested sql statements and explain plan is looking ok ...
But then we ran the same sql using Toad .... and the performance was 1:1000 ..... What took PHP 5minutes, took Toad 5 seconds .... Database has over 7mil records ... and takes 72Gb on disk ...
Can anyone explain why ? Is there any known problem with OCI driver that i'm not aware of ?
We run IIS on Windows 2008 64bit Server .... Oracle is 10.1.0.2.0 ... and table on which sql statement is executed contains CLOB columns .... and yes we search inside CLOBs using INSTR ..... but still why so big performance difference ??
What is also wierd is that sometimes very simple sql statement takes ages, but the complex one is fast .....
I have attached our DB setting below, if someone could take a look , if there is someting that is not ok .... or should be increased .... If yes, then please describe how to perform ALTER of some parameter that will efect database(tablespace if so) and not just current session. ..
Have you ever tried comparing performance of a PHP OCI application vs. Shadowfax, the racing horse? Comparing a PHP application with a GUI tool makes no sense whatsoever. As for the application tuning, I can only direct you to several worthy manuscripts which will teach you the proper process. Here are the books that you should read, in that order:
Optimizing Oracle Performance by Cary Millsap and Jeffrey Holt
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions by Thomas Kyte
Effective Oracle by Design by Thomas Kyte
Troubleshooting Oracle Performance by Christian Antognini
Oracle Insights: Tales of the Oak Table
Citizen In Space by Robert Sheckley
The Hitchhiker's Guide to the Galaxy by Douglas Adams
The last two books are a bit off topic but will teach you how to think out of the box. There are, of course, many more books. I can wholeheartedly recommend authors like K. Gopalakrishnan, Gaja Vaidayanatha, Jonathan Lewis (a bit hard to read but well worth it) and Tim Gorman. In other words, tuning an application is a process which requires a fairly extensive knowledge.
What monitoring capabilities have you built into your application (this is a hint) to identify bottlenecks?
Check AWR reports to see how efficient your application is.
Look at some of the tips in the Underground PHP & Oracle Manual (and other fine PHP books like Mladen's http://www.amazon.com/Easy-Oracle-PHP-Create-Dynamic/dp/0976157306) and make sure all the basics (persistent connections, binding, prefetching etc) are being done.