SQL Scripts for Monitoring Transactions

                                           SQL Scripts for Monitoring Transactions

There are some useful scripts that are helpful in monitoring the instances or transactions for particular composites deployed in weblogic Environment.

Benefit: These scripts are helpful for techies working in production support that will help them in analyzing
the load coming to there environment for particular services or for all the services deployed in weblogic Environment.

Below are the scripts:

Average, minimum, and maximum duration of components

SELECT DOMAIN_NAME,
COMPONENT_NAME,
DECODE(STATE,'5','COMPLETE','9','STALE','10','FAULTED') STATE,
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,
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,
COUNT(1) COUNT
FROM CUBE_INSTANCE
WHERE CREATION_DATE >= SYSDATE-1
--AND COMPONENT_NAME LIKE '%%'
AND COMPOSITE_NAME LIKE '%%'
GROUP BY DOMAIN_NAME, COMPONENT_NAME, STATE
ORDER BY COMPONENT_NAME, STATE

Note: Enter the name of the component or composite name accordingly.

Number of instance in every hour (load query)

SELECT inner_tab.hour_time,count(*) no_of_incidents  FROM ( select to_number(to_char(created_time, 'HH24')) hour_time  from COMPOSITE_INSTANCE
where created_time BETWEEN to_date('23-09-2013 19:00:00','DD-MM-YYYY HH24:MI:SS')
AND to_date('24-09-2013 00:00:00','DD-MM-YYYY HH24:MI:SS')
)inner_tab GROUP BY inner_tab.hour_time order by inner_tab.hour_time

Note: change the date accordingly.

Running instances of any particular composite in last one hour

select compin.id, substr(compin.composite_DN, 0, instr(compin.composite_DN, '!')-1) Composite_name, compin.source_name, compin.conversation_id
, to_char(compin.created_time, 'MM/DD/YY-HH:MI:SS')
from composite_instance compin
where
compin.state = '0'
and compin.id not in (select cmpst_id from cube_instance cubein)
and compin.created_time > sysdate - 1/24
and substr(compin.composite_DN, 0, instr(compin.composite_DN, '!')-1) IN('composite_name1','composite_name2');

Note: change the name of the composites accordingly.

Instance processing times

SELECT create_cluster_node_id, cikey, conversation_id, parent_id, ecid, title, state, status, domain_name, composite_name, cmpst_id, TO_CHAR
(creation_date,'YYYY-MM-DD HH24:MI:SS') cdate, TO_CHAR(modify_date,'YYYY-MM-DD HH24:MI:SS') mdate,
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))
FROM   cube_instance
WHERE  TO_CHAR(creation_date, 'YYYY-MM-DD HH24:MI') >= '2013-05-06 11:00'
AND    TO_CHAR(creation_date, 'YYYY-MM-DD HH24:MI') <= '2013-05-06 18:00'
ORDER BY cdate;

Note: change the date and time accordingly.

Number of long Running(More than 7 days) instances for any particular composite.

select compin.id, substr(compin.composite_DN, 0, instr(compin.composite_DN, '!')-1) Composite_name, compin.source_name, compin.conversation_id
, to_char(compin.created_time, 'MM/DD/YY-HH:MI:SS')
from composite_instance compin
where
compin.state = '0'
and compin.id not in (select cmpst_id from cube_instance cubein)
and compin.created_time < sysdate - 7
and substr(compin.composite_DN, 0, instr(compin.composite_DN, '!')-1) IN('Partition/composite_name');

Note : change the partition and composite name accordingly.

Thanks a lot for your patience!!!!

Regards
-Ashish