Debug or Trace option enabled in profiles

Some profile options may be required to be set to Y, but others should be N.

select  distinct
        a.application_short_name app_short,
        user_profile_option_name optname,
        decode(level_id,
        10001,'SITE',
        10002,'APP : '||a2.application_short_name,
        10003,'RESP: '||r.responsibility_key,
        10004,'USER: '||u.user_name,
        'Unknown') d_level,
        profile_option_value optval,
        v.last_update_date updated
from fnd_profile_options_vl o,
        fnd_profile_option_values v,
        fnd_application a,
        fnd_application a2,
        fnd_responsibility r,
        fnd_user u
where (
        o.user_profile_option_name like '%Debug%' or
        o.user_profile_option_name like '%DEBUG%' or
        o.user_profile_option_name like '%Trace%' or
        o.user_profile_option_name like '%TRACE%'
        )
and a.application_id = v.application_id
and o.application_id = v.application_id
and o.profile_option_id = v.profile_option_id
-- Find the associate level for profile
and r.application_id (+) = v.level_value_application_id
and r.responsibility_id (+) = v.level_value
and a2.application_id (+) = v.level_value
and u.user_id (+) = v.level_value
and profile_option_value = 'Y'
order by 2,1,3,4;

Avarage time for completed Concurrent Requests

It provides the average time taken by requests to run.

SELECT /*+ index(r FND_CONCURRENT_REQUESTS_N6) */ f.application_short_name apps, p.concurrent_program_name, 
   p.user_concurrent_program_name description, COUNT(*) COUNT,
   DECODE(TRUNC(AVG(actual_completion_date - actual_start_date)), 0, NULL, TRUNC(AVG(actual_completion_date - actual_start_date)) || ' Days' || ' + ') || 
   TO_CHAR(TO_DATE(TRUNC(MOD(AVG(actual_completion_date - actual_start_date),1) * 86400), 'SSSSS'), 'HH24:MI:SS') average,
   DECODE(TRUNC(MAX(actual_completion_date - actual_start_date)), 0, NULL, TRUNC(MAX(actual_completion_date - actual_start_date)) || ' Days' || ' + ') || 
   TO_CHAR(TO_DATE(TRUNC(MOD(MAX(actual_completion_date - actual_start_date),1) * 86400), 'SSSSS'), 'HH24:MI:SS') MAX,
   DECODE(TRUNC(MIN(actual_completion_date - actual_start_date)), 0, NULL, TRUNC(MIN(actual_completion_date - actual_start_date)) || ' Days' || ' + ') || 
   TO_CHAR(TO_DATE(TRUNC(MOD(MIN(actual_completion_date - actual_start_date),1) * 86400), 'SSSSS'), 'HH24:MI:SS') MIN
FROM applsys.fnd_concurrent_request_class c,
     applsys.fnd_application f,
     apps.fnd_concurrent_programs_vl p,
     applsys.fnd_concurrent_requests r
WHERE r.program_application_id = p.application_id
   AND r.concurrent_program_id = p.concurrent_program_id
   AND r.phase_code='C' AND r.status_code = 'C' 
   AND actual_completion_date BETWEEN SYSDATE-2 AND SYSDATE
   AND p.application_id = f.application_id
   AND r.program_application_id = f.application_id
   AND r.request_class_application_id = c.application_id(+)
   AND r.concurrent_request_class_id = c.request_class_id(+)
-- AND p.concurrent_program_name LIKE '%XXCUST%'
-- AND p.user_concurrent_program_name like '%SSO%'
GROUP BY c.request_class_name, f.application_short_name,
   p.concurrent_program_name, p.user_concurrent_program_name, r.priority
ORDER BY average DESC, APPS ASC;

Find Concurrent Programs with trace enabled

It shows which programs are defined with Trace enabled. Generally, you should enable trace for a program while you are debugging, but then you should turn it off when you're done.

SELECT A.CONCURRENT_PROGRAM_NAME "Program Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User Program Name",
SUBSTR(B.USER_NAME,1,15) "Last Updated By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;