XPath - Terra Ignota

There are more things in heaven and earth, Horatio,

Than are dreamt of in your philosophy.

As usual before  holidays we've got another issue with timeouts,  adapters errors and database performance.

I found that DB Adapter used several queries and then joins result sets. This way get us right result (very simplified version you can see  bellow) with pretty far from optimal performance. 

<off:OfferActionCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                           xmlns:off="http://xmlns.oracle.com/offer">
  <off:OfferAction>
    <off:id>2121</off:id>
    <off:offerPositionId>
      <off:id>2133</off:id>
      <off:positionNum>3</off:positionNum>
    </off:offerPositionId>
    <off:offerActionDetalCollection>
      <off:OfferActionDetal>
        <off:id>2262</off:id>
      </off:OfferActionDetal>
      <off:OfferActionDetal>
        <off:id>2263</off:id>
      </off:OfferActionDetal>
    </off:offerActionDetalCollection>
  </off:OfferAction>
</off:OfferActionCollection>

Option  'Use Outer Join ..' corrects  query issues , but returns non-unique collection. Like this one:

<off:OfferActionCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                           xmlns:off="http://xmlns.oracle.com/offer">
  <off:OfferAction>
    <off:id>2121</off:id>
    <off:offerPositionId>
      <off:id>2133</off:id>
      <off:positionNum>3</off:positionNum>
    </off:offerPositionId>
    <off:offerActionDetalCollection>
      <off:OfferActionDetal>
        <off:id>2262</off:id>
      </off:OfferActionDetal>
      <off:OfferActionDetal>
        <off:id>2263</off:id>
      </off:OfferActionDetal>
    </off:offerActionDetalCollection>
  </off:OfferAction>

  <off:OfferAction>
    <off:id>2121</off:id>
    <off:offerPositionId>
      <off:id>2133</off:id>
      <off:positionNum>3</off:positionNum>
    </off:offerPositionId>
    <off:offerActionDetalCollection>
      <off:OfferActionDetal>
        <off:id>2262</off:id>
      </off:OfferActionDetal>
      <off:OfferActionDetal>
        <off:id>2263</off:id>
      </off:OfferActionDetal>
    </off:offerActionDetalCollection>
  </off:OfferAction>
</off:OfferActionCollection>

I realised that I don't know much about DB Adapter, so we decide to create XQuery/XPath (or anything else)  to filer collection. 

Well, in fact now I realise that I know about X* almost nothing. By the way, after several hours googling, writing and trying  I found an acceptable message transformation expression.

It loops thru distinct values of OfferAction/id (for clause) and returns first node of Xpath result ( /OfferAction[off:id=$ActionId][1]).

The hardest thing for me was - how to combine XPath filters and expressions.

It works just perfect, but I think that next time I'll play on my own field - 

 queries, indexes and hints.

Happy New Year to everyone! Hope to see you in the next year.

Oracle BAM 11g. Report optimizations

I'm trying to keep in touch with our SOA development team. So I've been surprised when I know that they aren't going to maintain and develop any BAM reports for new composites. During discussion they mentioned very poor  reports performance, they tired to optimize it, create several indexes, but without any noticeable result.
Errare humanum est - said Ancient Romans, they leaved to us a lot of such phrases. And  I also have several rules. Meet with one of these:

" The most important part of performance tuning is the database design and optimization"

You can tune heap and garbage collection, you can try to split threads and assign it to separate processor cores. But you always can crate such "great" design, that your efforts will give you almost nothing, because all performance will be limited by disks IO.
I know what you could say, and I have to notice that I'm talking about old-school relational databases. Well lets go back to the BAM case.

After short inspection, I've noticed that there are  only two big data objects, and several lookup tables, so reports were created against classic star - schema.
In fact, I was able to get report with aggregate composites information, but I was not able to open detailed process report.
It's sounds quite strange for me, so I took a look to the  layout of  main data objects.

Lookup fields in the BAM data object

If you are not familiar with BAM, I should explain what BAM data object is.

Using  BAM Architect you can manipulate with data representation objects. Then  you can build reports against your objects in  BAM Studio.  From the other side BAM server publishes  data objects with a set of web services and you can populate it with data. The most common using of BAM is a SOA monitoring  dashboards.

By the way, from the performance tuning perspective you should know two things:

•  You can build indexes against Data Objects fields. 
• BAM Data Objects use database tables and indexes to persist information.

Hmmm (I like this sound so much), is there any indexes for lookup fields and in the lookup data objects? The short answer was - NO, and I've immediately created them. 
Actually you can use Data Architect capabilities and don't ask DBA to do this.

Add Index to the data object

Just open Indexes part (1) of data object description and click to Add Index (2). Select object fileds (database columns) and name it.

When I've done it, against all objects and all source and lookup fields. The BAM reports start works, like a charm.