--long time running session
SELECT s1.sid,s1.SERIAL# ,s1.username,totalwork,last_update_time,elapsed_seconds,message
FROM v$session s1, v$session_longops s2
WHERE
s1.serial#=s2.serial# and s1.sid=s2.sid
and s2.username<>'SYSTEM' and s2.username<>'SYS'
--and elapsed_seconds >20
ORDER BY 6 desc

-- 最耗费CPU资源的TOP 25 SQLs
select * from (
select rank() over(order by buffer_gets desc) as rank_bufgets
,to_char(100 * ratio_to_report(buffer_gets) over(), '999.99') pct_bufgets
,sql_text
from v$sqlarea
) where rownum<26 ;

-----inquery the full SQL statement
SELECT sql_text
FROM v$sqltext a
WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b WHERE b.SID = '&sid')
ORDER BY piece ASC

-----inquery full SQL according to HASH_VALUE
select * from v$sqltext where hash_value='&hash_value'
order by piece

---SESSION _ ID
select sql.sql_text, sess.logon_time, pro.pid "Oracle ProID", sess.sid "SessID",sess.serial#, pro.spid "OS ProID", pro.username "Oracle UsrName", pro.program "Program",
sess.module "Module",
sess.username, sess.osuser, sess.machine, sess.terminal, sess.program, sess.type
from v$process pro,v$session sess , v$sql sql
where pro.addr=sess.paddr
and sess.sql_address = sql.address(+)
and sess.sid = &SESSION_ID

--- ORACLE_ID
select sql.sql_text, sess.logon_time, pro.pid "Oracle ProID", sess.sid "SessID",pro.spid "OS ProID", pro.username "Oracle UsrName", pro.program "Program",
sess.module "Module",
sess.username, sess.osuser, sess.machine, sess.terminal, sess.program, sess.type
from v$process pro,v$session sess , v$sql sql
where pro.ADDR=sess.paddr
and sess.sql_address = sql.address (+) -- sql.address = sess.sql_address(+) ?
and pro.pid= &ORACLE_PROCESS_ID

--- OS_PROCESS_ID
select sql.sql_text, sess.logon_time, pro.pid "Oracle ProID", sess.sid "SessID",pro.spid "OS ProID", pro.username "Oracle UsrName", pro.program "Program",
sess.module "Module",
sess.username, sess.osuser, sess.machine, sess.terminal, sess.program, sess.type
from v$process pro,v$session sess , v$sql sql
where pro.ADDR=sess.paddr
and sess.sql_address = sql.address (+) -- sql.address = sess.sql_address(+) ?
and pro.spid = &OS_ID

--- SQL
select sql.SQL_TEXT,sql.BUFFER_GETS , sql.CPU_TIME, sql.DISK_READS,sql.ELAPSED_TIME ,sql.RUNTIME_MEM,sql.EXECUTIONS
,sql.FETCHES,sql.PARSE_CALLS,sql.FIRST_LOAD_TIME,sql.MODULE,sql.OPTIMIZER_COST, sql.OPTIMIZER_MODE,sql.SORTS
, sess.SID , sess.PROGRAM ,SESS.TYPE
from v$sql sql , v$session sess
where
sess.SQL_ADDRESS(+)=sql.ADDRESS
and upper(sql_text) like '%&sql%'

----- Request_ID

SELECT a.process_status_code,a.oracle_process_id,a.os_process_id,process_start_date FROM FND_CONCURRENT_PROCESSES A , FND_CONCURRENT_REQUESTS B
WHERE
B.CONTROLLING_MANAGER=A.CONCURRENT_PROCESS_ID
AND B.REQUEST_ID='&req_id'


select 'CONC',p.pid,request_id
, p.username OS_NAME
, p.spid
, s.sid
, s.username
, s.serial#
, s.sql_address
,u.user_name
,CONCURRENT_PROGRAM_NAME
from v$process p
, v$session s
,fnd_concurrent_requests cr
,fnd_user u,fnd_concurrent_programs cp
where
s.audsid=cr.oracle_session_id --- 如果是oracle report, using the following sql
and cr.REQUESTED_BY = u.user_id
and s.paddr = p.addr
and cp.CONCURRENT_PROGRAM_ID =cr.CONCURRENT_PROGRAM_ID
and cr.request_id = &REQUESTID

select request_id,STATUS_CODE,PHASE_CODE ,oracle_id,OS_PROCESS_ID, oracle_session_id,concurrent_program_id, printer
from fnd_concurrent_requests
where request_id = &REQUEST_ID

----如何查找后台一个正在运行的concurrent reporter SESSION
select sid, serial#,osuser, process, machine, program,module from v$session
where status='ACTIVE'
AND SCHEMANAME<>'SYS'
AND UPPER(PROGRAM) LIKE 'AR60RUN%'

----------------------------------------
--- get the form. information from os process
select 'FORM', p.pid
, p.username OS_NAME
, p.spid
, s.sid
, s.username
, s.serial#
, s.sql_address
,u.user_name
,f.form_name
,sysdate-lrf.start_time
from v$process p
,v$session s
,fnd_login_resp_forms lrf
,fnd_logins l
,fnd_user u
,fnd_form. f
where s.audsid=lrf.audsid
and lrf.login_id = l.login_id
and l.user_id = u.user_id
and lrf.form_id = f.form_id
and s.paddr = p.addr
and p.spid = &OSPROCESS

--- get the concurrent information from os process
select 'CONC',p.pid,request_id
, p.username OS_NAME
, p.spid
, s.sid
, s.username
, s.serial#
, s.sql_address
,u.user_name
,CONCURRENT_PROGRAM_NAME
from v$process p
, v$session s
,fnd_concurrent_requests cr
,fnd_user u,fnd_concurrent_programs cp
where s.audsid=cr.oracle_session_id
and cr.REQUESTED_BY = u.user_id
and s.paddr = p.addr
and cp.CONCURRENT_PROGRAM_ID =cr.CONCURRENT_PROGRAM_ID
and p.spid = &OSPROCESS


SELECT r.request_id "Request ID",
s.sid "Session ID" ,
s.serial#
FROM applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_queues_tl qt,
applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_processes p,
v$session s
WHERE r.controlling_manager=p.concurrent_process_id
AND q.application_id=p.queue_application_id
AND q.concurrent_queue_id=p.concurrent_queue_id
AND qt.application_id=q.application_id
AND qt.concurrent_queue_id=q.concurrent_queue_id
AND r.phase_code='R'
AND qt.language in ('ZHS')
AND p.session_id=s.audsid

---根据request_id查找是哪个trace file
/* Formatted on 2007/02/16 15:32 (Formatter Plus v4.8.5) */
SELECT fcr.request_id, fcr.concurrent_program_id, fcp.concurrent_program_name,
TO_CHAR (fcr.actual_completion_date, 'DD-MON-YY HH24:MI:SS'),
actual_completion_date, fcr.oracle_process_id,
par_udd.VALUE || '/*' || fcr.oracle_process_id || '*.trc' TRACE
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
v$parameter par_udd
WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
AND LOWER (par_udd.NAME) = 'user_dump_dest'
AND fcr.request_id = 36751549

----根据HASH_VALUE生成执行计划
select '| Operation | PHV/Object Name | Rows | Bytes| Cost |' as "Optimizer Plan:" from dual
union all
select '--------------------------------------------------------------------------------' from dual
union all
select *
from (select rpad('|'||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
rpad(decode(id, 0, '----- '||to_char(hash_value)||' -----'
, substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',
decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v$sql_plan
where hash_value = &hash_value)

come from：http://space.itpub.net/594850/viewspace-578011