oracle常⽤性能分析语句总结oralce查询当前执⾏耗资源的语句
select t.*, s.sid, s.serial#, s.machine, s.program, s.osuser
from(select c.USERNAME,
a.event,
to_char(at)as seconds,
a.sql_id,
dbms_lob.substr(b.sql_fulltext,3500,1) sqltext
from(selectrownum rn, t.*
from(selectdecode(s.session_state,
'WAITING',
s.event,
'Cpu + Wait For Cpu') Event,
s.sql_id,
s.user_id,
count(*) CNT
from v$active_session_history s
where sample_time >sysdate-30/1440
groupby s.user_id,
decode(s.session_state,
'WAITING',
s.event,
'Cpu + Wait For Cpu'),
s.sql_id
orderby CNT desc) t
whererownum<20) a,
v$sqlarea b,
dba_users c
where a.sql_id = b.sql_id
and a.user_id = c.user_id
orderby CNT desc) t,
v$session s
where t.sql_id = s.sql_id(+);
查询正在执⾏的sql语句及执⾏该语句的⽤户SELECT b.sidoracleID,
b.username登录Oracle⽤户名,
b.serial#,
spid操作系统ID,
paddr,
sql_text正在执⾏的SQL,
b.machine计算机名
FROM v$processa,v$session b,v$sqlarea c
WHERE a.addr=b.paddr
AND b.sql_hash_value=c.hash_value
--查看正在执⾏sql的发起者的发放程序
SELECTOSUSER电脑登录⾝份,
PROGRAM发起请求的程序,
USERNAME登录系统的⽤户名,
SCHEMANAME,
B.Cpu_Time花费cpu的时间,
STATUS,
B.SQL_TEXT执⾏的sql
FROMV$SESSIONA
LEFTJOINV$SQLBONA.SQL_ADDRESS=B.ADDRESS
ANDA.SQL_HASH_VALUE=B.HASH_VALUE
ORDERBYb.cpu_timeDESC
--查出oracle当前的被锁对象
SELECTl.session_idsid,
s.serial#,
l.locked_mode锁模式,
l.os_user_name登录机器⽤户名,
s.machine机器名,
o.object_name被锁对象名,
s.logon_time登录数据库时间
FROMv$locked_objectl,all_objectso,v$sessions
WHEREl.object_id=o.object_id
ANDl.session_id=s.sid
ORDERBYsid,s.serial#;
数据库等待时间查询等等
--查询enq和latch的数量
select * from v$session_wait where eventlike 'enq%' or event like 'lat%' or event like 'cursor: pin S%'; --查询锁表SID
select * from v$lock where block=1 ;
--查询死锁的源头和被锁的会话：
select s.sid blocker,substr(s.program,1,40) program, w.username, w.sid blocked from v$session s,v$session w where w.blocking_session = s.sid andw.blocking_session_status='VALID';
--查看进程直接锁的关系，锁的源头
select /*+rule*/ l1.sid, ' IS BLOCKING ', l2.sidfrom v$lock l1, v$lock l2
where l1.block =1 quest > 0 andl1.id1=l2.id1 and l1.id2=l2.id2;
--查询sid对应的sql_id
selectstatus,machine,sql_id,prev_sql_id,client_info from v$session where sid=1105;
--查询完整sql
select sql_text from v$sql where sql_id='bydf32qgqdwdu';
---查询锁表
SELECT l.session_id sid,
s.serial#,
l.locked_mode,
l.os_user_name,
s.machine,
o.object_name,
s.logon_time
FROM v$locked_object l,all_objects o, v$session s
oracle登录命令
WHERE l.object_id = o.object_id
AND l.session_id =s.sid
ORDER BY sid, s.serial#;
--性能相关，enq latch"cursor: pin S wait on X"
select sid,TIME_REMAINING tr,ELAPSED_SECONDSes,a.SQL_ID,a.* from v$session_longops a  where
a.TIME_REMAINING>0order by a.sid;
select count(*),event from v$session_waitgroup by event;where event like 'enq%' or event like 'lat%' or event like'cursor: pin S%';
select * from v$session_wait whereevent='db file scattered read';
--到系统杀状态为killed的锁
select 'kill -9 '||b.spid||' ' fromv$session a,v$process b where a.sid=2193 and a.paddr=b.addr;
select * from v$session where sid=4864;
select * from v$session a ,v$process bwhere paddr=addr and a.sid=4864;
select * from v$lock where block=1 order bysid;
select * from v$locked_object;
select username,sid,serial#,logon_time fromv$locked_object,v$session where v$locked_object.session_id=v$session.sid;
select * from v$session where sid in
(select sid from v$session_waitwhere event like 'enq%' or event like 'lat%' or event like 'cursor: pin S%');
--查看具体sql
select * from v$sql wheresql_id='8q4uycb97mcpa'
select sql_text,sql_fulltext,t2.client_info,t2.program,t2.SQL_ID, t2.sid, t2.event,
t2.sql_hash_value,t2.sql_id,t2.username,t2.wait_class,t2.p1, t2.p2, t2.p3, t2.state
from v$sqlstats t1 inner joinv$session t2 on t1.SQL_ID = t2.sql_id
inner join v$session_wait t3 ont2.sid = t3.sid where t3.wait_class <> 'Idle';
--查看具体session等待问题
selectusername,sid,client_info,sql_id,machine,logon_time,event
from v$session
where status='ACTIVE'
and usernameis not null
and eventlike 'lat%'
or event like 'enq%'
or event like 'cursor: pin S%';
--查询process和session：
select * from v$resource_limit;
--查出长时间操作的语句
select * from v$session_longops wheretime_remaining>0;
select a.* from v$sql a,v$session b wherea.sql_id=b.sql_id and b.sid=262;
select/*+rule*/ username,sid,opname,
round(sofar*100/ totalwork,0) || '%' as progress,
time_remaining,sql_text
from v$session_longops , v$sql

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。