Oracle ADF Metrics on EM

Oracle entrrprice manager (EM) is an usefull monitoring tool for ADF application module tuning.

Some times monitoring does not work if applciation is versioned.

In this case use the patch suggested here by the oracle support:

ADF Performance screen shows "No Data available" under Application Module Pools tab (Doc ID 2575146.1)

Oracle BPM 12c SOAINFRA purge tables and query

 

Purge

Purge has become a configurable process by enterprise console. A very nice feature since we cannot query the database :). But this blog is titled “everyday usefull queries”…so you will always want to know that purge has run the previous day.

* Show job details

1 2 3 4

select log_date, status, req_start_date, actual_start_date, run_duration from user_scheduler_job_run_details --where job_name = 'DELETE_INSTANCES_AUTO_JOB1' order by log_date;

* Show the job history and status

1 2 3

select log_date, status from user_scheduler_job_log --where job_name = 'DELETE_INSTANCES_AUTO_JOB1' order by log_date;

* Show a running job

1 2 3

select session_id, running_instance, elapsed_time, cpu_used from user_scheduler_running_jobs --where job_name = 'DELETE_INSTANCES_AUTO_JOB1';

The above queries are documented. If you want more details please check on purge and how it works :
Oracle Documentation – Managing Data Growth

Another usefull link on purge is:

How to find purgable instances link

* Run Purge on Demand for a single flow id

1	exec soa.delete_instances_adhoc ('60910152,');

Oracle BPM 12c SOAINFRA tables and query - part 2

To fetch information based on State of instance, Composite name and approvers name

run below mention query:

SELECT (CASE WHEN c.STATE=1 THEN 'OPEN AND RUNNING'
WHEN c.STATE=2 THEN 'OPEN AND SUSPENDED'
WHEN c.STATE=3 THEN 'OPEN AND FAULTED'
WHEN c.STATE=4 THEN 'CLOSED AND PENDING'
WHEN c.STATE=5 THEN 'CLOSED AND COMPLETED'
WHEN c.STATE=6 THEN 'CLOSED AND FAUTED'
WHEN c.STATE=7 THEN 'CLOSED AND CANCELLED'
WHEN c.STATE=8 THEN 'CLOSED AND ABORTED'
WHEN c.STATE=9 THEN 'CLOSED AND STALE'
WHEN c.STATE=10 THEN 'NON-RECOVERABLE'
ELSE C.STATE || ''
END) AS STATE, w.compositename, w.updatedbydisplayname ,

count(*)AS NUM_OF_INSTANCES
FROM CUBE_INSTANCE C, WFTASK W
WHERE c.cmpst_id=w.compositeinstanceid
group by w.compositename, w.updatedbydisplayname,C.STATE;

The above query needs access to two tables “CUBE_INSTANCE” and “WFTASK”.

• CUBE_INSTANCE: - The cube instance table stores the instance data for any composite instance. The CIKEY is the primary key of the table and this is the unique reference for a initiated instance. The STATE denotes whether the instance is stale/completed successfully/error. ‘Cmpst_id’ is the composite instance Id.
• WFTASK: - This table is Metadata table for workflow tasks. ‘Compositename’ shows the name of the composite. ‘Updatedbydisplayname’ column shows the approver’s name.
  

Find by title or flow_id the “flow tree”.First column is the parent composite

 

select parent.COMPOSITE_NAME as ParentCMP,

cu.COMPOSITE_NAME,cu.COMPONENT_NAME,cu.STATE, 

(CASE WHEN cu.STATE = 0 THEN 'INITIATED'
WHEN cu.STATE = 1 THEN 'OPEN AND RUNNING'
WHEN cu.STATE = 2 THEN 'OPEN AND SUSPENDED'
WHEN cu.STATE = 3 THEN 'OPEN AND FAULTED'
WHEN cu.STATE = 4 THEN 'CLOSED AND PENDING'
WHEN cu.STATE = 5 THEN 'CLOSED AND COMPLETED'
WHEN cu.STATE = 6 THEN 'CLOSED AND FAUTED'
WHEN cu.STATE = 7 THEN 'CLOSED AND CANCELLED'
WHEN cu.STATE = 8 THEN 'CLOSED AND ABORTED'
WHEN cu.STATE = 9 THEN 'CLOSED AND STALE'
WHEN cu.STATE = 10 THEN 'CLOSED_ROLLED_BACK'
ELSE cu.STATE || '' END) cuState,cu.CIKEY,
cu.COMPOSITE_REVISION,
cu.CREATION_DATE,
scf.ACTIVE_COMPONENT_INSTANCES,
scf.RECOVERABLE_FAULTS,
scf.TITLE,
parent.CIKEY as ParentCikey,
cu.FLOW_ID
from CUBE_INSTANCE cu,SCA_FLOW_INSTANCE scf,CUBE_INSTANCE parent
where
parent.CIKEY=to_number(REGEXP_SUBSTR(cu.PARENT_ID, '[^:]+$'))
and cu.FLOW_ID=scf.FLOW_ID
and cu.PARENT_ID is not null
and scf.title='GIVEYOURTITLEHERE'
-- and scf.flow_id='1234'
ORDER by cu.CREATION_DATE

Faulted composites

 

 SELECT scai.title FROM SCA_COMMON_FAULT scaf,SCA_FLOW_INSTANCE scai
where
scaf.flow_id=scai.flow_id and
scaf.FAULT_NAME = 'SystemFault'  and
scaf.CREATION_DATE > TO_DATE('2018-02-14 00:00:00', 'YYYY-MM-DD HH24:Mi:SS')
-- Different States:
-- 256 not recoverable
-- 2560 recovered
-- 512 recovery required RETRY/REPLAY/ABORT/CONTINUE
-- 3584 recovered retry
-- 3072 recovery required RETRY/ABORT/CONTINUE
-- 2304 AUTO RETRY
-- 756 recovery required RETRY/ABORT/CONTINUE transactionRollback
and scaf.state=512
--and scai.RECOVERABLE_FAULTS!=0
--and  scaf.COMPONENT_TYPE='bpel'
group by scai.title,scaf.flow_id

 

Count All Instances by state in human readable format, group by composite. (same as 11g)

SELECT (CASE
WHEN STATE=1 THEN 'OPEN AND RUNNING'
WHEN STATE=2 THEN 'OPEN AND SUSPENDED'
WHEN STATE=3 THEN 'OPEN AND FAULTED'
WHEN STATE=4 THEN 'CLOSED AND PENDING'
WHEN STATE=5 THEN 'CLOSED AND COMPLETED'
WHEN STATE=6 THEN 'CLOSED AND FAUTED'
WHEN STATE=7 THEN 'CLOSED AND CANCELLED'
WHEN STATE=8 THEN 'CLOSED AND ABORTED'
WHEN STATE=9 THEN 'CLOSED AND STALE'
WHEN STATE=10 THEN 'NON-RECOVERABLE'
ELSE STATE || '' END) AS STATE,COMPOSITE_NAME, 

COUNT(*) AS NUM_OF_CUBE_INST 

FROM CUBE_INSTANCE GROUP BY STATE,COMPOSITE_NAME

SQL Query to extract BPM Tasks information 

 

SELECT
         fwt.tasknumber
       , fwt.assigneesdisplayname
       , fwt.assigneddate
       , fwt.outcome
       , fwt.title
       , fwt.taskdefinitionname
       , fwc.wfcomment
       , fwat.content
       , fwat.name AS attachmentname
       , fwat.description
       , fwat.attachmentsize
       , fwm.name
       , fwm.encoding
       , fwm.blobvalue
       , fwm.elementseq
FROM
         fa_fusion_soainfra.wftask                    fwt
       , fa_fusion_soainfra.wfassignee                fwa
       , fa_fusion_soainfra.wftaskhistory             fwh
       , fa_fusion_soainfra.wfcomments                fwc
       , fa_fusion_soainfra.wfattachment              fwat
       , fa_fusion_soainfra.wftaskassignmentstatistic fwst
       , fa_fusion_soainfra.wfmessageattribute        fwm
       , fa_fusion_soainfra.wfcollectiontarget        fwtg
WHERE
         1               =1
         AND fwt.taskid  = fwa.taskid
         AND fwt.taskid  = fwh.taskid
         AND fwa.taskid  = fwc.taskid(+)
         AND fwt.taskid  = fwat.taskid(+)
         AND fwat.taskid = fwst.taskid(+)
         AND fwt.taskid  = fwm.taskid
         AND fwt.taskid  = fwtg.taskid(+)
         --and fwt.tasknumber = '201200'
ORDER BY
         fwt.compositecreatedtime DESC;

ref:
Access To SOA BPM Tables In BI Publisher (Doc ID 2275088.1) 

SOA INFRA usefull everyday queries 12C