Capgemini Oracle Blog

Capgemini Oracle Blog

Opinions expressed on this blog reflect the writer’s views and not the position of the Capgemini Group

Tuning the Purging Strategy; Oracle SOA Suite Metadata Repository Performance Management

Categories : SOAHow-toDatabase
My colleague, Paul Wever, wrote an excellent blog about turning the MDS to increase performance. Purging is something we do at almost all our customers. Paul provides excellent tips, tricks and insights to increase the performance.

In my job as system administrator/DBA, projects related to Oracle’s SOA Suite I put a lot of emphasis on configuration of the environment, like the OS, the Oracle database, WebLogic, OSB, and other products. Part of the Oracle SOA Suite is the Oracle Metadata Repository, where all metadata and run-time data of applications is stored. This repository is often forgotten in performance management, but deserves special attention when dealing with performance improvements.
This blog is part of a series of blogs around Oracle SOA Suite Metadata Repository Performance Management. In this blog we will show how a performance gain of factor more than 100 was achieved while purging the Metadata Repository.

Terminology
Oracle Metadata Repository
Oracle Metadata Repository is an Oracle database that contains additional schemas to support Oracle Fusion Middleware and its components, for design and run-time management of the applications. Oracle SOA Suite mainly uses two components:
  • The Metadata Store (MDS) to store all SCA artifacts
  • The Dehydration Store (SOAINFRA) to persist composite instances and to audit composite instance
Case
Investigating performance issues around purging the Oracle Metadata Repository, I stumbled over the following delete statement that was executed during purging the Oracle Metadata Repository:

DELETE FROM composite_instance_assoc
      WHERE    assoc1_composite_instance_id IN (SELECT id
                                                  FROM composite_instance_purge)
            OR assoc2_composite_instance_id IN (SELECT id
                                                  FROM composite_instance_purge);


This statement is executed every time when purging the Oracle Metadata Repository.
This statement took 19 minutes and 24 seconds to complete. The execution plan for this statement looked like this. 

Id Operation Name Rows Bytes Cost (%CPU)
0 DELETE STATEMENT   1 136 4988K (3)
1  DELETE COMPOSITE_INSTANCE_ASSOC      
*2   FILTER        
3    TABLE ACCESS FULL COMPOSITE_INSTANCE_ASSOC 980K 127M 466 (1)
*4    TABLE ACCESS FULL COMPOSITE_INSTANCE_PURGE 1 6 5 (0)
*5    TABLE ACCESS FULL COMPOSITE_INSTANCE_PURGE 1 6 5 (0)
Explain Plan 1: TABLE ACCESS FULL on COMPOSITE_INSTANCE_ASSOC

The explain plan shows a full table scan on table composite_instance_assoc and twice on table composite_instance_purge. Table composite_instance_purge is a table used by the purging strategy to store batches of records. As a result the full table scan on this table is desirable. In an upcoming blog I’ll go more in dept on the architecture of the purging mechanism.
A full table scan on composite_instance_assoc is highly undesirable. The size of the table dictates the speed of deleting a single row or a batch of rows. The larger the table, the slower the deletion process becomes.

How to fix it?
To eliminate full table scans, an index must be used. Unfortunately, the Oracle Metadata Repository doesn’t come with indexes on both columns. Let’s create these indexes.

CREATE INDEX COMPOSITE_INSTANCE_ASSOC1_IDN ON COMPOSITE_INSTANCE_ASSOC (ASSOC1_COMPOSITE_INSTANCE_ID);
CREATE INDEX COMPOSITE_INSTANCE_ASSOC2_IDN ON COMPOSITE_INSTANCE_ASSOC (ASSOC1_COMPOSITE_INSTANCE_ID);

Rerunning the query doesn’t change the execution path. It still uses the same execution path as shown above. But in order to get rid of the full table scan on composite_instance_assoc, the indexes need to be used!
Looking at the query, it actually consists of two queries, one that deletes records from composite_instance_assoc where a match is found between the purge-able data set in composite_instance_purge and the assoc1_composite_instance_id column. And another one where the match is made on the assoc2_composite_instance_id column.

Both statements are shown here and their execution plans. It’s obvious that running both statements equals the same result it would get from the original statement.

DELETE FROM composite_instance_assoc
      WHERE    assoc1_composite_instance_id IN (SELECT id
                                                  FROM composite_instance_purge);


Id Operation Name Rows Bytes Cost (%CPU)
0 DELETE STATEMENT   9210 1277K 7 (29)
1  DELETE COMPOSITE_INSTANCE_ASSOC      
2   NESTED LOOPS   9210 1277K 7 (29)
3    SORT UNIQUE   9210 55260 5 (0)
4     TABLE ACCESS FULL COMPOSITE_INSTANCE_ASSOC 9210 55260 5 (0)
*5     INDEX RANGE SCAN COMPOSITE_INSTANCE_ASSOC1_IDN 1 136 0 (0)

And
DELETE FROM composite_instance_assoc
      WHERE    assoc2_composite_instance_id IN (SELECT id
                                                  FROM composite_instance_purge);


Id Operation Name Rows Bytes Cost (%CPU)
0 DELETE STATEMENT   19653 2725K 7 (29)
1  DELETE COMPOSITE_INSTANCE_ASSOC      
2   NESTED LOOPS   19653 2725K 7 (29)
3    SORT UNIQUE   9210 55260 5 (0)
4     TABLE ACCESS FULL COMPOSITE_INSTANCE_ASSOC 9210 55260 5 (0)
*5     INDEX RANGE SCAN COMPOSITE_INSTANCE_ASSOC1_IDN 2 272 0 (0)

Both statements only took seconds (!) to complete, the first one took 5 seconds, the second one only 2 seconds.
This results in a factor 166 ( (19x60+24)/(5+2) = 166.29) performance improvement. As the size of the composite_instance_assoc table will grow, the performance improvement factor will also grow.

Wrapping it up
The performance improvement requires some adjustments to the Oracle Metadata Repository.
First, you need to create two additional indexes on table composite_­instance_assoc.

CREATE INDEX COMPOSITE_INSTANCE_ASSOC1_IDN ON COMPOSITE_INSTANCE_ASSOC (ASSOC1_COMPOSITE_INSTANCE_ID);
CREATE INDEX COMPOSITE_INSTANCE_ASSOC2_IDN ON COMPOSITE_INSTANCE_ASSOC (ASSOC1_COMPOSITE_INSTANCE_ID);

Second, you need to make changes to procedure deleteCompositeInstances in the body of the purging package SOA_FABRIC.
Replace the following code:
        --v_sql := 'delete from composite_instance_assoc where assoc1_composite_instance_id in ( select id from ' || purge_id_table ||') or assoc2_composite_instance_id in ( select id from ' || purge_id_table ||')';
        --execute immediate v_sql;

with:
        v_sql := 'delete from composite_instance_assoc where assoc1_composite_instance_id in ( select id from ' || purge_id_table ||')';
        execute immediate v_sql;
        v_sql := 'delete from composite_instance_assoc where assoc2_composite_instance_id in ( select id from ' || purge_id_table ||')';
        execute immediate v_sql;


Although the performance improvement is substantial, making changes to the Oracle Metadata Repository is not what we would recommend. In case of upgrades, the changes can easily be overwritten. When Oracle creates a patch, it’s likely that it will be implemented in subsequential patch sets.
Therefore, we registered a Service Request with Oracle, that resulted in a Bug Report. At the moment of writing this blog, Oracle hasn’t yet released a patch to solve this issue.

Conclusion
  • The improvement resulted in a performance boost of factor more than 100.
  • The performance boost improves the efficiency of your purging strategy, keeping the SOA Suite repository smaller in size, thus more manageable and in better condition to serve requests.
  • This improvement adds to the stability of your SOA Suite application.
  • Oracle already acknowledged the issues and has created a bug (Bug 19730112). In time Oracle will come with a patch to resolve the issue.
  • This is a work-around! When Oracle releases the patch to solve this issue, I recommend you to install it to prevent the issues from recurring after an upgrade.
Hope you liked it and can use it to improve the performance of your SOA Suite application.


Paul Wever is Oracle DBA and Weblogic configuration specialist at Capgemini.

About the author

Martijn van der Kamp
Martijn van der Kamp
I am an Oracle Integration Consultant. My specialization lay in the areas of SOA Suite, BPM Suite, BAM and databases. Colleagues think high of my social skills and customer focus. Furthermore I'm known for my drive and enthusiasm. Within projects I take great responsibility for the work which I deliver, this will act itself in terms of quality and timeliness of delivery.

Leave a comment

Your email address will not be published. Required fields are marked *.