EBS Performance Issue Related Queries

Find Top SQL:

select sql_id, module, count(*) samples
from   gv$active_session_history
where  sample_time > sysdate - 30/1440
group  by sql_id, module
order  by 3 desc
fetch first 10 rows only;

Find Session details from SQL_ID:

select * from gv$session where sql_id='bsd2bz342w5mu';

Find program/concurrent details from above query:

select r.request_id, u.user_name, r.actual_start_date,
       round((sysdate - r.actual_start_date)*24*60) mins_running,
       r.argument_text
from   fnd_concurrent_requests r, fnd_concurrent_programs p, fnd_user u
where  p.concurrent_program_id = r.concurrent_program_id
and    p.application_id = r.program_application_id
and    u.user_id = r.requested_by
and    p.concurrent_program_name = 'POXYZ'
and    r.phase_code = 'R'
order  by r.actual_start_date;

Blockers:

select inst_id, sid, serial#, blocking_instance, blocking_session,
       event, seconds_in_wait, sql_id, module
from   gv$session
where  blocking_session is not null;

What active sessions are waiting on:

select inst_id, wait_class, event, count(*)
from   gv$session
where  status = 'ACTIVE' and type = 'USER' and wait_class <> 'Idle'
group  by inst_id, wait_class, event
order  by 4 desc;