Tuesday, December 27, 2011

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.   



Tuhin said...

I am not sure you are still looking at this blog or not. But I am having this same issue with my reports. I am having 20M records on my DO. And I do not have option to limit that number. All are active data. Now I want to see the movement. But report is very slow. Index created also. still same.

Michael Mikhailidi said...

Well, there is multiple ways to boost performance:
1. Straight and expensive: database server/RAC scale up.
2. Use cache or IMDB in front of Oracle RDBMS. For example Bam Report -> Oracle TimesTen -> Oracle RDBMS.
3. Full schema revision - separate transactional data and reports to produce pre-calculated data.
4. table partitioning and limit default report ranges (ie create pages by time)

Unknown said...

Nice post. Oracle is a relational database management system produced by oracle corporation. Nowadays most of the multinational companies used this oracle database for storing and managing their data's and programs. So learning Oracle Training is one of the best idea to make a bright career.