Diagnose SOA composite performance using sql queries

Even though the Enterprise manager console along with the weblogic diagnostic framework provides different ways of monitoring the Oracle SOA suite the below queris can come in handy for analyzing the performance of SOA composite applications and start looking into the composite design issues.

The following query gets the details from CUBE_INSTANCE table for the BPEL instances and provide informations like instances, their state,average, minimum & maximum durations

in seconds and the total count after the previous purge or deployment.

SELECT DOMAIN_NAME PARTITION,COMPONENT_NAME,

DECODE(STATE,'1','RUNNING','5','COMPLETED','6',

'FAULTED','9','STALE') STATE,

TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4))),'999990.000') AVG,

TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4))),'999990.000') MIN,

TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),18,4))),'999990.000') MAX,

COUNT(1) COUNT

FROM <DB_PREFIX>.CUBE_INSTANCE where COMPONENT_NAME like 'XX%'

GROUP BY DOMAIN_NAME, COMPONENT_NAME, STATE

ORDER BY COMPONENT_NAME, STATE

The following query gets the details from MEDIATOR_INSTANCE table for the mediator instances and provide informations like instances, their state,average, minimum & maximum

durations in seconds and the total count.

SELECT SUBSTR(COMPONENT_NAME, 1, INSTR(COMPONENT_NAME,'/')-1)PARTITION,

SUBSTR(COMPONENT_NAME, INSTR(COMPONENT_NAME,'/')+1,

INSTR(COMPONENT_NAME,'!')-INSTR(COMPONENT_NAME,'/')-1) COMPONENT,

SOURCE_ACTION_NAME ACTION,

DECODE(COMPONENT_STATE,'0','COMPLETED','2',

'FAULTED','3','ABORTED','4','RECOVERY

NEEDED','8','RUNNING','16','STALE') STATE,

TO_CHAR(AVG((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4))),'999990.000') AVG,

TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4))),'999990.000') MIN,

TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),12,2))*60*60) +

(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +

TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),18,4))),'999990.000') MAX,

COUNT(1) COUNT FROM <DB_PREFIX>._SOAINFRA.MEDIATOR_INSTANCE WHERE COMPONENT_NAME like 'XYZ%'

GROUP BY COMPONENT_NAME, SOURCE_ACTION_NAME, COMPONENT_STATE

ORDER BY STATE ,COMPONENT_NAME, SOURCE_ACTION_NAME, COMPONENT_STATE

Below sql statement can be run to query the virtual table v$sql in Oracle to find the recently executed top slowest  queries and needs tuning.

SELECT * FROM (SELECT sql_fulltext, sql_id,child_number,disk_reads,executions,first_load_time,last_load_time FROM v$sql ORDER BY elapsed_time DESC)
WHERE ROWNUM < 15

The above queries are seen working fine upto PS6(11.1.1.7).

DB Adapter fails to return nVarchar Type from stored procedure with MS SQLServer 2005

When you are using DbAdapter to access MS SQLServer 2005 Stored Procedures that return resultsets as dynamic row sets with nVarchar column types, a class type mapping exception is thrown.

You will see a stack trace as follows:

<faultstring>Exception occured when binding was invoked.
Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation '***' failed due to: Unimplemented string conversion.
Conversion of JDBC type to String is not supported.
An attempt was made to convert a Java object to String using an unsupported JDBC type: .
Use a data type with a supported JDBC type.
".
The invoked JCA adapter raised a resource exception.
Please examine the above error message carefully to determine a resolution.</faultstring>

The issue can be resolved by switching the driver from  com.microsoft.sqlserver.jdbc.SQLServerDriver c to weblogic.jdbc.sqlserver.SQLServerDriver

But there is a limitation of maximum size  4000 characters that can be returned by any column of the stored procedure while using this driver.