Software Engineer Thoughts

I was asked to compare DRCP to SHARED server connections. I've also promised to give some more statistics and comparisons for DRCP and standard connectivity.
First few results to compare to SHARED server as asked in comments to my previous post:

Version	Description	# Requests	Average	Median	Standard Deviation	Throughput
1	oci_connect DEDICATED server						Not tested
2	oci_pconnect DEDICATED server	1200	84	63	116	4077
3	oci_connect SHARED server	1200	280	264	150	1290
4	oci_pconnect SHARED server	1200	82	78	93	3965
5	oci_connect with DRCP	1200	104	94	45	3265
6	oci_pconnect with DRCP	1200	78	78	23	4396

In this case SHARED server is configured to use 4 dispatchers and max process = 1000.


Cheers, Paweł

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ł

This post is continuation of my series of post about Database Resident Connection Pooling.
Let start with session and processes. My test shows that Pooled Servers are shown in v$session view only when there is user connected, that means session is busy. For inactive servers only process in v$process view is reported. To identify this processes we can run query like that:

select * from v$process
where program like '%(L0%)';

If our Connection pooling is started, at least one process will be reported. This is (削除) so called Connection Broker (削除ここまで) process that is authenticating connections and assigning them to another server for processing. According to my tests (削除) Connection Broker (削除ここまで) this process usually have highest number in brackets (ex. L003 if you set up max number of processes to 4). To see busy servers run this query:

select * from v$session
where program like '%(L0%)';

Note that Connection Broker is never reported in v$session view.

In next test I've tested if global variables in packages are stored between connections. My test confirms that packages are always initialized after new connection is taken from pool. That's very good, and is what I was expecting. This is advantage over traditional connection pooling at client side like in Java, where environment is totally shared between consecutive use of the same connection. (This is true when only process if reused, when PURITY=NEW)

My next test was concerning usage of context. I've created sample CONTEXT, initialized it in session and reconnected. I've ensured that session was assigned to the same process (pooled session) and read context. My test also confirmed that context is reinitialized between connections. In traditional connection pooling it will not happen. Also when using PHP and persistent connection you have to take care of clearing global variables and context after acquiring session from pool (or before giving it back to pool). Database Resident Connection Pooling will make programmers life easier, at least on that field. (This is true when only process if reused, when PURITY=NEW)

My last test for today was performance test. Test was run on rather poor desktop machine with single processor.

Testcase	Normal Connection	Pooled Connection	%Improvement
Connect and Disconnect 1000 times	226 sec	154 sec	31%
Connect, Insert one row, Disconnect 1000 times	309 sec	178 sec	42%

This results are very promising. First we see 30% improvement of time to connect to DB. What is more important we can see 40% less time on repeating the same simple statement after establishing each connection. The only explanation is that Pooled connection can reuse already parsed statement. That is good news if we think about scalability.

You may be also interested in Oracle Official White-paper on DRCP.

At the end I would like to thank Christopher Jones as he was involved in DRCP development. I've mailed with him few times last year about problems with PHP-Oracle connectivity and lack of connection pooling.

Cheers, Paweł

In my last post I wrote about unsuccessful attempt to check functionality of DRCP: Database Resident Connection Pooling. Now it's time for success story :) The problem with previous situation was, that Oracle was not fully functional. And that was because I manually set, that i have all proper versions of linux packages, what was not true.
Now I've installed Oracle Enterprise Linux v 5.0 and Oracle 11g over it, with some help of Tim instructions.
I've added entry in TNSNAMES.ORA on linux box:

DB11GPOOL =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.194.111)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = DB11G)(SERVER=POOLED)
 )
 )

And tried to connect to pool

[oracle@localhost admin]$ sqlplus pooltest/pool@db11gpool
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 29 01:44:53 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server

So I've started connection pooling (as SYS at Oracle):

exec DBMS_CONNECTION_POOL.START_POOL();

And tried again:

[oracle@localhost admin]$ sqlplus pooltest/pool@db11gpool
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 29 02:02:37 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select 1 from dual;
     1
----------
     1

This time successful login.
Now I've left this session untouched for some time (more then 300 second - default time-out for pool)

SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2821
Session ID: 125 Serial number: 21

As you can see, session was timed-out by pool, and disconnected.
Ok, lets change parameters for pool to observer behaviour, when max number of session is reached.
Execute it as SYS:

begin
 DBMS_CONNECTION_POOL.ALTER_PARAM ('','MINSIZE','1');
 DBMS_CONNECTION_POOL.ALTER_PARAM ('','MAXSIZE','4');
end;
/

After I set it, I'was able to have 3 (not 4) simultaneous sessions using Connection pool. Whenever I start 4-th or 5-th session it is waiting for connection. You do not get any error, session just hung up. When one of active session is released (closed or timed-out) then one of waiting sessions is being connected. When I was looking for explanation, I found out one that in view V$CPOOL_STATS:

• NUM_OPEN_SERVERS=4 - Total number of busy and free servers in the pool (including authentication servers)
  
• NUM_BUSY_SERVERS=3 - Total number of busy servers in the pool (not including authentication servers)
  
• NUM_AUTH_SERVERS=1 - Number of authentication servers in the pool
  

So, we have to have at least 1 authentication server in our pool. We should be aware of this when we setup small number of MAXSIZE of pool.
When I first read about database connection pooling I was thinking that when you start using all connections in pool, than next attempt to connect will open new normal session. But it will not be good. Mechanism of queues is better, because client will not overload database server. Might be it's not good for clients, as they might wait long time. On the other hand in well written application sessions should be short and closed immediately when no more needed in request.
More tests of DRCP in next posts...
Cheers, Paweł

Finally I've found time and managed to install Oracle 11g. I did it on Fedora Core 5 run in VMWare Server on Windows 2K Pro.
My first attempt was to install it on Fedora Core 6 but I didn't succeed, and even I managed to corrupt VM totally. My second try was to create new VM with FC5. I've applied all available updates, and tried to install Oracle 11g. Oracle gave warning that some packages do not match requirements, but after manually marking check-box it proceeded and installed successfully. And seems to be fully functional. Do not try this for production DB :)
After that I've spend lot of time trying to connect to this DB from my Windows box. I was reconfiguring VM Network setting, FC Network setting many times without any sign of progress. Finally I found out that it can be firewall that is causing problem. It was really silly that I spend so much time to solve so simple problem...
Any way my Oracle 11g is up and running, so I started to test one of th features that are in my interest: DRCP: Database Resident Connection Pooling.
I've enables DRCP:

SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();
PL/SQL procedure successfully completed.

And created sample user for tests:

SQL> create user pooltest identified by pool ;
User created.
SQL> grant connect to pooltest;
Grant succeeded.

I've added entry in TNSNAMES.ORA on Windows box:

DB11GPOOL =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.194.111)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = DB11G)(SERVER=POOLED)
 )
 )

And tried to connect using POOLED server:

c:\Temp>sqlplus pooltest/pool@db11gpool

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Aug 27 22:30:08 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

ERROR:
ORA-56606: DRCP: Client version doesnot support the feature

So, DRCP needs proper client version to work. It's not a good news, as I thought that this will not require any changes at client side :(
I have to make my test on single box, or install Oracle on one more box (VM). Ok, first try from the same box where DB installed:

[oracle@vm111 admin]$ sqlplus pooltest/pool@db11gpool

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Aug 27 22:41:51 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Aborted

Seems that I have to dig little more more to make it working.

Cheers, Paweł

UPDATE: Success story on DRCP in next post.

Oracle 11g was today announced. As an engineer I'm interested in new features of 11g release. I was already writing about this here and here. But this were just my thoughts based on others relations from Oracle Open World 2006.
Now official list of Oracle 11g new features is published. Here is page that contains more technical information on Oracle 11g.
Based on new features list I choose some that are very useful in project that I'm work now on, and I see them as important:

1. Binary XML storage and XML path indexing for schema-less XML documents
   
2. Result caches: improves speed of repeated execution of queries and function calls that access read-only or read-mostly data.
   
3. Database Resident Connection Pooling: enables faster connections to the database for application, that do not provide connection pooling (ex. PHP).
   
4. Faster triggers, including more-efficient invocations of per-row triggers
5. Faster simple SQL operations
6. SecureFiles: ti's new solution for storing large objects (LOBs) and datatypes such as XML
7. Automatic compilation for PL/SQL and Java in the database: new “native” compilation of PL/SQL that do not need C compiler.
   

New features that look interesting, and I want to play with them:

1. Semantic Technologies: native support for Resource Description Framework (RDF) and Web Ontology Language (OWL) standards,
2. Oracle Spatial enhancements: Richer, more-interactive map application development
3. New partitioning capabilities:
   - Partitioning by parent/child references,
   - Partitioning by virtual columns,
   - More composite partitioning options, including range/range, list/range, list/hash, and list/list,
   - Interval partitioning, which allows you to automatically create new partitions based on intervals, such as every month or every day,
   
4. Oracle Flashback Data Archive: enables fast query access to old versions of the data.
5. Expanded support for standards, such as XML Query (XQuery) 1.0 and service-oriented architecture (SOA)

I'm waiting for Oracle 11g to be available for download...

Cheers, Paweł

Zend has announced new version of Zend Core for Oracle.

Key features contained in all Zend Core V2.0 releases include:

• Improved Windows Performance and Reliability – Up to 200-300%
• PHP 5.2.1 enhanced with several key security and bug fixes
• Unified "stack" installer - Bundled copies of Apache and MySQL for quick and easy install and configuration
• Bundled Zend Framework for fast and easy application development
• Universal Database Support - drivers provided for most popular databases.
• Mac OS X Support
• Zend Core Updater - Automatically download Zend Network updates

Give it a try...

Cheers, Paweł

For all PHP developers, web admins and users this might be very interesting. the Month of PHP Bugs is an initiative to improve the security of PHP. It focuses on PHP Core vulnerabilities that are dangerous for web servers with applications written in PHP. It is done in hope that flaws that are publicly disclosed will get fixed quickly.

Cheers, Paweł