Analyzing instances in an Oracle SOA environment: linking composite instances via references

To analyse a running environment, it is useful to know which process calls which other process and how often it does so (which instance is initiated by which reference from which other process). This provides insight in how composites are linked and thus how process flows are implemented. In a previous post I've looked at Oracle Business Transaction Management (BTM) to achieve this insight (http://javaoraclesoa.blogspot.nl/2013/11/a-first-look-at-oracle-business.html). Oracle BTM can't analyse local invocations and requires quite some work to install/setup correctly. Also a license is required, for example SOA Management Pack Enterprise Edition, in order to use this product. To make monitoring composite instances and their relations possible without this product, the dehydration store, can be queried. In this post I'll describe a method on how this can be done. Mind that this method is not fully tested and certainly not supported by Oracle. Use with caution!

Implementation

Sample setup

In order to see if I could implement this, I've first created a couple of test processes;

HelloWorld is a synchronous process which adds 'Hello ' to the input and returns it. HelloWorldCaller and HelloWorldCaller2 proxy this service. HelloWorldCallerCaller proxies HelloWorldCaller.

When I call HelloWorldCallerCaller, the audit trail will look as followed;

Thus the information I want to obtain from the dehydration store is the following;
HelloWorld is called by 2 processes; HelloWorldCaller and HelloWorldCaller2
HelloWorldCaller is called by one process; HelloWorldCallerCaller
HelloWorldCallerCaller and HelloWorldCaller2 are not called by other processes

In total when I call every process once, I want to see the following counts;
HelloWorld; called 4 times (1 directly, 3 via references)
HelloWorldCaller; called 2 times (1 directly, one via reference)
HelloWorldCaller2; called once (1 directly)
HelloWorldCallerCaller; called once (1 directly)

In total 4 calls which are executed via references.

Since the described method uses information from created instances, first instances need to be created. I've called every process once using the test console in EM FMC. This is also done to confirm all processes work as expected.

Identifying calls and counts

In order to identify process instances, the following query can be used;

select rtrim(ltrim(REGEXP_SUBSTR(composite_dn, '/[^,]+!'),'/'),'!') value from composite_instance

If you want to take into account the domain and revision, the following can be used;

select rtrim(REGEXP_SUBSTR(composite_dn, '[^,]+\*'),'*') value from composite_instance

To identify the calls how much calls are executed via references to a process, the following can be executed;

SELECT rtrim(REGEXP_SUBSTR(ci.composite_dn, '[^,]+\*'),'*') process,
  rtrim(REGEXP_SUBSTR(ri.composite_dn, '[^,]+\*'),'*') called_from,
  COUNT(*) called_count
FROM composite_instance ci,
  reference_instance ri
WHERE ci.parent_id=('reference:'
  ||ri.id)
GROUP BY ci.composite_dn,
  ri.composite_dn

The result of this query is displayed below;

As can be seen, this corresponds to the expected results; 3 calls to HelloWorld via references and one call to HelloWorldCaller via references.

This however does not identify calls which are executed not via a reference, but for example by the test console.

To identify these calls, the following can be used;

SELECT rtrim(REGEXP_SUBSTR(ci.composite_dn, '[^,]+\*'),'*') process,
  'non-composite' called_from,
  COUNT(*) called_count
FROM composite_instance ci
WHERE ci.parent_id is null
GROUP BY ci.composite_dn

When a union all is used, all instances of processes become visible. It is possible though that there are situations when a process is not initiated by a reference and the parent_id is not null. These instances are not included in the below query. If these cases occur, they can be identified easily on more heavily used environments.

SELECT rtrim(REGEXP_SUBSTR(ci.composite_dn, '[^,]+\*'),'*') process,
  rtrim(REGEXP_SUBSTR(ri.composite_dn, '[^,]+\*'),'*') called_from,
  COUNT(*) called_count
FROM composite_instance ci,
  reference_instance ri
WHERE ci.parent_id=('reference:'
  ||ri.id)
GROUP BY ci.composite_dn,
  ri.composite_dn
union all
SELECT rtrim(REGEXP_SUBSTR(ci.composite_dn, '[^,]+\*'),'*') process,
  'non-composite' called_from,
  COUNT(*) called_count
FROM composite_instance ci
WHERE ci.parent_id is null
GROUP BY ci.composite_dn

The result is the following. This shows all the expected calls for this example.

In order to make this query time aware to for example determine a moving average, the CREATED_TIME from the COMPOSITE_INSTANCE and REFERENCE_INSTANCE tables can be used. You can also produce call chains by using hierarchical queries on this (mind loops though when you do). In this sample I'm not looking at composite states. I've also limited this analysis to instances present in the dehydration store and have not included analyzing references defined in composites. It is possible other references exist but are not used often. The above queries will only make them visible if they occur in instances