Of ECIDs and Histograms; Oracle SOA Suite Metadata Repository Performance Management by Paul Wever

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 15 was achieved while querying 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 instances

ECIDs
ECID, or Execution Context ID, is a unique identifier to correlate events or requests associated with the same transaction across several components. As a message is passed from composite to composite, the ECID is passed within each message. In other words, ECIDs are used to track a message flow that crosses instances of different composite applications.
Histograms & buckets
Histograms are a special type of column statistics that provide more detailed information about the data distribution in a table column. Histograms help the Oracle Optimizer in deciding whether to use an index or a fulltable scan. These are most useful for a column that is included in a WHERE clause and the data distribution is skewed. A histogram sorts values into buckets to help estimate the cardinality. Oracle uses a maximum of 254 buckets.
Case
Investigating performance issues around purging the Oracle Metadata Repository, I stumbled over the following simple query that was executed by the SOA Suite while running an application:
^{SELECT ID, CONVERSATION_ID, …
  FROM COMPOSITE_INSTANCE
WHERE (ECID = :1)
ORDER BY CREATED_TIME DESC;}
This is a simple query with a simple WHERE clause returning 22 records. Nothing special. And as an index on column ECID with lots of distinct values exists, I expected the Oracle Optimizer to execute the query using an Index Range Scan.
To my surprise the Oracle Optimizer chose to use a Full Table Scan (see Explain Plan 1 below). The query took 2.79 seconds. Read the whole article here.

SOA & BPM Partner Community

For regular information on Oracle SOA Suite become a member in the SOA & BPM Partner Community for registration please visit www.oracle.com/goto/emea/soa (OPN account required) If you need support with your account please contact the Oracle Partner Business Center.

Blog Twitter LinkedIn Facebook Wiki