Wednesday, December 10, 2008

Benchmarking Oracle 10g XE

Free to develop, deploy, and distribute...

Yes, that's the tagline for Oracle Database 10g Express Edition (XE), an entry-level database based on the 10g R2 code base. Certain handicaps are imposed: only one database can be installed per machine, user data is limited to 4GB, only one CPU shall be used, and only up to 1GB of memory.

Although there are several database benchmarking tools available, I've decided to try Benchmark Factory. The licensed version allows you to simulate 100 users whereas the freeware version is limited to only 20 virtual users.

My target database is an Oracle XE installed on a 32-bit Windows XP Pro system. I ran Benchmark Factory on another unit connected via a 100Mbps Ethernet LAN. Here are the workload benchmark results for 5, 10, 15, and 20 virtual users.

I encountered ORA-12519 errors (TNS:no appropriate service handler found) before the number of virtual clients reached 20. This was resolved by increasing the number of processes specified in the server parameter file (spfile). This is done via the following SQL commands:

SQL> alter system set processes=300 scope=spfile;
SQL> alter system set sessions=300 scope=spfile;

Restart the database and verify that the new parameter values have taken effect. Execute the following SQL command to display the current values:

SQL> select name, value from v$parameter where name in ('processes','sessions');

XE's response times did not falter even up to 100 users. Here is the transaction-time vs user-load comparison graph for 20 to 100 virtual users.

The 1GB memory restriction may have been breached at around 350 virtual users when I encountered ORA-12518 errors (TNS:listener could not hand off client connection). At 325 virtual users, the maximum qualified throughput (MQTH) is computed at 469.221 tpmC. The highest published TPC-C result can be found at