Tabular display of Completed Concurrent Requests/hour/Manager


 
Short desc Tabular display of Completed Concurrent Requests/hour/Manager
Os Any
Tested on 11.5.10,11.5.9
Remarks
Submitted by Ajith Pathiyil
Date Feb 2008


Description This scripts spool output in csv format for better presentation. This script gives 1 week data of how many concurrent programs ran per managers in each hour on each day. Its very help for tunning Concurrent Managers queues.

 

 

 

 

rem -----------------------------------------------------------------------
rem Filename:   crcm_weekday.sql
rem Purpose:    Tabular display of Completed Concurrent requests Concurrent Requests/hour/Manager)
rem                  - Can only run from sqlplus
rem Author:     Ajith pathiyil
rem -----------------------------------------------------------------------

cl scr
set pagesize 50000
set line 1000
set veri off
set colsep " "

set termout off
def time="time"
def time2="time2"                  
col time new_value time
col time2 new_value time2
set heading off
set verify off
set feedback off
select 'to_char(actual_start_date ,''DD/MM/YY HH24:MI:SS'')' time
from   dual
/
select 'to_char(actual_start_date ,''DAY'')' time2
from   dual
/
set termout on
spool d:\crcm_weekday_1.csv
prompt Concurrent Manager,Day,Weekday,00,01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23
select /*+ ALL_ROWS */
fcqtl.user_concurrent_queue_name||', '||
substr(&&time, 1, 5)||', '||&&time2||', '||
       to_char(sum(decode(substr(&&time,10,2),'00',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'01',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'02',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'03',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'04',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'05',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'06',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'07',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'08',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'09',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'10',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'11',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'12',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'13',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'14',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'15',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'16',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'17',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'18',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'19',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'20',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'21',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'22',1,0)),'9999')||', '||
       to_char(sum(decode(substr(&&time,10,2),'23',1,0)),'9999')
from
       fnd_concurrent_programs fcp,
       FND_CONC_REQ_SUMMARY_V fcptl,
       fnd_concurrent_processes fcproc,
       fnd_concurrent_queues_tl fcqtl,
       fnd_concurrent_queues fcq   
 where
       fcptl.phase_code = 'C'
       and actual_completion_date is not null
       and actual_start_date is not null
       and fcq.concurrent_queue_id = fcproc.concurrent_queue_id
       and fcq.application_id =fcproc.queue_application_id
       and fcq.manager_type  = 1
       and fcptl.controlling_manager  = fcproc.concurrent_process_id
       and fcptl.program_application_id =fcp.application_id
       and fcptl.concurrent_program_id = fcp.concurrent_program_id
       and fcp.concurrent_program_name not in ('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
       and fcproc.queue_application_id = fcqtl.application_id
       and fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
       and fcqtl.language=userenv('LANG')
       and actual_start_date >= trunc(sysdate - 6) and actual_start_date  <= trunc(sysdate + 1)
 group by
       substr(&&time,1,5),&&time2,fcqtl.user_concurrent_queue_name;
spool off
set colsep " "