Showing posts with label concurrent requests. Show all posts
Showing posts with label concurrent requests. Show all posts

Tuesday, September 22, 2009

Is There a Method to Block Concurrent Requests From Running?

Yes. From the manager work shift, by making the processes=0 for that work shift at specific time and date.


Something to remember when creating manager work shifts that:

A. Date-specific work shifts override work shifts that do not specify a specific date.
B. Overlapping Work Shifts - Priority Levels:

1. The work shift with the more specific time period takes effect for the overlapping time period.

2. Overlapping work shifts with the same priority:

The work shift with the largest target processes takes effect.

Is Thers a Document That Defines All System Administrator Concurrent Requests?

No document exists that list all the concurrent programs definitions and descriptions.There are many programs and are handled by many different teams, there is documentation for eachindividual program, but nothing that summarizes all together.A search through My Oracle Support application with the concurrent program executable name will return references that contain details of the particular program..The following is a query that summarizes all the concurrent programs and a brief description ofthem plus their respective executable file. This is the same information that you can be obtained throughapplications if you navigate programs one by one.


SQL> select cp.concurrent_program_name, cpt.user_concurrent_program_name, cpt.description,ex.executable_name, ext.user_executable_name, ext.descriptionfrom fnd_concurrent_programs cp, fnd_concurrent_programs_tl cpt,fnd_executables ex, fnd_executables_tl extwhere cp.concurrent_program_id = cpt.concurrent_program_idand ex.executable_id = ext.executable_idand cp.executable_id = ex.executable_id;

The System Administrator’s Guide does summarize some of the common programs utilized andtheir description, for example:

- Function Security Reports

- Active Responsibilities Report

- Active Users Report

- Signon Audit Concurrent Requests Report

- Signon Audit Responsibilities Report

- Report Group Responsibilities Reportand so on.

You may use Concurrent Program Details Report, which will list every concurrent report, to find out more details on a specfic report.The "Concurrent Program Details Report" provides details on how the program is registered and the"description" abstract of the registered program.For usage, you need to reference applications manuals.

Friday, April 17, 2009

Useful Queries Related to Concurrent Requests in 11i Applications

Scheduled concurrent requests:

The below query will return all the concurrent requests which are scheduled using any of the above methods:
==========================================================
SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' cr.description, cp.user_concurrent_program_name ) NAME, argument_text, cr.resubmit_interval, NVL2 (cr.resubmit_interval, 'PERIODICALLY', NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') ) schedule_type, DECODE (NVL2 (cr.resubmit_interval, 'PERIODICALLY', NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE') ), 'PERIODICALLY', 'EVERY ' cr.resubmit_interval ' ' cr.resubmit_interval_unit_code ' FROM ' cr.resubmit_interval_type_code ' OF PREV RUN', 'ONCE', 'AT :' TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'), 'EVERY: ' fcr.class_info ) schedule, fu.user_name, requested_start_date FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu, apps.fnd_conc_release_classes fcr WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = 'P' AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = 'US' AND fcr.release_class_id(+) = cr.release_class_id AND fcr.application_id(+) = cr.release_class_app_id;
==========================================================

Note: The "SCHEDULE" column in the above query returns a string of zeros and ones for the requests which are scheduled on specific days of the month or week. Positions 1 through 31: Specific day of the month. Position 32: Last day of the month Positions 33 through 39: Sunday through Saturday

Checking the duplicated schedules of the same program with the same arguments:

The below query can be used to check the duplicated schedule of the same program with the same arguments. This can be used to alert the users to cancel these duplicated schedules. Note: This query will return even though the request was submitted using a different responsibility.
==========================================================
SELECT request_id, NAME, argument_text, user_name FROM (SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' cr.description, cp.user_concurrent_program_name ) NAME, argument_text, fu.user_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = 'P' AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = 'US' AND fu.user_name NOT LIKE 'PPG%') t1 WHERE EXISTS ( SELECT 1 FROM (SELECT cr.request_id, DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:' cr.description, cp.user_concurrent_program_name ) NAME, argument_text, fu.user_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_requests cr, apps.fnd_user fu WHERE cp.application_id = cr.program_application_id AND cp.concurrent_program_id = cr.concurrent_program_id AND cr.requested_by = fu.user_id AND cr.phase_code = 'P' AND cr.requested_start_date > SYSDATE AND cp.LANGUAGE = 'US' AND fu.user_name NOT LIKE 'PPG%') t2 WHERE t1.NAME = t2.NAME AND t1.argument_text = t2.argument_text AND t1.user_name = t2.user_name GROUP BY NAME, argument_text, user_name HAVING COUNT (*) > 1) ORDER BY user_name, NAME
==========================================================

Average pending time per request:

This is a very useful query to check the performance of the concurrent managers. Average pending time for a request is calculated like below: ("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc) We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.

==========================================================
SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY, concurrent_queue_name, (SUM ( ( actual_start_date - (CASE WHEN requested_start_date > request_date THEN requested_start_date ELSE request_date END ) ) * 24 * 60 * 60 ) ) / COUNT (*) "Wait_Time_per_Req_in_Secs" FROM apps.fnd_concurrent_requests cr, apps.fnd_concurrent_processes fcp, apps.fnd_concurrent_queues fcq WHERE cr.phase_code = 'C' AND cr.actual_start_date IS NOT NULL AND cr.requested_start_date IS NOT NULL AND cr.controlling_manager = fcp.concurrent_process_id AND fcp.queue_application_id = fcq.application_id AND fcp.concurrent_queue_id = fcq.concurrent_queue_id GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name ORDER BY 2
==========================================================

Note: Depending on the purging schedules some requests might miss if the corresponding data in fnd_concurrent_processes is purged.

Checking which manager is going to execute a program:

The below query identifies the manager which will be executing a given program. This query is based on the specialization rules set for the managers.
==========================================================
SELECT user_concurrent_program_name, user_concurrent_queue_name FROM apps.fnd_concurrent_programs_tl cp, apps.fnd_concurrent_queue_content cqc, apps.fnd_concurrent_queues_tl cq WHERE cqc.type_application_id(+) = cp.application_id AND cqc.type_id(+) = cp.concurrent_program_id AND cqc.type_code(+) = 'P' AND cqc.include_flag(+) = 'I' AND cp.LANGUAGE = 'US' AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id AND NVL (cqc.queue_application_id, 0) = cq.application_id AND cq.LANGUAGE = 'US'
==========================================================

To see all the pending / Running requests per each manager wise:

==========================================================
SELECT request_id, phase_code, status_code, user_name, user_concurrent_queue_name FROM apps.fnd_concurrent_worker_requests cwr, apps.fnd_concurrent_queues_tl cq, apps.fnd_user fu WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R') AND cwr.hold_flag != 'Y' AND cwr.requested_start_date <= SYSDATE AND cwr.concurrent_queue_id = cq.concurrent_queue_id AND cwr.queue_application_id = cq.application_id AND cq.LANGUAGE = 'US' AND cwr.requested_by = fu.user_id ORDER BY 5
==========================================================
Note: The same information can be seen in Administer Concurrent Manager form for each manager.


Checking the incompatibilities between the programs:

The below query can be used to find all incompatibilities in an application instance.
==========================================================
SELECT a2.application_name, a1.user_concurrent_program_name, DECODE (running_type, 'P', 'Program', 'S', 'Request set', 'UNKNOWN' ) "Type", b2.application_name "Incompatible App", b1.user_concurrent_program_name "Incompatible_Prog", DECODE (to_run_type, 'P', 'Program', 'S', 'Request set', 'UNKNOWN' ) incompatible_type FROM apps.fnd_concurrent_program_serial cps, apps.fnd_concurrent_programs_tl a1, apps.fnd_concurrent_programs_tl b1, apps.fnd_application_tl a2, apps.fnd_application_tl b2 WHERE a1.application_id = cps.running_application_id AND a1.concurrent_program_id = cps.running_concurrent_program_id AND a2.application_id = cps.running_application_id AND b1.application_id = cps.to_run_application_id AND b1.concurrent_program_id = cps.to_run_concurrent_program_id AND b2.application_id = cps.to_run_application_id AND a1.language = 'US' AND a2.language = 'US' AND b1.language = 'US' AND b2.language = 'US'
==========================================================

The table apps.fnd_concurrent_program_serial has the information about incompatibilities.

Monday, March 30, 2009

How To Prevent Concurrent Requests Executing on New Cloned Environment

How to prevent Concurrent Requests executing on a environment created as part of a clone.
To prevent concurrent requests executing on a newly cloned environment 2 steps will be required.


1) Prevent Concurrent Managers from starting as part of the Clone

2) Change data concerning concurrent requests so they do not execute when the concurrent managers start.

To prevent Applications Services (including Concurrent Managers) from starting automatically during Rapid Clone an enhancement request has been raised to see if this can be considered an option during the clone.
Until this feature is available there is the following workaround.


1. Edit the $COMMON_TOP/clone/bin/adcfgclone.pl


2. Go to the end of the file


3. Change the following lines:-
print "\n Starting application Services for $s_dbSid:\n";print "Running:\n";print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/\n");system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");
TO:
print "\nNOT Starting application Services for $s_dbSid:\n";#print "Running:\n";#print(" $s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/\n");#system("$s_com/admin/scripts/$s_contextname/adstrtal.$ext $s_apps_user/$PWD");


4. Run "perl adcfgclone.pl appsTier" as normal.
The services will not start Automatically when the clone completes allowing data to be changed safely.
There is a need to change 2 sets of concurrent requests to prevent execution


a) Terminate 'Running' Requestsb) Set Pending jobs to 'On Hold'

a) Set Terminating or Running to Completed/TerminatedUPDATE fnd_concurrent_requestsSET phase_code = 'C', status_code = 'X'WHERE status_code ='T'OR phase_code = 'R'/

b) Place Pending/(Normal/Standby) to On HoldUPDATE fnd_concurrent_requestsSET hold_flag = 'Y'WHERE phase_code = 'P'AND status_code in ('Q','I')/

Once these changes have been committed then the Application services including concurrent manager can be restarted and the concurrent jobs will not be executed.