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.
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
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 were as follows (total timings are rounded up to the nearest second):
||2 sec | 293 ms avg
||6 sec | 504 ms avg
||16 sec | 593 ms avg
||3 sec | 1284 ms avg
||10 sec | 861 ms avg
||29 sec | 1094 ms avg
||2 sec | 389 ms avg
||9 sec | 838 ms avg
||21 sec | 803 ms avg
||3 sec | 1038 ms avg
||18 sec | 1644 ms avg
||36 sec | 1403 ms avg
Java (Spring framework)
||1 sec | 101 ms avg
||1 sec | 82 ms avg
||2 sec | 72 ms avg
||1 sec | 112 ms avg
||2 sec | 232 ms avg
||5 sec | 203 ms avg
||1 sec | 73 ms avg
||1 sec | 116 ms avg
||3 sec | 116 ms avg
||1 sec | 271 ms avg
||3 sec | 256 ms avg
||6 sec | 234 ms avg
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.
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.
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.
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.
- 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
- login to source oracle server as oracle.
- source the environment file for STARTEST – orastartest
- connect to rman – rman target=/ catalog=recman/xxx@rmantest
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.
- source the environment file for STARDUST – orastardust
- copy import line from import script in /u22/oradata/STARDUST
- 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
- The tablespacess are now available in STARDUST.
- alter tablespace sits_data read write;
- alter tablespace sits_indx read write;
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”
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!