conc request queries

7/22/2019 Conc request Queries

http://slidepdf.com/reader/full/conc-request-queries 1/5

-- Concurrent Request querries ________________________________________________________________________________ ___________________________________________________________________________ ContentsMonitoring run time of the request db sessionenvironmentperformanceLoad

Env

Query Find Sessions which are blocked: find req.id from sid: find status of given req.id: find sql for sid: find status of sid: find sid from req.id: all running request: SID running: child requests of a req: log file and out file of a request

Manager which is running the concurrent request Concurrent program definition of the given request Child request of the running request Parent request of the running request Incompatibility setting of the request Number of concurrent requests running per day Number of concurrent requests running per hour in a day How many concurrent requests of this program are running or failing how many requests of this program is scheduled How many concurrent request submitted each day - Concurrent program wise - avg run time How many concurrent request submitted each day - Concurrent manager wiseSite

Note ________________________________________________________________________________ ___________________________________________________________________________ Env---set pages 50 lines 170 set feed on head on time on timing onalter session set nls_date_format='dd-mon-yy hh24:mi:ss'

________________________________________________________________________________ ___________________________________________________________________________ Find Sessions which are blocked:------------------------------- select blocking_session

,sid ,serial# ,wait_class ,seconds_in_wait

from v$sessionwhere blocking_session is not NULLorder by blocking_session;

________________________________________________________________________________ ___________________________________________________________________________



find req.id from sid:-------------------- select f.request_id, v.spid,s.sid, s.username,s.serial#, s.osuser, s.status from v$process v, v$session s, applsys.fnd_concurrent_requests f where s.paddr=v.addr and f.oracle_process_id=v.spid and trunc(f.request_date)=trunc(sysdate) and sid=&SID;

________________________________________________________________________________ ___________________________________________________________________________ find sid from req.id:-------------------- set lines 200 SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, gv$process c, gv$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id=d.audsid AND a.request_id = &Request_ID AND a.phase_code = 'R';

________________________________________________________________________________

___________________________________________________________________________ find status of given req.id:--------------------------- SELECT request_id, user_concurrent_program_name , DECODE(phase_code,'C','Completed',phase_code) phase_code , DECODE(status_code,'D', 'Cancelled' ,'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated', 'C', 'Normal', status_code) status_code , to_char(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date , to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss') , argument_text , completion_text

FROM apps.fnd_conc_req_summary_v

WHERE request_id = '&req_id' ORDER BY 6 DESC; ________________________________________________________________________________ ___________________________________________________________________________ find sql for sid:---------------- select sid,sql_text

from gv$session ses, gv$sqlarea sqlwhere ses.sql_hash_value = sql.hash_value(+)

and ses.sql_address = sql.address(+)and ses.sid='&oracle_sid';

________________________________________________________________________________ ___________________________________________________________________________ find status of sid:

------------------ select sid,serial#,USERNAME,STATUS,SQL_ADDRESS,OSUSER,MACHINE

from v$session where sid='&sid'; ________________________________________________________________________________ ___________________________________________________________________________ all running request:------------------- SELECT request_id id, nvl(meaning, 'UNKNOWN') status, user_concurrent_program_name rpname,



to_char(actual_start_date, 'DD-MON-RR HH24:MI:SS') sd, decode(run_alone_flag, 'Y', 'Yes', 'No') ra FROM apps.fnd_concurrent_requests fcr, apps.fnd_lookups fl, apps.fnd_concurrent_programs_vl fcpv WHERE phase_code = 'R' AND LOOKUP_TYPE = 'CP_STATUS_CODE' AND lookup_code = status_code AND fcr.concurrent_program_id = fcpv.concurrent_program_id AND fcr.program_application_id = fcpv.application_id ORDER BY actual_start_date, request_id;

________________________________________________________________________________ ___________________________________________________________________________ SID running:-----------select SID,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME,TIME_REMAINING,ELAPSED_SECONDS,SQL_ADDRESSfrom v$session_longopswhere sid in (4678)order by LAST_UPDATE_TIME desc ;

________________________________________________________________________________ ___________________________________________________________________________ child requests of a req:-----------------------

COLUMN "Program Name" FORMAT A37COLUMN "Delay" FORMAT 9999.99COLUMN "Elapsed" FORMAT 9999.99SELECT /* + ORDERED USE_NL(x fcr fcp fcptl)*/

fcr.request_id "Request ID",fcptl.user_concurrent_program_name"Program Name",fcr.phase_code,fcr.status_code,

-- to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS') "Submitted",-- (fcr.actual_start_date - fcr.request_date)*1440 "Delay",

to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start Time",to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",

(fcr.actual_completion_date - fcr.actual_start_date)*1440 "Elapsed",fcr.oracle_process_id "Trace ID"FROM (SELECT /* + index (fcr1 fnd_concurrent_requests_n3) */

fcr1.request_idFROM apps.fnd_concurrent_requests fcr1

WHERE 1=1START WITH fcr1.request_id = &parent_request_id

CONNECT BY PRIOR fcr1.request_id = fcr1.parent_request_id) x,apps.fnd_concurrent_requests fcr,apps.fnd_concurrent_programs fcp,apps.fnd_concurrent_programs_tl fcptl

WHERE fcr.request_id = x.request_idAND fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_idAND fcp.application_id = fcptl.application_idAND fcp.concurrent_program_id = fcptl.concurrent_program_idAND fcptl.language = 'US'

ORDER BY 1; ________________________________________________________________________________ ___________________________________________________________________________ log file and out file of a request----------------------------------select request_id,status_code,phase_code,logfile_node_name,outfile_node_name,log



file_name,outfile_namefrom apps.fnd_concurrent_requestswhere request_id in ('66826246','66826305')

________________________________________________________________________________ ___________________________________________________________________________ Concurrent request and printers----------------------------------SELECT fl.meaning , fu.user_name , fu.description requestor , fu.end_date , NVL(fu.email_address, 'n/a') email_address , fcr.request_id , fcr.status_code , fcr.phase_code , fcr.number_of_copies , fcr.printer , fcr.request_date , fcr.requested_start_date , fcp.description , fcr.argument_text , frt.responsibility_name FROM apps.fnd_concurrent_requests fcr , apps.fnd_user fu , apps.fnd_lookups fl

, apps.fnd_concurrent_programs_vl fcp , apps.fnd_responsibility_tl frt WHERE fcr.requested_by = fu.user_id AND fl.lookup_type = 'CP_STATUS_CODE' AND fcr.status_code = fl.lookup_code AND fcr.program_application_id = fcp.application_id AND fcr.concurrent_program_id = fcp.concurrent_program_id AND fcr.responsibility_id = frt.responsibility_id AND fcr.printer = 'IL105_5W_HP22' --Printer-name AND fcr.phase_code <> 'C' and fcr.status_code <> 'C'

________________________________________________________________________________ ___________________________________________________________________________

Manager which is running the concurrent request---------------------------------- ________________________________________________________________________________ ___________________________________________________________________________ Concurrent program definition of the given request

________________________________________________________________________________ ___________________________________________________________________________ Child request of the running request

________________________________________________________________________________ ___________________________________________________________________________ Parent request of the running request

________________________________________________________________________________ ___________________________________________________________________________

Incompatibility setting of the request ________________________________________________________________________________ ___________________________________________________________________________ Number of concurrent requests running per day--------------------------------------------- select /*+ parallel(a,4) */

count(1) , trunc(acutal_completion_date) from apps.fnd_concurrent_requests a group by trunc(actual_completion_date)



order by trunc(actual_completion_date) ________________________________________________________________________________ ___________________________________________________________________________ Number of concurrent requests running per hour in a day------------------------------------------------------- select /*+ parallel(a,4) */

count(1), to_char(actual_completion_date,"dd-mon-yyyy hh24")

from apps.fnd_concurrent_requests a where actual_completion_date > sysdate -1 group by to_char(actual_completion_date,"dd-mon-yyyy hh24") order by to_char(actual_completion_date,"dd-mon-yyyy hh24")

________________________________________________________________________________ ___________________________________________________________________________ How many concurrent requests of this program are running or failing------------------------------------------------------------------- select /*+ parallel(a,4) */

count(1), a.concurrent_program_id

, a.phase_code , a.status_code from apps.fnd_concurrent_requests a where a.concurrent_progam_id=(select concurrent_program_id from apps.fnd_concurrent_requests where request_id=&Request_id)

group by a.phase_code,a.status_code ________________________________________________________________________________ ___________________________________________________________________________ how many requests of this program is scheduled---------------------------------------------- select /*+ parallel(a,4) */

count(1), a.phase_code

, a.concurrent_program_id from apps.fnd_concurrent_requests a where a.concurrent_progam_id=(select concurrent_program_id from apps.fnd_concurrent_requests where request_id=&Request_id) and phase_code NOT in ( 'R', 'C')

group by a.phase_code

________________________________________________________________________________ ___________________________________________________________________________ How many concurrent request submitted each day - Concurrent program wise - avg run time---------------------------------------------------------------------------------------

________________________________________________________________________________ ___________________________________________________________________________ How many concurrent request submitted each day - Concurrent manager wise------------------------------------------------------------------------

conc request queries

Documents