Advertisements

Posts Tagged 'soa-infra'

Query to find BPEL execution time

I had a scenario where business event is captured by a mediator in one of the composites and routes it to several BPEL processes in a sequence to complete particular business flow. I had to know the time taken by each of these BPELs in my end-to-end flow. Instead of going through each of the BPEL instance in EM console, I came up with the following query to find out the time taken. I used the ECID to correlate all the BPEL instances generated during my process flow. Check if it’s of help to any.

SELECT INSTANCE_KEY, COMPOSITE_NAME, COMPONENT_NAME,
       CREATION_DATE, MODIFY_DATE, EVAL_TIME
FROM (SELECT INSTANCE_KEY, COMPOSITE_NAME, COMPONENT_NAME,
               CREATION_DATE, MODIFY_DATE, EVAL_TIME
       FROM BPEL_PROCESS_INSTANCES
        WHERE instance_key IN (SELECT cikey FROM CUBE_INSTANCE
                              WHERE ecid = <<ecid>>)
       UNION ALL
       SELECT 1 INSTANCE_KEY, ‘Comp’ COMPOSITE_NAME, COMPONENT_NAME,
             CREATED_TIME CREATION_DATE, UPDATED_TIME MODIFY_DATE,
             CASE
          WHEN UPDATED_TIME IS NOT NULL THEN
            (extract(DAY FROM (UPDATED_TIME – CREATED_TIME)) * 24 * 60 * 60 +
             extract(hour FROM (UPDATED_TIME – CREATED_TIME)) * 60 * 60 +
             extract(minute FROM (UPDATED_TIME – CREATED_TIME)) * 60 +
             extract(second FROM (UPDATED_TIME – CREATED_TIME))) * 1000
         ELSE NULL
             END AS eval_time
        FROM MEDIATOR_INSTANCE
    WHERE ecid = <<ecid>>
       )
ORDER BY INSTANCE_KEY;

In case of the multiple events, we can rewrite the query if the composites are using sensors. The following query has been used in our case to monitor and to get execution times, minimum and maximum time taken for each of the component in process flow.

/*SELECT ECID, INSTANCE_KEY, CID, COMPOSITE_NAME, COMPONENT_NAME, CREATION_DATE,  MODIFY_DATE, EVAL_TIME */
–SELECT COMPONENT_NAME,  min(EVAL_TIME), max(eval_time)
SELECT ECID, count(component_name) cnt
FROM (SELECT ECID, CIKEY INSTANCE_KEY, CIKEY CID, COMPOSITE_NAME, COMPONENT_NAME, CREATION_DATE, MODIFY_DATE,
        CASE
        WHEN MODIFY_DATE IS NOT NULL
        THEN (extract(DAY FROM (MODIFY_DATE – CREATION_DATE)) * 24 * 60 * 60 +
              extract(hour FROM (MODIFY_DATE – CREATION_DATE)) * 60 * 60 +
              extract(minute FROM (MODIFY_DATE – CREATION_DATE)) * 60 +
              extract(second FROM (MODIFY_DATE – CREATION_DATE))) * 1000
          ELSE NULL
              END AS EVAL_TIME
      FROM CUBE_INSTANCE
      WHERE ecid IN (SELECT b.ecid FROM COMPOSITE_SENSOR_VALUE a, MEDIATOR_INSTANCE b
                                               WHERE sensor_name = <<sensor name>>
                            AND  a.composite_instance_id = b.composite_instance_id
                                        AND string_value IN (<<values>>)
                        )                           
    UNION ALL
    SELECT a.ECID, 1 INSTANCE_KEY, a.composite_instance_id CID,
               ‘Comp’ COMPOSITE_NAME, a.COMPONENT_NAME,
              a.CREATED_TIME CREATION_DATE, a.UPDATED_TIME MODIFY_DATE,
              CASE
        WHEN UPDATED_TIME IS NOT NULL
        THEN (extract(DAY FROM (UPDATED_TIME – CREATED_TIME)) * 24 * 60 * 60 +
              extract(hour FROM (UPDATED_TIME – CREATED_TIME)) * 60 * 60 +
              extract(minute FROM (UPDATED_TIME – CREATED_TIME)) * 60 +
              extract(second FROM (UPDATED_TIME – CREATED_TIME))) * 1000
          ELSE NULL
              END AS eval_time
      FROM MEDIATOR_INSTANCE a, COMPOSITE_SENSOR_VALUE b
      WHERE a.composite_instance_id = b.composite_instance_id
        AND b.sensor_name = <<sensor name>>
        AND b.string_value IN (<<values>>)
  )
–GROUP BY COMPONENT_NAME
GROUP BY ECID order by cnt

–ORDER BY ecid, INSTANCE_KEY, CID;

Note: Based on the requirement, the above select and group by statements have to be commented/uncommented.

Advertisements

SOA-INFRA Issue

I have installed SOA Suite and OSB several times but never observed an issue with soa-infra. I used Oracle DB Enterprise Edition in all these installations.

Today i have tried with SOA Suite 11.1.1.4 installation with XE database as i was just preparing an environment for developers for their practice. But observed an issue with soa-infra in EM console which showing it as down. So went to deployments in weblogic admin console and verified the status of soa-infra application and found it in ‘Failed’ status. So the first thing i tried out was starting the soa-infra from deployments section in admin console. When i did this, i encountered the following error:

ORA-04063: package body "DEV_MDS.MDS_INTERNAL_SHREDDED" has errors ORA-06508: PL/SQL: could not find program unit being called: "DEV_MDS.MDS_INTERNAL_SHREDDED" ORA-06512: at line 1

Solution:

  • Do ‘grant execute on dbms_lob to public’ in sqlplus using sys login.
  • Recompile the package DEV_MDS.MDS_INTERNAL_SHREDDED

Restart the server and verify that soa-infra is in Active status now. If the issue is not resolved drop the schema and recreate using the RCU. But before recreating, make sure that following environment variable is set when using XE database.

set RCU_JDBC_TRIM_BLOCKS=TRUE


Advertisements

Pages

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 371 other followers

Enter your email address to follow this blog and receive notifications of new posts by email.

Advertisements