SOA 11g - Useful SOA Infra SQL queries for Oracle SOA Production Support - Part II ( Queries and its usage)

Query to find the count of instances for individual composites :

SELECT count(*),SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1,INSTR(SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1),'!')-1)
FROM COMPOSITE_INSTANCE WHERE
CREATED_TIME > TRUNC(SYSDATE)-7 AND CREATED_TIME < TRUNC(SYSDATE)
GROUP BY SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1,INSTR(SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1),'!')-1);

Normally composite_dn will be like XX_Test/HelloWorld!1.0*soa_36243828-42da-4952-9579-f58c0e913705, so to overcome that, we can use below code to get the composite name alone.

"SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1,INSTR(SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1),'!')-1)"

This query is used to get the instance count of all composites, all states between timestamps. 
Filters can be added and modified to get the results as required. 

How to find the instance ID if we have composite sensors in our composites:

SELECT Ci.*,
  number_value Delivery_ID
FROM composite_sensor_value csv,
  (SELECT *
  FROM COMPOSITE_INSTANCE
  WHERE CREATED_TIME > TRUNC(SYSDATE)-7
  AND CREATED_TIME  < TRUNC(SYSDATE)
  AND SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1,INSTR(SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1),'!')-1) IN ('%Composite_Name%')
    --AND STATE <> '1'
  )CI
WHERE CSV.COMPOSITE_INSTANCE_ID = CI.ID
AND SENSOR_NAME                 = '%Sensor_Name%'
AND NUMBER_VALUE                = '%Sensor_Values%'
ORDER BY CREATED_TIME;
Add or modify filters to get the results as expected. 

If you are not sure about the type of sensor, use this substr(clob_value, instr(clob_value,'>',1,1)+1, instr(clob_value,'</',1,1)-1) to filter or select the values. 

if you want to get the details for particular state, then read this post to understand the state and description.

Query to get the count of instance on hourly basis:

SELECT TO_CHAR(TRUNC(ci.CREATED_TIME, 'HH'), 'DD-MON-YYYY HH24:MI:SS'), COUNT (*) FROM COMPOSITE_INSTANCE CI
WHERE CREATED_TIME > TO_DATE('19-JUN-2014 08:35:10', 'DD-MON-YYYY HH24:MI:SS') AND
CREATED_TIME < sysdate
GROUP BY TO_CHAR (TRUNC (CI.CREATED_TIME, 'HH'), 'DD-MON-YYYY HH24:MI:SS')
ORDER BY TO_CHAR (TRUNC (CI.CREATED_TIME, 'HH'), 'DD-MON-YYYY HH24:MI:SS');

Modify the filter created_time accordingly to get the expected results. 
You can add composite_dn filter to get the hourly count of instances for one particular composite. 

Query to get the count of instances on different state:

SELECT COUNT(*),
  DECODE(cube_instance.STATE, 0, 'Initiated', 1, 'Running', 2, 'Suspended', 3, 'Faulted', 4, 'Closed Pending', 5, 'Closed Completed', 6, 'Closed Faulted', 7, 'Closed Cancelled', 8, 'Closed Aborted', 9, 'Closed Stale', 10,'Closed Rolled Back','unknown') state
FROM CUBE_INSTANCE
WHERE creation_date > TRUNC(sysdate)-1
AND creation_date   < sysdate
GROUP BY STATE;

If you want to get the same details on composite wise,

SELECT COUNT(*),
  DECODE(CUBE_INSTANCE.STATE, 0, 'Initiated', 1, 'Running', 2, 'Suspended', 3, 'Faulted', 4, 'Closed Pending', 5, 'Closed Completed', 6, 'Closed Faulted', 7, 'Closed Cancelled', 8, 'Closed Aborted', 9, 'Closed Stale', 10,'Closed Rolled Back','unknown') STATE ,
  COMPOSITE_NAME
FROM CUBE_INSTANCE
WHERE CREATION_DATE > TRUNC(sysdate)-1
AND CREATION_DATE   < SYSDATE
GROUP BY STATE,
  COMPOSITE_NAME
ORDER BY COMPOSITE_NAME;

Above are the major tables and possible joins, so we can rewrite as per our requirement and get the expected results quickly. 

Caution - From my experience, while running queries in Production SOA infra schema, make sure you restrict the number of records to search by adding date filter else it may thorow temp table space error.