Script to Monitor Concurrent requests average run time

Do you need a daily  report which would give an average run time of all concurrent requests in your environment?

So you would know which concurrent request is taking longer time and on which day:
The below script gives an average run time for all concurrent requests for the current day, previous day, day before yesterday and for the whole week:



set pagesize 500
set echo off
set feedback off
set linesize 200
col USER_CONCURRENT_PROGRAM_NAME for a75
col wkly_Time Heading 'Weekly Run | Time | Avg |(In Minutes) '
col dbydy_Tim Heading 'Day Before|Yesterday | Run Time | Avg |(In Minutes) '
col ydy_Tim Heading 'Yesterday | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading 'Today | Run Time | Avg |(In Minutes) '
col tdy_Tim Heading &_DATE
select  wkly.user_concurrent_program_name, wkly.wkly_time , to_char(nvl(dbydy.dbydy_time,'     Not Run'))  Dbydy_tim,to_char(nvl(ydy.ydy_time,'     Not Run'))  ydy_tim,to_char(nvl(tdy.tdy_time,'     Not Run'))  tdy_tim
FROM
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') wkly_time
FROM apps.fnd_conc_req_summary_v
WHERE nvl(actual_start_date,sysdate) >= (sysdate-7)
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name) wkly,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') dbydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-2))
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name ) dbydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') ydy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate-1))
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name ) ydy,
(SELECT user_concurrent_program_name,
to_char(avg(round((actual_completion_date- actual_start_date)*24*60)),'99999999.99') tdy_time
FROM apps.fnd_conc_req_summary_v
WHERE trunc(nvl(actual_start_date,sysdate)) = trunc((sysdate))
AND phase_code='C' and status_code  in ('C','G')
group by user_concurrent_program_name ) tdy
WHERE wkly.user_concurrent_program_name =dbydy.user_concurrent_program_name (+)
AND   dbydy.user_concurrent_program_name = ydy.user_concurrent_program_name (+)
AND   ydy.user_concurrent_program_name  = tdy.user_concurrent_program_name (+)
order by wkly_time desc

Log and Sql trace in MWA

The mwa.LogLevel can have the following values:

-          Fatal, Error, Warning, Debug, and Trace.

-          Trace is the least restrictive: it will log all messages. 

-          Fatal is the most restrictive: it will display only messages from fatal errors.

-          Trace is the setting normally suggested when debugging a problem on mobile.

How to set up mwa trace:

To enable logging you set "mwa.LogLevel = TRACE" in the mwa.cfg file then restart the mobile server.

Steps:

1.       Cd $MWA_TOP/secure

2.       Change the below mentioned entries in mwa.cfg:

Change mwa.LogLevel=TRACE

3.       Restart the MWA server.

4.       Perform the transaction.

5.       [port].INV.log
[port].system.log  are the logs which will capture the transaction logs in the below mentioned location:

Reference: Metalink: Doc ID 338291.1

How to setup sql trace file for MWA:

How It Works
If the user sets the parameter mwa.LogLevel = performance in configuration file (mwa.cfg), the mobile application will write a SQL*Trace file for all users connecting to the mobile server. All user connections will be written to the same SQL*Trace file usually found in the server side UDUMP directory in the same location as the other trace files.

Steps:

1.       Cd $MWA_TOP/secure

2.       Change the below mentioned entries in mwa.cfg:

Change mwa.LogLevel=performance

3.       Restart the MWA server.

4.       Perform the related navigation steps to cause the issue.

5.       You can search in the trace directories for files created. Or you can try to locate the particular file using SQL like the following to help. Query to identify the trace file name with the corresponding number from the username.

select prs.spid
from v$session ses , v$process prs
where ses.program = 'JDBC Thin Client'
and ses.client_info is not null
and ses.osuser = '&OS_USER_NAME'
and ses.paddr = prs.addr
order by ses.logon_time desc ;

6.       Use the column "prs.spid" which maps to the filename in udump location. For example, if the column has the value 18027, then the file name could be  "ora_18027.trc".

7.        Create a TKPROF file and upload both the raw trace (.trc) and TKPROF.

8.        After generating the trace file, ensure that you disable SQL*Trace by stopping the mobile server, resetting the parameter in mwa.cfg, and restarting the mobile server.

Reference Metalink Doc ID 277655.1