幾個定位、查詢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)
come from：http://space.itpub.net/594850/viewspace-578011
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/90618/viewspace-671938/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 查詢某個session正在執行的sql語句OracleSessionSQL
- Sql語句本身的優化-定位慢查詢SQL優化
- SQL查詢語句 (Oracle)SQLOracle
- SQL server 查詢語句SQLServer
- SQL mother查詢語句SQL
- 記一個實用的sql查詢語句SQL
- 一個經典的查詢及其SQL語句SQL
- SQL Server阻塞查詢語句SQLServer
- SQL查詢語句使用 (轉)SQL
- sql 查詢經典語句SQL
- 查詢效率低下的sql的語句SQL
- mysql查詢效率慢的SQL語句MySql
- 查詢執行慢的SQL語句SQL
- SQL SERVER 條件語句的查詢SQLServer
- 查詢正在執行的SQL語句SQL
- 15個初學者必看的基礎SQL查詢語句SQL
- postgresql dba常用sql查詢語句SQL
- SQL語句查詢表結構SQL
- mysql 查詢建表語句sqlMySql
- SQL查詢語句精華文章(轉)SQL
- oracle、my sql、sql隨機查詢語句OracleSQL隨機
- Laravel 框架查詢執行的 SQL 語句Laravel框架SQL
- 在mysql查詢效率慢的SQL語句MySql
- SQL基礎的查詢語句烈鉍SQL
- 查詢Oracle正在執行的SQL語句OracleSQL
- SQL Server 查詢歷史執行的SQL語句SQLServer
- 幾個SQL查詢小技巧SQL
- SQL Server-簡單查詢語句SQLServer
- SQL單表查詢語句總結SQL
- SQL語句巢狀查詢問題SQL巢狀
- 查詢阻塞與被阻塞SQL語句SQL
- Sql Server系列：查詢分頁語句SQLServer
- 查詢sql語句執行次數SQL
- 一個sql查詢語句報ORA-01722SQL
- 一句簡單的SQL查詢語句的背後...SQL
- SQL 查詢語句的執行順序解析SQL
- 查詢orcale執行的SQL語句記錄SQL
- 找出Mysql查詢速度慢的SQL語句MySql