oracle查找session所有sql,几个定位、查找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)