| Tuning
Messages in an Oracle E-Business Suite – ESB Integration
Environment
By Ron Batra [Mar-08]
Introduction and Scope
In a large scale A2A (application-to-application)
integration of Oracle E-Business Suite into an Enteprise Application
Framework using Oracle SOA Suite, let us consider the sub-case
of publication service (mainly Oracle Project Accounting with
some other data elements). This service publishes data elements
Oracle Enterprise Service Bus (ESB), from where five other applications
subscribe to the data elements being published. The focus of
this article is to show an iterative process in tuning the messages
generated in an event driven architecture framework. Figure
1 shows the conceptual architecture. The focus of this paper
is on message sizing and not so much on service invocation and
related topics such as adapter selection etc.
Event Driven Architecture and Messaging:
In an event driven architecture, changes to
data elements can be transferred in real-time/near real-time
through an ESB framework. These data changes are converted into
a message that is published to the ESB. In older file transfer
based integration methods, delta transactions were simply collected
and written to a file and this file was transferred to the target
system(s). In most cases, the file-size was never an issue.
In an event driven architecture and an ESB framework, message
sizing and volume of messages is one of the key design decisions
to be made. In most cases, business events cannot be changed,
so the focus is on tuning message sizing and throughput. To
do this well data cardinatity and affinity needs to be closely
examined and implemented in the final design.
Consideration on Integration Design Patterns:
In this scenario of integrating N+ Enteprise
Applications, the canonical model design pattern works well
– it allows domain value mapping as well as dynamically
addition of new subscribers. In this case, while designing the
publication service, we started off with 5 subscription services;
however that number can change dynamically without any major
code rework. It is usually worthwhile to invest time upfront
in designing the canonical model and validate rigorously the
business requirements that drive the design of the service.
Oracle EBusiness Suite Integration Repository:
The Oracle Integration Repository maintained
at http://irep.oracle.com lists integration methods available
for each product family in the EBusiness Suite with the most
common choices being Concurrent Programs, EDI, Interface Views,
Java, Open Interfaces, PL/SQL, Web Services and XML Gateway
Maps. In our case of the outbound interface in an ESB framework
(publishing to the ESB) - our business requirements drove us
to extract data from different product families and combine
them into a large message. We chose to develop a system of views
and triggers as explained later on in the section.
Data Elements to be published:
The design of the publication service is
driven largely by the requirements of the subscribers. However,
reading between the tea-leaves and trying to forecast future
data needs can often pay off by avoiding redesign of the publication
service in the future. Data elements from Human Resources, General
Ledger, Purchasing, Project Accounting had to be combined into
one large message to be published to the ESB.
| Module |
Key
Data Elements |
HR |
Organization Units, Organization ID,
People, Assignments |
| GL |
GL Code Combinations, GL Periods |
| PO |
Vendors |
Project Accounting |
Project ID, Project Name, Project
Cost, Project Cost Distribution Lines, Periods, Tasks,
Expenditures (Items, Groups, Types etc.) |
Table 1: Data
Elements Listing
Design Approach 1: Complex SQL View and Staging Tables
In the first iteration of the
design, we used a combination of views, PL/SQL program units
and a parent-child relationship structure created between 4
staging tables. Table 5 has to retrieve a data set based on
a Project ID field. This query has to navigate the parent-child
relationship between 4 tables for every result-set. A trace
of the adapter code found a lot of fetches, even with bind variables;
it was obvious there was some room for efficiency here. We traced
the SQL calls made to the database using traditional Oracle
database tracing methods.
The key point to note here is
the number of joins the adapter code had to do, to put together
a result set that could be transformed into an XML message.
This led to a significant difference between SQL elapsed time
and actual execution time. This can increase even more if the
Oracle Application Server and Database Server are in different
locations and network latency can come into play – and
applies to every message published. The average message size
was 54KB and we averaged about 10000 messages per day.
Design Approach 2: Adding GROUP BY Clause
In the second iteration, we changed
the SQL of the Delta Transactions View, and added a GROUP BY
clause. This was done to reduce the number of transactions populated
in the cost transactions table. The message size reduced to
about 40KB, and we averaged about 15,000 messages per day.
Design Approach 3: Flattened Table Structure with Simplified
Logic
To reduce the recursive SQL and
the joins coming from the parent-child relationship, we decided
to create a flattened (essentially a de-normalized table) where
data elements are referenced by project id and date. The temp
table creates only records that do not have an ID in table 1.
Once a message is published on the ESB, we do not need to store
the same data elements in the temp table, and let the ESB queues
and its persistence take care of managing data consumption by
the publishers.
We found our messages reduced
to 14KB and while the number of messages went up, we were able
to process 50000 messages in 2 hours. This was well within the
acceptable processing window.

Determining message size:
We combined two measurements
to determine message size. In an ESB environment, a message
is a combination of data elements and an XML structure (tags).
The data elements sizing is calculated from the tables where
the data originates – eg. VARCHAR2(60) + DATE + NUMBER
– one can take the maximum possible and assume that
as the upper limit for the data elements. To the bytes obtained
from this, we added the size of the XML tags relevant to the
data mapping. Since XML is text, this is relatively uncomplicated.
However, this is an estimate and not the actual amount that
will pass through the ESB, where transport methods and protocols
can add their overhead to a message.
Conclusion:
Tuning messages can start from
the data extraction layer. Depending on the complexity of the
data sets, and related affinity, a publication service can use
traditional SQL tuning methods to enable adequate throughout
of messages through the adapter. Depending on the integration
environment and number of end-points, a high-volume of small
sized messages may be processed faster than a smaller number
of, but larger sized messages. Message sizing is a key element
in an event driven architecture.
|