oracle cpu 100% 问题

laokaizzz

于 2018-08-13 15:22:12 发布

阅读量1.7k

点赞数

分类专栏：解决问题

解决问题专栏收录该内容

233 篇文章

订阅专栏

本文提供两个SQL查询案例，用于在Oracle数据库中优化会话和等待事件的查询。第一个查询通过v$sesstat、v$session等视图获取CPU使用情况，第二个查询结合v$session_wait、v$sqlarea等视图来定位长时间等待的SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考：https://blog.youkuaiyun.com/xyz846/article/details/6862840

https://blog.youkuaiyun.com/gumengkai/article/details/53196447

select ss.sid,se.command,ss.value CPU ,se.username,se.program
from v$sesstat ss, v$session se
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid=ss.sid
and ss.sid>6
order by ss.sid

SELECT P.PID,
S.SID,
S.SERIAL#,
S.USERNAME,
Q.SQL_ID,
Q.SQL_TEXT,
Q.SQL_FULLTEXT,
W.EVENT,
W.WAIT_TIME,
W.STATE,
CASE
WHEN W.STATE = 'WAITING' THEN
W.SECONDS_IN_WAIT
WHEN W.STATE = 'WAITING KNOWN TIME' THEN
W.WAIT_TIME
END AS SEC_IN_WAIT
FROM V$SESSION S, V$SESSION_WAIT W, V$SQLAREA Q, V$PROCESS P
WHERE S.SID = W.SID
AND S.SQL_ID = Q.SQL_ID
AND P.ADDR = S.PADDR
AND W.EVENT NOT LIKE 'SQL*Net%'
AND S.USERNAME IS NOT NULL
AND W.WAIT_TIME >= 0
ORDER BY W.WAIT_TIME DESC;