When a few seconds are important: improving PHP Oracle connection speed


I read the announcement about Oracle and the Open Invention Network.  When I get back to San Francisco next week I hope to hear more about it from my team who are working faithfully to make Linux better. 

In the meantime here's a PHP performance tip from Krishna Mohan.

Oracle has the concept of a client character set that application data is in.  Oracle will convert data from the character set of the stored data into the client character set when data is fetched and vice versa when data is inserted.  There may be a conversion cost or data loss if the client character set doesn't match the database character set.

The database character set can be chosen when the database is created. You can find what character set a database uses with this query:

    select value
    from nls_database_parameters
    where parameter = 'NLS_CHARACTERSET';

But you knew all that.

What Krishna pointed out was that specifying the client character set name as the optional fourth parameter to oci_pconnect() has a postive effect on performance.  If you let the value default then PHP has to invoke code to determine what the client character set should be. This lookup may involve a potentially expensive environment check.

Test it yourself.  Benchmark a few thousand oci_pconnect calls with and without the character set:

oci_pconnect("hr", "hr", "//localhost/XE", "AL32UTF8");
oci_pconnect("hr", "hr", "//localhost/XE");

I used AL32UTF8 which is the character set of the Oracle XE "Universal" database I have.

Testing with the database and PHP both on my little old machine I was getting figures like 0.6 vs 2.7 seconds for 50,000 pconnect calls in the one script.  I saw some other results that showed only a three-times difference.

This tip is a snippet of information: use it wisely.  The cost may not be anywhere near, say, the cost of writing a query poorly.

Comments:

This was very helpful. As the character set was not explicitly specified, the php code was grabbing the "default" system setting. I could not find the location of this default setting in an ancient fedora server, and my predecessor did not leave any documentation. When I viewed the data with Oracle SQL Developer or Toad, the special characters were present - just not showing up on the web pages. We are recoding the whole php file set with this. The speed enhancement is a bonus as well. This is the last place I would have looked. thanks for the huge snippet.

Posted by Daniel on February 17, 2009 at 11:03 PM PST #

The free book http://www.oracle.com/technology/tech/php/pdf/underground-php-oracle-manual.pdf has this and other useful tips as well.

Posted by Chris Jones on February 18, 2009 at 01:39 AM PST #

Post a Comment:
Comments are closed for this entry.
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today