1. Query to find number of purgable instances and respective states according to dates
--version 1
select
to_char(trunc(partition_date,'mm'),'YYYY-mm') Month_Year,
decode(state,
1,'1 - Completed',
2,'2 - Running with faults',
3, '3 - Completed with faults',
16, '16 - Running with terminated',
17, '17 - Completed with terminated',
19, '19 - Completed with faults and terminated',
64, '64 - ?') "State",
count(distinct ecid)
from composite_instance
where ( bitand(state,127)=1 OR bitand(state,6) =2 OR bitand(state,16) =16
OR bitand(state,64) =64 OR state between 32 and 63
OR state = 3
OR state =19)
group by to_char(trunc(partition_date,'mm'),'YYYY-mm'), state
ORDER BY TO_CHAR(TRUNC(PARTITION_DATE,'mm'),'YYYY-mm'), STATE ASC;
-- version 2 group by date
select
to_char(trunc(partition_date,'mm'),'YYYY-mm') Month_Year,
DECODE(STATE,
0,'0-Running',
1,'1-Completed',
2,'2-Running with faults',
3, '3-Completed with faults',
4, '4-Running with recovery required',
5,'5-Completed with recovery required',
6,'6-Running with faults and recovery required',
7,'7-Completed with faults and recovery required',
8,'8-Running with suspended',
9,'9-Completed with suspended',
10,'10-Running with faults and suspended',
11,'11-Completed with faults and suspended',
12,'12-Running with recovery required and suspended',
13,'13-Completed with recovery required and suspended',
14,'14-Running with faults, recovery required, and suspended',
15,'15-Completed with faults, recovery required, and suspended',
16,'16-Running with terminated',
17,'17-Completed with terminated',
18,'18-Running with faults and terminated',
19,'19-Completed with faults and terminated',
20,'20-Running with recovery required and terminated',
21,'21-Completed with recovery required and terminated',
22,'22-Running with faults, recovery required, and terminated',
23,'23-Completed with faults, recovery required, and terminated',
24,'24-Running with suspended and terminated',
25,'25-Completed with suspended and terminated',
26,'26-Running with faulted, suspended, and terminated',
27,'27-Completed with faulted, suspended, and terminated',
28,'28-Running with recovery required, suspended, and terminated',
29,'29-Completed with recovery required, suspended, and terminated',
30,'30-Running with faulted, recovery required, suspended, and terminated',
31,'31-Completed with faulted, recovery required, suspended, and terminated',
32,'32-Unknown',
34,'34-Open and Faulted',
36,'36-Running with recovery required and unknown state',
64,'64-Stale') "State",
count(distinct ecid)
from composite_instance
where ( bitand(state,127)=1 OR bitand(state,6) =2 OR bitand(state,16) =16
OR bitand(state,64) =64 OR state between 32 and 63
OR state = 3
OR state =19)
GROUP BY TO_CHAR(TRUNC(PARTITION_DATE,'mm'),'YYYY-mm'), STATE
ORDER BY TO_CHAR(TRUNC(PARTITION_DATE,'mm'),'YYYY-mm'), STATE ASC;
--version 3 group by count of instances
select
DECODE(STATE,
1,'1-Completed',
2,'2-Running with faults',
3, '3-Completed with faults',
4, '4-Running with recovery required',
5,'5-Completed with recovery required',
6,'6-Running with faults and recovery required',
7,'7-Completed with faults and recovery required',
8,'8-Running with suspended',
9,'9-Completed with suspended',
10,'10-Running with faults and suspended',
11,'11-Completed with faults and suspended',
12,'12-Running with recovery required and suspended',
13,'13-Completed with recovery required and suspended',
14,'14-Running with faults, recovery required, and suspended',
15,'15-Completed with faults, recovery required, and suspended',
16,'16-Running with terminated',
17,'17-Completed with terminated',
18,'18-Running with faults and terminated',
19,'19-Completed with faults and terminated',
20,'20-Running with recovery required and terminated',
21,'21-Completed with recovery required and terminated',
22,'22-Running with faults, recovery required, and terminated',
23,'23-Completed with faults, recovery required, and terminated',
24,'24-Running with suspended and terminated',
25,'25-Completed with suspended and terminated',
26,'26-Running with faulted, suspended, and terminated',
27,'27-Completed with faulted, suspended, and terminated',
28,'28-Running with recovery required, suspended, and terminated',
29,'29-Completed with recovery required, suspended, and terminated',
30,'30-Running with faulted, recovery required, suspended, and terminated',
31,'31-Completed with faulted, recovery required, suspended, and terminated',
32,'32-Unknown',
34,'34-Open and Faulted',
36,'36-Running with recovery required and unknown state',
64,'64-Stale') "State",
count(distinct ecid) as CNT
from composite_instance
where ( bitand(state,127)=1 OR bitand(state,6) =2 OR bitand(state,16) =16
OR bitand(state,64) =64 OR state between 32 and 63
OR state = 3
OR state =19)
GROUP BY STATE
ORDER BY CNT DESC;
2. Query to find number of Running instances and respective states according to dates
--version 1
select to_char(trunc(partition_date,'mm'),'YYYY-mm') Month_Year,
decode(state, 1,'1 - Completed',
2,'2 - Running with faults',
3, '3 - Completed with faults',
16, '16 - Running with terminated',
17, '17 - Completed with terminated',
19, '19 - Completed with faults and terminated',
64, '64 - ?', state) "State", count(distinct ecid)
FROM COMPOSITE_INSTANCE
group by to_char(trunc(partition_date,'mm'),'YYYY-mm'), state
order by to_char(trunc(partition_date,'mm'),'YYYY-mm'), state asc;
--version 2
select
DECODE(STATE,
0,'0-Running',
1,'1-Completed',
2,'2-Running with faults',
3, '3-Completed with faults',
4, '4-Running with recovery required',
5,'5-Completed with recovery required',
6,'6-Running with faults and recovery required',
7,'7-Completed with faults and recovery required',
8,'8-Running with suspended',
9,'9-Completed with suspended',
10,'10-Running with faults and suspended',
11,'11-Completed with faults and suspended',
12,'12-Running with recovery required and suspended',
13,'13-Completed with recovery required and suspended',
14,'14-Running with faults, recovery required, and suspended',
15,'15-Completed with faults, recovery required, and suspended',
16,'16-Running with terminated',
17,'17-Completed with terminated',
18,'18-Running with faults and terminated',
19,'19-Completed with faults and terminated',
20,'20-Running with recovery required and terminated',
21,'21-Completed with recovery required and terminated',
22,'22-Running with faults, recovery required, and terminated',
23,'23-Completed with faults, recovery required, and terminated',
24,'24-Running with suspended and terminated',
25,'25-Completed with suspended and terminated',
26,'26-Running with faulted, suspended, and terminated',
27,'27-Completed with faulted, suspended, and terminated',
28,'28-Running with recovery required, suspended, and terminated',
29,'29-Completed with recovery required, suspended, and terminated',
30,'30-Running with faulted, recovery required, suspended, and terminated',
31,'31-Completed with faulted, recovery required, suspended, and terminated',
32,'32-Unknown',
34,'34-Open and Faulted',
36,'36-Running with recovery required and unknown state',
64,'64-Stale') "State",
count(1)
from composite_instance
GROUP BY STATE
ORDER BY STATE ASC;
3. Table queries
SELECT COUNT(1) FROM COMPOSITE_INSTANCE;--
SELECT COUNT(1) FROM CUBE_INSTANCE;--
SELECT COUNT(1) FROM WFTASK;--
select count(1) from DLV_MESSAGE;--
--Count from important tables
SELECT
(SELECT COUNT(*) FROM CUBE_INSTANCE) AS CUBE_INST,
(SELECT COUNT(*) FROM COMPOSITE_INSTANCE) AS COMP_INST,
(SELECT COUNT(*) FROM COMPONENT_INSTANCE) AS COMPNT_INST,
(SELECT COUNT(*) FROM COMPOSITE_INSTANCE_FAULT) AS COMPST_INST_FLT,
(SELECT COUNT(*) FROM REFERENCE_INSTANCE) AS REF_INST,
(SELECT COUNT(*) FROM CUBE_SCOPE) AS CUBE_SCP,
(SELECT COUNT(*) FROM COMPOSITE_SENSOR_VALUE) AS COMP_SEN_VAL,
(SELECT COUNT(*) FROM XML_DOCUMENT) AS XML_DOC,
(SELECT COUNT(*) FROM XML_DOCUMENT_REF) AS XML_DOC_REF,
(SELECT COUNT(*) FROM AUDIT_TRAIL) AS AUDIT_TRAIL,
(SELECT COUNT(*) FROM AUDIT_COUNTER) AS AUDIT_CNTR,
(SELECT COUNT(*) FROM AUDIT_DETAILS) AS AUDIT_DET,
(SELECT COUNT(*) FROM INSTANCE_PAYLOAD) AS INST_PYLD,
(SELECT COUNT(*) FROM HEADERS_PROPERTIES) AS HDRS_PROPS,
(SELECT COUNT(*) FROM WFTASK) AS WFTASK,
(SELECT COUNT(*) FROM WI_FAULT) AS WI_FLT,
(SELECT COUNT(*) FROM DLV_MESSAGE) AS DLV_MSSG,
(SELECT COUNT(*) FROM DLV_SUBSCRIPTION) AS DLV_SUBC,
(SELECT COUNT(*) FROM DOCUMENT_DLV_MSG_REF) AS DOC_DLV_MSG_REF,
(SELECT COUNT(*) FROM DOCUMENT_CI_REF) AS DOC_CI_REF,
(SELECT COUNT(*) FROM WORK_ITEM) AS WRK_ITEM
FROM DUAL;
3. Calculate increase in size of database schema's
select * from(select c.segment_name "Object Name",b.object_type,sum(space_used_delta) / 1024 / 1024 "Growth (MB)"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate)-&days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner ='*_SOAINFRA'
GROUP BY C.TABLESPACE_NAME,C.SEGMENT_NAME,B.OBJECT_TYPE)
ORDER BY 3 DESC;
4. Current Size of each tables
select segment_name,segment_type,bytes/1024/1024 MB
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE='TABLE' AND OWNER='*_SOAINFRA' order by 3 desc;
5. Size allocated to soa infra and current size
SELECT A1.TABLESPACE_NAME TABLE_SPACE, B.AUTOEXTENSIBLE, B.INCREMENT_BY,
SUM (A1.BYTES)/1024/1024 FREE_SPACE_MB, SUM (A1.BLOCKS) FREE_BLOCKS, SUM(B.BYTES)/1024/1024 ALLOCATED_SPACE_MB
FROM SYS.DBA_FREE_SPACE A1, SYS.DBA_DATA_FILES B
WHERE A1.TABLESPACE_NAME=B.TABLESPACE_NAME AND A1.TABLESPACE_NAME ='*_SOAINFRA'
GROUP BY A1.TABLESPACE_NAME, B.AUTOEXTENSIBLE, B.INCREMENT_BY;
--All tables
select
a.tablespace_name,
round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024)))) MAX_GB,
(SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024)) USED_GB,
round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024))) - (SUM(a.bytes)/(1024*1024*1024) -
round(c.Free/1024/1024/1024))),2) FREE_GB,
round(100*(SUM(a.bytes)/(1024*1024*1024) -
round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024))))) USED_PCT
from
dba_data_files a,
sys.filext$ b,
(SELECT
d.tablespace_name ,sum(nvl(c.bytes,0)) Free
FROM
dba_tablespaces d,
DBA_FREE_SPACE c
WHERE
d.tablespace_name = c.tablespace_name(+)
group by d.tablespace_name) c
WHERE
a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP BY A.TABLESPACE_NAME, C.FREE/1024
ORDER BY USED_PCT desc;
----------------------------------------------------------------------------------------------------
Table space allocated to all schema's
select tablespace_name,used_percent, TABLESPACE_SIZE from dba_tablespace_usage_metrics order by USED_PERCENT desc;
----------------------------------------------------------------------------------------------------
6. Performance of BPEL or BPM component in a day
SELECT DOMAIN_NAME,
COMPONENT_NAME,
DECODE(STATE,'5','COMPLETE','9','STALE','10','FAULTED') 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') AVERG,
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') MINI,
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') MAXI,
COUNT(1) CNT
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;
7. Performance of Mediator component in a day.
SELECT SUBSTR(COMPONENT_NAME, 1, INSTR(COMPONENT_NAME,'/')-1)
PARTITON,
SUBSTR(COMPONENT_NAME, INSTR(COMPONENT_NAME,'/')+1,
INSTR(COMPONENT_NAME,'!')-INSTR(COMPONENT_NAME,'/')-1) COMPONENT,
DECODE(COMPONENT_STATE, '0', 'COMPLETED', '16', 'STALE', '2',
'FAULTED', '4', 'RECOVERY NEEDED', '8', 'RUNNING') 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') AVERG,
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') MINI,
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') MAXI,
COUNT(1) CNT
FROM MEDIATOR_INSTANCE
WHERE CREATED_TIME >= SYSDATE-1
--AND COMPONENT_NAME LIKE '%%'
GROUP BY COMPONENT_NAME, COMPONENT_STATE
ORDER BY COMPONENT_NAME, COMPONENT_STATE;
8. Performance of each composite.
SELECT CMPST_ID,
TO_CHAR(CREATION_DATE, 'YYYY-MM-DD HH24:MI') CREATION_DATE,
COMPONENT_NAME,
COMPONENTTYPE,
DECODE(STATE,'5','COMPLETE','9','STALE','10','FAULTED') STATE,
TO_CHAR((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') DURTION
FROM CUBE_INSTANCE
WHERE TO_CHAR(CREATION_DATE, 'YYYY-MM-DD HH24:MI') >= TO_CHAR(SYSDATE-1,'YYYY-MM-DD HH24:MI')
AND (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)) > 10
--AND COMPONENT_NAME LIKE '%%'
--AND COMPOSITE_NAME LIKE '%%'
ORDER BY COMPONENT_NAME, CREATION_DATE;
SELECT COMPOSITE_INSTANCE_ID,
TO_CHAR(COMPOSITE_CREATION_DATE, 'YYYY-MM-DD HH24:MI') CREATION_DATE,
SUBSTR(COMPONENT_NAME, INSTR(COMPONENT_NAME,'/')+1,
INSTR(COMPONENT_NAME,'!')-INSTR(COMPONENT_NAME,'/')-1) COMPONENT,
DECODE(COMPONENT_STATE, '0', 'COMPLETED', '16', 'STALE', '2','FAULTED', '4', 'RECOVERY NEEDED', '8', 'RUNNING') STATE,
TO_CHAR((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') DURTION
FROM MEDIATOR_INSTANCE
WHERE TO_CHAR(COMPOSITE_CREATION_DATE, 'YYYY-MM-DD HH24:MI') >= TO_CHAR(SYSDATE-1,'YYYY-MM-DD HH24:MI')
AND (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)) > 10
-- AND COMPONENT_NAME LIKE '%%'
ORDER BY COMPONENT_NAME, CREATION_DATE;
8. Slow performing queries in the database
SELECT SQL_FULLTEXT, SQL_ID, CHILD_NUMBER, DISK_READS, EXECUTIONS,
FIRST_LOAD_TIME, LAST_LOAD_TIME, ELAPSED_TIME FROM V$SQL ORDER BY
ELAPSED_TIME DESC;
SELECT SQL_FULLTEXT, SQL_ID, CHILD_NUMBER, DISK_READS, EXECUTIONS,
FIRST_LOAD_TIME, LAST_LOAD_TIME, ELAPSED_TIME FROM V$SQL ORDER BY
ELAPSED_TIME DESC;
9. select * from user_tab_modifications order by timestamp desc;
Other queries can be found here
No comments:
Post a Comment