几个定位、查找session的sql语句

--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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/594850/viewspace-578011/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/594850/viewspace-578011/