Wednesday, August 29, 2012

Queries Help Analysing Concurrent Requests

 - Connect sqlplus as apps user and execute:

NOTE : modify the select statement to change <request_id> for the request in position wherever needed.

SELECT request_id, status_code, phase_code
from fnd_concurrent_requests
WHERE request_id = <request_id>;

SELECT b.concurrent_queue_name, a.TYPE_APPLICATION_ID,a.TYPE_ID, a.INCLUDE_FLAG, a.TYPE_CODE
FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b,
fnd_concurrent_requests c
WHERE a.queue_application_id = b.application_id
and a.concurrent_queue_id = b.concurrent_queue_id
and a.TYPE_ID = c.concurrent_program_id
and a.TYPE_APPLICATION_ID = c.program_application_id
and c.request_id = <request_id>;

select distinct type_code
from FND_CONCURRENT_QUEUE_CONTENT;

SELECT b.concurrent_queue_name, a.TYPE_APPLICATION_ID,a.TYPE_ID, a.INCLUDE_FLAG,a.TYPE_CODE
FROM FND_CONCURRENT_QUEUE_CONTENT a, fnd_concurrent_queues b
WHERE a.queue_application_id = b.application_id
and a.concurrent_queue_id = b.concurrent_queue_id
order by b.concurrent_queue_name;

SELECT creq_run.request_id, fcq.concurrent_queue_name,
creq_run.program_application_id, creq_run.concurrent_program_id, creq_run.REQUESTED_BY
FROM    fnd_concurrent_processes fcp,
          fnd_concurrent_queues fcq,
          fnd_concurrent_requests creq_run
WHERE  fcp.concurrent_process_id = creq_run.controlling_manager
and fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND creq_run.request_id = <request_id>;

select
d.CONCURRENT_TIME_PERIOD_ID, d.SLEEP_SECONDS, d.MIN_PROCESSES, c.CONCURRENT_QUEUE_NAME
from fnd_concurrent_queue_size d, fnd_concurrent_queues c
where d.CONCURRENT_QUEUE_ID = c.CONCURRENT_QUEUE_ID
order by c.CONCURRENT_QUEUE_NAME;

SELECT cprog.TO_RUN_APPLICATION_ID, cprog.TO_RUN_CONCURRENT_PROGRAM_ID
FROM  fnd_concurrent_requests creq_run,
fnd_concurrent_program_serial cprog
WHERE creq_run.program_application_id = cprog.RUNNING_APPLICATION_ID
AND creq_run.concurrent_program_id = cprog.RUNNING_CONCURRENT_PROGRAM_ID
AND creq_run.request_id = <request_id>;

SELECT cprog.RUNNING_APPLICATION_ID, cprog.RUNNING_CONCURRENT_PROGRAM_ID
FROM  fnd_concurrent_requests creq_run,
fnd_concurrent_program_serial cprog
WHERE creq_run.program_application_id = cprog.TO_RUN_APPLICATION_ID
AND creq_run.concurrent_program_id = cprog.TO_RUN_CONCURRENT_PROGRAM_ID
AND creq_run.request_id = <request_id>;

SELECT creq_run.request_id,
      cprog.concurrent_program_name,
      cprog.run_alone_flag,
      creq_run.number_of_arguments,
      creq_run.argument_text,
      creq_run.REQUESTED_BY,
      creq_run.cd_id,
      fcq.concurrent_queue_name,
      to_char(creq_run.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
      to_char(creq_run.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),
      to_char(creq_base.request_id),
      to_char(creq_base.requested_start_date, 'DD-MON-RR HH24:MI:SS'),
      to_char(creq_base.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
      to_char(creq_base.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),
      creq_base.single_thread_flag,
      creq_base.request_limit,
      creq_base.REQUESTED_BY,
      creq_run.cd_id
FROM  fnd_concurrent_requests creq_run,
       fnd_concurrent_programs cprog,
       fnd_concurrent_requests creq_base,
       fnd_concurrent_processes fcp,
       fnd_concurrent_queues fcq
WHERE (    (creq_run.program_application_id = cprog.application_id)
AND (creq_run.concurrent_program_id = cprog.concurrent_program_id)
AND (nvl(creq_base.actual_start_date,sysdate) >=
creq_run.actual_start_date)  
AND (creq_base.requested_start_date <=
nvl(creq_run.actual_completion_date,sysdate))
AND (creq_base.request_id = <request_id>))
and fcp.concurrent_process_id = creq_run.controlling_manager
and fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
order by creq_run.actual_start_date;

SELECT creq_run.request_id,
      creq_run.number_of_arguments,
      creq_run.argument_text,
      to_char(creq_run.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
      to_char(creq_run.actual_completion_date, 'DD-MON-RR HH24:MI:SS'),
      to_char(creq_base.requested_start_date, 'DD-MON-RR HH24:MI:SS'),
      to_char(creq_base.actual_start_date, 'DD-MON-RR HH24:MI:SS'),
      to_char(creq_base.actual_completion_date, 'DD-MON-RR HH24:MI:SS')
FROM  fnd_concurrent_requests creq_run,
       fnd_concurrent_program_serial cprog,
       fnd_concurrent_requests creq_base
WHERE (    (creq_run.program_application_id = cprog.RUNNING_APPLICATION_ID)
AND (creq_run.concurrent_program_id = cprog.RUNNING_CONCURRENT_PROGRAM_ID)
AND (creq_base.program_application_id = cprog.TO_RUN_APPLICATION_ID)
AND (creq_base.concurrent_program_id = cprog.TO_RUN_CONCURRENT_PROGRAM_ID)
AND (nvl(creq_base.actual_start_date,sysdate) >=
creq_run.actual_start_date)  
AND (creq_base.requested_start_date <=
nvl(creq_run.actual_completion_date,sysdate))
AND (creq_base.request_id = <request_id>));

select fo.profile_option_name, level_id, level_value,nvl(to_char(fpv.profile_option_value), 'NULL')
from fnd_profile_option_values fpv, fnd_profile_options fo
where fo.PROFILE_OPTION_NAME Like 'CONC%'
and fpv.profile_option_id(+)=fo.profile_option_id;

No comments: