Posted on

Oracle SOA vs Spring – SOAP Web Service throughput testing

We are soon going to embark on a major project to introduce enterprise notification handling at the University. Part of that will be the ability to handle a large number of messages in an efficient and robust manner. We already use Oracle SOA Suite here at the University, but wanted to test its throughput versus a lighter approach, that of Java and the Spring framework.

The scenarios

We chose four scenarios to test:

  • Basic assign, parameter passed in is passed out as response
  • DB Write , parameter passed in is written to Oracle Database
  • DB Read, parameter passed in is used to read value from Oracle Database
  • DB Read/Write, parameter passed in is written to Oracle Database, then read back out again

Testing constraints

We then applied the same constraints to both Oracle SOA and Java:

  • A connection pool must be used with the same settings (min 1, max 10 connections)
  • The same table structure/setup must be used with both technologies
  • We use the same back-end Oracle database
  • Testing would be done using a SOAP UI load test

For Oracle SOA, we set up a simple composite which tested the various features.

For Java Spring, we used Spring Boot, Spring Web Services, and Spring JPA.

The results

The results were as follows (total timings are rounded up to the nearest second):

Oracle SOA

500 calls 2000 calls 5000 calls
Assign 2 sec | 293 ms avg 6 sec | 504 ms avg 16 sec | 593 ms avg
Write 3 sec | 1284 ms avg 10 sec | 861 ms avg 29 sec | 1094 ms avg
Read 2 sec | 389 ms avg 9 sec | 838 ms avg 21 sec | 803 ms avg
Write Read 3 sec | 1038 ms avg 18 sec | 1644 ms avg 36 sec | 1403 ms avg

Java (Spring framework)

500 calls 2000 calls 5000 calls
Assign 1 sec | 101 ms avg 1 sec | 82 ms avg 2 sec | 72 ms avg
Write 1 sec | 112 ms avg 2 sec | 232 ms avg 5 sec | 203 ms avg
Read 1 sec | 73 ms avg 1 sec | 116 ms avg 3 sec | 116 ms avg
Write Read 1 sec | 271 ms avg 3 sec | 256 ms avg 6 sec | 234 ms avg

Conclusions

It is clear that the Java Spring solution is giving better throughput times,, and that is especially evident when we increase the load. However it would be unfair to use throughput times alone in looking at what Oracle SOA provides. It gives for example an “out of the box” message resilience and  support for automated message retry that would have to be coded in when using Java even with the benefit of Spring frameworks. However, Spring can provide a very useful high throughput entry point into Oracle SOA.

We want to benefit from the strengths of each of the technologies, so we are going to use the following:

  • Java Spring Web Services will be used as the initial entry point for creating/editing/deleting notification messages
  • The Java Spring WS will put a message in a queue for Oracle SOA
  • Oracle SOA will poll the queue for messages, then will apply the necessary business processing and rule logic for pushing notifications out
  • Oracle SOA will handle message retry in the event of processing failures
  • Java Spring Web Services will be used for pulling user notifications out for subscriber systems

As with most of the modern web, building a solution is about choosing the right set of technologies and not choosing a single technology approach. We’re confident now that we can introduce the necessary scale to handle a modern enterprise notifications system.

Posted on

Using Oracle Transportable Tablespaces to refresh Schemas/Tablespaces in Databases

If there is a requirement to refresh large schemas/tablespaces within a  databases regularly it is worth considering using transportable tablespaces (TTS). This method is ideal for moving large data quickly thus minimising downtime. The time taken will depend on the size of the data files being moved and the amount of DDL contained, but generally speaking the operation will not take much longer than the time to move the data files. Interestingly TTS forms the basis for the new pluggable databases to be delivered in 12c, there is a “plugged_in” column in dba_tablespaces which will be set to “yes” after using TTS.

There are some limitations which can be found in the link below, but in the most cases we are able to use TTS.

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm#ADMIN11394

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmxplat.htm#BRADV05432

If we are refreshing using point in time data, where there is no requirement for  real time data, we would use RMAN backups to create our TTS sets. This means there is no effect on our source system i.e. no need to put the tablespaces into read only mode for the duration of the copy.

rman tts

TTS Example

I recently transported sits_data and sits_indx in STARDUST(target) from STARTEST(source) using an RMAN catalog and a recent backup of the source db. RMAN will handle the creation of an auxiliary database for you to facilitate the point in time recovery of the desired tablespaces.

 

Assumptions:

  • You are already using an RMAN catalog to backup the source system.
  • The target system already exists.
  • There is adeququite space for another copy of the data files.
  • The Tablespaces do not exist on the target system( i.e. dropped or renamed)
  • TTS_IMP directory is pointing to /u22/oradata/STARDUST
  • tablespaces have already been checked using
    exec dbms_tts.TRANSPORT_SET_CHECK(‘tablespace(s)

Execution:

        1. login to source oracle server as oracle.
        2. source the environment file for STARTEST – orastartest
        3. connect to rman –  rman target=/ catalog=recman/xxx@rmantest
        4. issue:
          RMAN> transport tablespace sits_data,sits_indx
          TABLESPACE DESTINATION ‘/u22/oradata/STARDUST’
          auxiliary destination ‘/b01/backup/TTS’
          until time ‘sysdate-2/24’;
          This will create a dump file and import script which will be used to import the ddl into the target db.
        5. source the environment file for STARDUST – orastardust
        6. copy import line from import script in /u22/oradata/STARDUST
        7. e.g. impdp / directory=TTS_DIR dumpfile= ‘dmpfile.dmp’ transport_datafiles= /u22/oradata/STARDUST/sits_data01.dbf, /u22/oradata/STARDUST/sits_data02.dbf, /u22/oradata/STARDUST/sits_data03.dbf, /u22/oradata/STARDUST/sits_data04.dbf,/u22/oradata/STARDUST/sits_data05.dbf, /u22/oradata/STARDUST/sits_data06.dbf, /u22/oradata/STARDUST/sits_data07.dbf, /u22/oradata/STARDUST/sits_data08.dbf, /u22/oradata/STARDUST/sits_data09.dbf, /u22/oradata/STARDUST/sits_data10.dbf, /u22/oradata/STARDUST/sits_data11.dbf, /u22/oradata/STARDUST/sits_data12.dbf, /u22/oradata/STARDUST/sits_indx01.dbf, /u22/oradata/STARDUST/sits_indx02.dbf, /u22/oradata/STARDUST/sits_indx04.dbf, /u22/oradata/STARDUST/sits_indx05.dbf, /u22/oradata/STARDUST/sits_indx06.dbf, /u22/oradata/STARDUST/sits_indx03.dbf
        8. The tablespacess are now available in STARDUST.
        9. alter tablespace sits_data read write;
        10. alter tablespace sits_indx read write;

 

Posted on

Path: A new stack of PHP, Yii and Oracle

Path has been through a variety of environments over its two-and-a-bit-year life. When it was a student project, it started off on servers run by EUSA. It then moved to IS’s webhosting environment. Now it’s on a proper supported LAMP infrastructure, with three environments and the sort of features you might have come to expect. This is fantastic news for Path’s future, but it hasn’t been the steadiest journey.

Continue reading “Path: A new stack of PHP, Yii and Oracle”

Posted on

Query the SQL which drives an Oracle view.

If you’ve ever wanted to find out which views reference a certain table/column condition in an Oracle database, there is a view called user_views which can be queried as the owner of the view and contains the sql used to define the population of the view. There is a slight trick to querying it though, as that column is a Long datatype. You can use the following SQL:

select * from user_views
where dbms_xmlgen.getxml('select text from user_views 
                          where view_name = ''' || view_name || '''') 
                         like '%<thing I want to search for>%';

Just replace the like with the thing you want to search for. Hope that’s useful!