Oracle SOA Suite 11.1.1.6: Monitoring SOA Composite Behavior with SQL Queries

Oracle SOA Suite 11.1.1.6: Monitoring SOA Composite Behavior with SQL Queries


The standard information obtained from Oracle Enterprise Manager Fusion Middleware Control might not be sufficient and adequate for fine grained monitoring. By querying some core product tables in the [PREFIX]_SOAINFRA schema such as the COMPOSITE_INSTANCE, CUBE_INSTANCE, and MEDIATOR_INSTANCE tables, you can get detailed metrics that include success/failure counts, composite instance performance, and durations of invokes as well. Here, we provide two main
queries to obtain performance metrics on BPEL processes and Mediator services, specifically the duration of time that each component took. Though Oracle typically does not recommend querying the product tables directly (since the structure of the tables may change after a patch or upgrade), note that these queries below run fine on Oracle SOA Suite 11g PS3 (11.1.1.4), PS4 (11.1.1.5), and PS5 (11.1.1.6).


The following query outputs a list of all BPEL component instances, their state,
average, minimum, and maximum durations, as well as counts:

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_DATECREATION_
DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATECREATION_
DATE),18,4))),'999990.000') AVG,
TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATECREATION_
DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATECREATION_
DATE),18,4))),'999990.000') MIN,
TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATECREATION_
DATE),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATE-CREATION_DATE),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(MODIFY_DATECREATION_
DATE),18,4))),'999990.000') MAX,
COUNT(1) COUNT
FROM CUBE_INSTANCE
GROUP BY DOMAIN_NAME, COMPONENT_NAME, STATE
ORDER BY COMPONENT_NAME, STATE


The following query displays a list of all Mediator component instances, their state,
average, minimum, and maximum durations, as well as counts:

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_TIMECREATED_
TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIMECREATED_
TIME),18,4))),'999990.000') AVG,
TO_CHAR(MIN((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIMECREATED_
TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIMECREATED_
TIME),18,4))),'999990.000') MIN,
TO_CHAR(MAX((TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIMECREATED_
TIME),12,2))*60*60) +
(TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIME-CREATED_TIME),15,2))*60) +
TO_NUMBER(SUBSTR(TO_CHAR(UPDATED_TIMECREATED_
TIME),18,4))),'999990.000') MAX,
COUNT(1) COUNT
FROM MEDIATOR_INSTANCE
GROUP BY COMPONENT_NAME, SOURCE_ACTION_NAME, COMPONENT_STATE
ORDER BY COMPONENT_NAME, SOURCE_ACTION_NAME, COMPONENT_STATE

Fusion Middleware/ Oracle SOA 11.1.1.6 :Switching the JVM from Sun JDK to JRockit JDK

Fusion Middleware/ Oracle SOA 11.1.1.6 :Switching the JVM from Sun JDK to JRockit JDK


1. Download and install Oracle JRockit JDK (latest version currently 28.2.0):

• Download JRockit for Linux x86-64 from http://www.oracle.com/technetwork/middleware/jrockit/downloads/index.html.
• Execute the following command to change permissions and start the installer:

           chmod 750 jrockit-jdk1.6.0_29-R28.2.0-4.1.0-linux-x64.bin
          ./jrockit-jdk1.6.0_29-R28.2.0-4.1.0-linux-x64.bin

• On the Welcome prompt, press Enter.
• On the Choose Production Installation Directory prompt, enter a directory such as /u01/app/oracle/jrockit1.6.0_29, to install JRockit and press Enter twice.
• On the Optional Components 1 prompt, press Enter to accept the default (which is not to install Demos and Samples).
• On the Optional Components 2 prompt, press Enter to accept the default (which is not to install Source Code).
• On the Installation Complete prompt, press Enter to exit the installer.

2. Log on to the Linux server hosting your Oracle SOA Suite 11g installation as the oracle user.

3. Stop the AdminServer as well as all managed servers.

4. Stop the Node Manager.

5. Edit the file $MW_HOME/wlserver_10.3/common/bin/commEnv.sh and replace the following two entries     

     as follows:

         OLD: JAVA_HOME="/u01/app/oracle/jdk1.6.0_26"

         NEW: JAVA_HOME="/u01/app/oracle/jrockit1.6.0_29"

         OLD: JAVA_VENDOR=Sun

         NEW: JAVA_VENDOR=Oracle

6.Edit the file $MW_HOME/user_projects/domains/[Domain]/bin/setDomainEnv.sh, and replace the 

   following two entries as follows:

   OLD: BEA_JAVA_HOME=""

   NEW: BEA_AVA_HOME="/u01/app/oracle/jrockit1.6.0_29"

   OLD: SUN_JAVA_HOME="/u01/app/oracle/jdk1.6.0_26"

   NEW: SUN_JAVA_HOME=""

7. Edit the domain configuration file $MW_HOME/user_projects/domains/[Domain]/bin/setSOADomainEnv.sh, and replace the following entry

as follows:  

    OLD: PORT_MEM_ARGS="-Xms768m -Xmx1536m"

    NEW: PORT_MEM_ARGS="-Xms1536m -Xmx1536m -Xgcprio:throughput

    -XX:+HeapDumpOnOutOfMemoryError

8.Start up the Node Manager.

9. Boot up the AdminServer and all managed servers back again.

10. View the new JDK being used in the $DOMAIN_HOME/servers/AdminServer/logs/AdminServer.log