Trifox, Inc. -- Database Access Experts

Oracle Benchmark

Oracle ran one of their inhouse benchmarks using VORTEX vs. their own PRO*C. Both setups accessed an Oracle server on a different machine. Without any tuning VORTEX outperformed Oracle by 30-50% as well as requiring 50% less memory on the server.
Below is a copy of the email that the Oracle engineer sent to Trifox describing the results of the benchmark. Trifox is confident that with some additional tuning the Trifox solution would achieve a 100% improvement.
Evaluation of the Vortex-Software with the Churchill-benchmark 
============================================================== 
 
1) Introduction 
--------------- 
During the last days, I made tests with the Churchill-benchmark to compare 
the performance of Oracle's Multi-Threaded-Server with Trifox's Vortex- 
software.  
This report explains the installation of the Vortex-software in the next  
chapter, describes the test configuration in chapter 3, and summarizes the 
test results in chapter 4. 
 
2) Installing the Vortex-Software 
--------------------------------- 
Nik Back from Trifox came to Oracle HQ and installed the Vortex-Software on 
KR6SEQ and KR1SEQ. This installation went smoothly (we compiled and linked 
the sources for the Vortex-Precompiler and Vortex-Multiplexer). 
 
During the precompilation of the Churchill-programs with the Vortex-precompiler 
there have been a few problems: 
(a) The Vortex-precompiler reports an error if the following keywords are  
    written in lowercase: SQLCA, SQLCODE, SQLERRD, SQLERRM. 
(b) The Vortex-precompiler does not precompile correctly when the keywords 
    EXEC SQL are followed directly by a new line. 
(c) The Vortex-precompiler generates "select 1from ..." from the statement 
    "select 1 from ..." which is not a valid Oracle-statement. 
(d) The Vortex-precompiler does not recognize " identified by     
    " as a valid connect string. The connect string must have the 
    form "/".  
These problems have been fixed by changing the programs of the Churchill- 
benchmark accordingly. The problems (a), (b), and (c) have also been fixed   
within the Vortex-software after the tests have been performed. 
 
3) Test Configuration 
--------------------- 
 
3.1) The Churchill-Benchmark 
---------------------------- 
The Churchill-benchmark consists of several PRO*C-programs, C-programs, 
shell-scripts, and an Oracle database. During a run of the benchmark, n users  
are performing m quotes (in our case: n=250 and m=4). One quote  
contains all the transactions that a Churchill employee performs on the Oracle  
database during a phone call with a customer. A quote consists of 
   22 INSERT-statements         (17 of them are different), 
   22 UPDATE-statements         (14 of them are different), 
    4 DELETE-statements          (4 of them are different), 
   72 SELECT-statements         (42 of them are different), 
  213 OPEN CURSOR-statements   (127 of them are different), 
  398 FETCH CURSOR-statements  (127 of them are different), 
   17 COMMIT-statements, 
   26 SAVEPOINT-statements, 
    8 ROLLBACK-statements 
There are 782 statements overall. 
The 72 SELECT-statements are selects that return at most one row and 
therefore do not need a cursor. For each of the 127 different OPEN 
CURSOR-statements, there is a separate DECLARE CURSOR-statement that includes 
a SELECT-clause different from the 72 single-row SELECT-statements. The number 
of different INSERT, UPDATE, DELETE, and SELECT-statements in the Churchill- 
benchmark therefore is: 17+14+4+42+127 = 204. 
 
After each COMMIT-statement, the user program sleeps for 1 second. 
 
At the end of the run, the benchmark calculates the average times for all 
quotes across all users. Furthermore, a SQL-Trace-Report for one specific 
user and a bstat/estat-Statistic is created. 
 
In our tests, the Churchill-clients are on KR6SEQ, and the Churchill-database 
and the corresponding Oracle-instance are on KR1SEQ. 
 
3.2) The Vortex Configuration  
----------------------------- 
On the client side, we compiled the PRO*C-programs with the Vortex-precompiler  
and started the Vortex-multiplexer V3.1.4.5 with the following parameters: 
 
vtxmux MUX 250 5 200 64 30 252 
 
Explanation of the parameters: 
usage: vtxmux name nc bs he hs ns cu [ns cu ...] [log] 
where: name is the symbolic MUX name 
         nc is max number of CLIENTs 
         bs is DB buffer size per CLIENT in 1Kb increments 
	 he is max number of hash table entries 
         hs is hash table size in 1Kb increments 
         ns is max number of SLAVEs per DB 
         cu is number of DB cursors per SLAVE (max: 252) 
         if log specified then ttc_.log is log file 
 
For each slave, the Vortex-software opens a connection to the database. That 
means that our 250 clients are multiplexed into 30 network connections to the 
database. 
For each connection, the Vortex-software starts a process on the Server-side 
(called "vtxhost") that passes the client requests to the Oracle instance. 
vtxhost is linked in single-task mode. 
The init.ora-parameters for the Oracle instance are the same as in the MTS- 
test (see 3.3) with the following exceptions: 
- The SHARED_POOL_SIZE is 34 MB and 
- No MTS-parameters are used     
 
3.3) The MTS-Configuration 
-------------------------- 
The MTS-tests have been performed with 20 server processes and 5 dispatcher 
processes. As MTS does the multiplexing on the server side, we still have 
250 network connections.  
The original Churchill-benchmark used a separate user name for each client. 
As the Vortex-software connects to the database with the same user name for 
each connection, I changed the Churchill-benchmark so that only one user name 
is used in the MTS-case (using a different user name for each connection 
is more expensive because of the cursor authentication that has to be done 
for each different user name). 
 
These init.ora-parameters have been used for the MTS-tests: 
db_block_buffers = 1000 
db_block_checkpoint_batch = 512 
db_file_multiblock_read_count = 32 
db_file_simultaneous_writes = 4 
dml_locks = 5000 
log_buffer = 1048576 
log_checkpoint_interval = 100000 
log_small_entry_max_size = 128 
open_cursors = 250 
processes = 500 
shared_pool_size = 190000000 
single_process = FALSE 
_db_block_write_batch = 512 
_kgl_bucket_count = 5 
_kgl_latch_count = 19 
mts_dispatchers = "tcp, 5" 
mts_listener_address = "(address=(protocol=tcp)(host=)(port=5151))" 
mts_max_servers = 20 
mts_servers = 20 
mts_service = "chill" 
 
 
4) Summary of Test Results 
-------------------------- 
 
The values in the following table are the average times in seconds for 
completing the different quotes. 
 
            ! quote1 ! quote2  ! quote3  ! quote4  
------------+--------+---------+---------+-------- 
MTS         !  186   !  133    !  137    !  115    
------------+--------+---------+---------+-------- 
Vortex (ST) !  117   !  106    !   98    !   90 
------------+--------+---------+---------+-------- 
Vortex (TT) !  201   !  170    !  169    !  154 
------------+--------+---------+---------+-------- 
 
Vortex (ST) means that the vtxhost-program has been linked in single-task 
mode. Vortex (TT) means that is has been linked in two-task mode. 
As MTS uses the single-task architecture, we should compare the MTS-results 
with the results for Vortex (ST). 
 
On the client-side, the tests with the Vortex-software needed around 100 MB 
more main memory than the tests with the MTS-software (because of the 
additional Vortex-processes and the cursor cache on the client side). 
On the server-side, the MTS-tests needed around 120 MB more memory than the 
tests with the Vortex- software (because of the much larger SGA). 
 
******************************************************************************