oracle 使用 gv$session 判断查询是否挂起
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10352300/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use gv$session to tell if a query is hanging
提问by Paul
I have a query running in Oracle, which may or may not be hung. It's been running for ~10 hours now, but based on the amount of data I'm loading that may not be unreasonable.
我有一个在 Oracle 中运行的查询，它可能会或可能不会被挂起。它现在已经运行了大约 10 个小时，但根据我加载的数据量，这可能不是不合理的。
I was looking at the session in gv$session and was wondering if there's a way to translate that information to see if there's actually any activity going on, or if the query is stuck waiting for a lock or otherwise hung.
我正在查看 gv$session 中的会话，想知道是否有办法翻译该信息以查看是否确实有任何活动正在进行，或者查询是否卡在等待锁定或以其他方式挂起。
I've already read the documentation for this view here. I'm mostly looking for tips from anyone whose had experience debugging these types of issues in Oracle.
我已经在这里阅读了此视图的文档。我主要是从任何有过在 Oracle 中调试这些类型问题经验的人那里寻找技巧。
Thanks!
谢谢！
回答by Justin Cave
In gv$session, the eventcolumn tells you what wait event your session is currently waiting on. If your session is waiting on some sort of lock held by another session, the eventwill tell you that (for example, it will be "enq: TX - row lock contention" if you are enqueued waiting to lock a row held by another session) and blocking_instanceand blocking_sessionwill be populated with the instance and session ID of the holder of the lock. You can also look at seconds_in_wait(if wait_time=0) to determine how many seconds the session has spent in the current wait event. That should at least tell you whether your session is currently "stuck" but it doesn't tell you if your query is ever really going to finish-- if there is a bad plan, it's entirely possible that you've got "good" wait events like waits for disk I/O that indicate the session is doing something but that the query is never really going to finish.
在 中gv$session，该event列会告诉您会话当前正在等待的等待事件。如果您的会话正在等待另一个会话持有的某种锁，它event会告诉您（例如，如果您排队等待锁定另一个会话持有的行，它将是“enq：TX - 行锁争用”） andblocking_instance和blocking_session将填充锁持有者的实例和会话 ID。你也可以看看seconds_in_wait（如果wait_time=0) 以确定会话在当前等待事件中花费了多少秒。这至少应该告诉你你的会话当前是否“卡住”，但它不会告诉你你的查询是否真的要完成——如果有一个糟糕的计划，你完全有可能得到“好”等待事件，如等待磁盘 I/O，表明会话正在做某事，但查询永远不会真正完成。
回答by Paul
Based on some further research and Ollie's comment I came up with these queries that help debug the issue:
根据一些进一步的研究和 Ollie 的评论，我提出了这些有助于调试问题的查询：
select s.sid,
s.username,
s.machine,
s.osuser,
cpu_time,
(elapsed_time/1000000)/60 as minutes,
sql_text
from gv$sqlarea a, gv$session s
where s.sql_id = a.sql_id
and s.machine like '####';
select lo.*,
a.sql_text
from gv$sqlarea a, gv$session_longops lo
where lo.sql_id = a.sql_id
and lo.sid = ####
order by lo.start_time;
回答by Shyam D
This will be Helpful to Check the Current Running Session
这将有助于检查当前正在运行的会话
select a.SID, a.SERIAL#, c.OBJECT_NAME
from v$session a, v$locked_object b, user_objects c
where a.SID=b.SESSION_ID and b.OBJECT_ID=c.OBJECT_ID
