Where the Concurrent Manager kept himself Busy

We come across this question many time what Concurrent manager was doing. So here is one script which will help you to find this:

rem ------------------------------------------------------------------
rem Filename: cmup.sql
rem Purpose: Concurrent Manager Usage by Program /day
rem - Can only run from sqlplus
rem -----------------------------------------------------------------------

cl scr
set feedback off
set verify off
set line 1000
set pagesize 1111
set head off
spool d:\cmup.csv
prompt Concurent Program, Program, Total Duration(Min), Avg Duration(Min), Min Duration(Min), Max Duration(Min), Times_Run
select /*+ ALL_ROWS */
fcqtl.user_concurrent_queue_name', 'fcptl.program','round(sum(greatest(actual_completion_date - actual_start_date,0))*60*24, 2)', 'round(avg(greatest(actual_completion_date - actual_start_date,0))*60*24, 2)', 'round(min(greatest(actual_completion_date - actual_start_date,0))*60*24, 2)', 'round(max(greatest(actual_completion_date - actual_start_date,0))*60*24, 2)', 'count(*)
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 - 1) and actual_start_date <= trunc(sysdate)
group by
fcqtl.user_concurrent_queue_name,
fcptl.program
order by
round(sum(greatest(actual_completion_date - actual_start_date,0))*60*24, 2) desc,
fcqtl.user_concurrent_queue_name asc
/
spool off

Happy Troubleshooting !!!

How Many Archive Log Generated Today

Hi I am back to Business. I would like to start my first post in this new year with some useful information to find how many archive file generated today hourly. Here is the script:

/* archlogs.sql
find the number of archivelogs each hour of the day
*/
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1
/

Happy Troubleshooting !!!

Happy New Year

You might be wondering where I have been for so long, Officially we were busy in our Dev server movement to some other location internally and at personal front we blessed with a Baby Boy on Christmas Eve (24th Dec). So was busy or would say too busy to look at this site. Today got some time so thought of sharing good news with you all and Wish Happy New Year to all of you out there. On Funny side :
"May all your troubles last as long as your New Year's resolutions."

Happy Troubleshooting !!!

What is significance of 0 & Y while using ( FNDCPASS, FNDLOAD, WFLOAD .....) .

Another in a row for Basics:

-'0' refers to the ( Concurrent Program ) request ID , as Invocation isNOT from the concurrent program SRS where in each Concurrent Request is assigneda non-zero id.

- 'Y' is the value for a flag that indicates the method of invocation is directly from the command-line not from the SRS.

Note: SRS (Standard Request Submission)
Happy Troubleshooting !!!

Meaning of Codes in FND_CONCURRENT_REQUESTS table?

Back to Basics, we use this daily and forget what they mean internally. so Today I thought of going back to Apps DBA Basics and giving meaning of the codes in the status_code and phase_code columns of the FND_CONCURRENT_REQUESTS table?

STATUS_CODE Column:
A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting

PHASE_CODE column.
C Completed
I Inactive
P Pending
R Running

Happy Troubleshooting !!!