Testing new OCI8 (1.3.0 beta) for PHP with DRCP Support

I've seen Christopher Jones announcement on availability of beta OCI8 extension for PHP with support for DRCP. I've decided to give it a try. On my test machine running OEL5 I've installed latest PHP 5.4.2 and then replaced OCI8 sources with new OCI8 1.3.0 beta release. I've configured PHP:
./configure --with-oci8=$ORACLE_HOME --with-apxs2=/usr/sbin/apxs --with-config-file-path=/etc/httpd/conf --enable-sigchild --without-sqlite --without-mysql
compiled it and installed. Everything went fine, and on sample page I've displayed phpinfo():

I've changed php.ini to enable Database Resident Connection Pooling:

oci8.connection_class="TESTPOOL"

and tested that it is used in PHP.
Then I've decided to test and compare performance of different methods of connection to Oracle from PHP. I've created simple PHP page, that connects to Oracle, execute simple query that returns one row, and inserts one row to another table. I've prepared test script for Apache JMeter against this page.
Test specification:

• Run on single processor machine
• Oracle and Apache+PHP run in VMware
• DRCP setup to have max 4 processes (3 processes effectively used, 4^th for authentication only)
• 6 simultaneous users opening the same web page 200 times
• 4 versions of PHP script were used:
  

1. oci_connect used to connect to Oracle without using DRCP (Database Resident Connection Pooling)– in this case new connection is created for every request from web browser. Connection is then closed at the end of script
   
2. oci_pconnect used to connect to Oracle without using DRCP – in this case new connection is created for every apache process that is responding to request from web browser. Connection is then closed at the end of script
   
3. oci_connect used to connect to Oracle with using DRCP (Database Resident Connection Pooling)– in this case for every request connection is obtained from pool and given back to pool at the end of script, seems that in this case only process is reused (PURITY=NEW), while session is not (see my observations when using SQL*Plus )
   
4. oci_pconnect used to connect to Oracle with using DRCP (Database Resident Connection Pooling)– in this case for every request connection is obtained from pool and given back to pool at the end of script. But in this case full potential of DRCP is used (PURITY=SELF)

Here are the results:

Version	Description	# Requests	Average	Median	Standard Deviation	Throughput
1	oci_connect without DRCP	1200	989	875	828	359
2	oci_pconnect without DRCP	1200	123	94	187	2505
3	oci_connect with DRCP	1200	117	94	115	2907
4	oci_pconnect with DRCP	1200	82	78	30	4060

I must say that I'm positively impressed by performance achieved by version 3 where oci_pconnect with DRCP was used. I expected that performance for version 2,3,4 will be very similar. But I've observed 60% increase in Throughput and 33% shorter response times when comparing version 2 and 4 (oci_pconnect with and without DRCP). I've changed one setting for my test in JMeter: I've turned on Keep-Alive for http transfer and here are the results:

Version	Description	# Requests	Average	Median	Standard Deviation	Throughput
1	oci_connect without DRCP						Not tested
2	oci_pconnect without DRCP	1200	84	63	116	4077
3	oci_connect with DRCP	1200	104	94	45	3265
4	oci_pconnect with DRCP	1200	78	78	23	4396

In this case I've observed less differences on Throughput and Response times between version 2 and 4. This is because with Keep-Alive causes that the same apache processes are used for requests coming from the same web browser. As connection is kept open, reduces time to establish connections between clients (web browsers) to apache server. Thats why we can see higher throughput. Additionally for oci_pconnect without DRCP less amount of sessions are created. Without Keep-Alive apache opened much more sessions, as it had to create more processes and every process had to connect to Oracle. Lets summarize my observations:

1. oci_pconnect with DRCP works stable in both cases (with and without Keep-Alive) giving maximun throughput and very low standard deviation,
2. oci_pconnect with DRCP has high standard deviation what means that there are lot of request with response time much higher then average,
3. In both cases connecting to Oracle with use of DRCP performed better than without.

Next time I'll try to go into extremes to see how DRCP behaves.
Cheers, Paweł

I've got spammed (in comments)

Due to quite massive spam I'm receiving today in comments I had to turn on word verification for comment posting. Hope it will protect me from spam for future. And sorry for inconvenience when commenting,

Cheers, Paweł

Using Named Parameters within SQL

One of the Oracle 11g new feature that I like is ability to use Named Parameter syntax in functions calls inside SQL. For example it is possible to run such query:

select DBMS_METADATA.get_xml(
          object_type     => object_type,
          name            => object_name) 
     from user_objects
where object_type in ('TABLE', 'VIEW');

It was not possible in previous Oracle versions (before 11g). It was only possible to specify parameters by position:

select DBMS_METADATA.get_xml(object_type, object_name) from user_objects
where object_type in ('TABLE', 'VIEW');

This is great feature as now SQL is more compatible with PL/SQL. And Named Parameters syntax is my favorite method of calling functions, as it gives me direct knowledge what value is assigned to parameters. Specifying parameters by position requires to remember sequence of parameters in function definition. Additionally named parameters syntax allows programmer to skip any of parameter that has default value, or to change order of parameters in function call. It is also possible to mix those 2 notations in single statement:

select DBMS_METADATA.get_xml(object_type, name => object_name) from user_objects
where object_type in ('TABLE', 'VIEW');

Hope you like it

Cheers, Paweł

Comments in SQL*Plus

This Warning from Eddie Awad remind me of problem with comments I've had some time ago. I've prepared an script that was intended to run in sqlplus. Then I've extensively tested it, and as a last step, I've commented it. And ... did not test again. When this script was run on production caused errors. The problem was in the way that sqlplus interpret comments.
Example:

SQL> select * from dual;

D
-
X

runs fine, while this:

SQL> select * from dual; -- some comments

stops at second line waiting for input:

SQL> select * from dual; -- some comment
  2 

It was really unpleasant experience.

Cheers, Paweł