Quick performance checks

Quick performance checks:

======================

1. Login Check:

1. Please login to client URL and check the speed.

2. Find Blocking sessions:

select blocking_session, sid, serial#, wait_class,seconds_in_wait from v$session where blocking_session is not NULL order by blocking_session;

3. Find Database Locks:

select

(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,

a.sid || ', ' ||

(select serial# from v$session where sid=a.sid) sid_serial,

' is blocking ',

(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,

b.sid || ', ' ||

(select serial# from v$session where sid=b.sid) sid_serial

from v$lock a, v$lock b

where a.block = 1

and b.request > 0

and a.id1 = b.id1

and a.id2 = b.id2;

4. Checking Gather stats:

select JOB_NAME,SCHEDULE_NAME,SCHEDULE_TYPE,ENABLED from DBA_SCHEDULER_JOBS where PROGRAM_NAME like 'GATHER%';

JOB_NAME         SCHEDULE_NAME            SCHEDULE_TYPE  ENABLED

---------------- ------------------------ -------------- -------

TRUE (If enabled coulum is true Gather stats is running.)

5. Checking CPU and Memory:

TOP

free -m

6. Checking Long running concurrent Requests:

SELECT a.request_id

,a.oracle_process_id "SPID"

,frt.responsibility_name

,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name

,a.description

,a.ARGUMENT_TEXT

,b.node_name

,b.db_instance

,a.logfile_name

,a.logfile_node_name

,a.outfile_name

,q.concurrent_queue_name

,a.phase_code,a.status_code, a.completion_text

, actual_start_date

, actual_completion_date

, fu.user_name

,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins

,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time

FROM APPLSYS.fnd_Concurrent_requests a2,

APPLSYS.fnd_concurrent_programs c2

WHERE c2.concurrent_program_id = c.concurrent_program_id

AND a2.concurrent_program_id = c2.concurrent_program_id

AND a2.program_application_id = c2.application_id

AND a2.phase_code || '' = 'C') avg_mins

,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours

FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b

,applsys.fnd_concurrent_queues q

,APPLSYS.fnd_concurrent_programs c

,APPLSYS.fnd_concurrent_programs_tl ctl

,apps.fnd_user fu

,apps.FND_RESPONSIBILITY_TL frt

WHERE a.controlling_manager = b.concurrent_process_id

AND a.concurrent_program_id = c.concurrent_program_id

AND a.program_application_id = c.application_id

AND a.phase_code = 'R'

AND a.status_code = 'R'

AND b.queue_application_id = q.application_id

AND b.concurrent_queue_id = q.concurrent_queue_id

AND ctl.concurrent_program_id = c.concurrent_program_id

AND a.requested_by = fu.user_id

AND a.responsibility_id = frt.responsibility_id

ORDER BY a.actual_start_date DESC;

Count Current Forms Application Users Connected to the Application

--> Script to check the currently connected Application users.

set lines 160
set pages 1000
col USER_NAME format a20
col RESPONSIBILITY_NAME format a50
col USER_FORM_NAME format a30
select to_date(time,'DD-MON-YYYY HH24:MI:SS') "Time", user_name, responsibility_name, user_form_name
FROM apps.fnd_form_sessions_v
ORDER BY 1;

Responsibilities available to a User

select frtl.responsibility_name
from apps.fnd_responsibility_tl frtl, apps.fnd_user_resp_groups furg,
apps.fnd_user fu where furg.responsibility_id = frtl.responsibility_id
and furg.responsibility_application_id = frtl.application_id
and furg.start_date <=sysdate
and nvl(furg.end_date,sysdate) >=sysdate
and frtl.language = 'US'
and furg.user_id = fu.user_id
and upper(fu.user_name) like upper('&User');