使用gv $ session判断查询是否挂起 [英] Use gv$session to tell if a query is hanging

问题描述

我有一个在Oracle中运行的查询，该查询可能已挂起，也可能未挂起.它已经运行了大约10个小时，但是根据我正在加载的数据量来看，这可能并不合理.

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.

我当时正在gv $ session中查看会话，并且想知道是否有一种方法可以转换该信息，以查看是否实际上正在进行任何活动，或者查询是否停留在等待锁或挂起的状态.

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.

我已经在此处阅读了该视图的文档.我主要是在寻求从Oracle调试此类问题的经验中获取经验的人.

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.

谢谢！

推荐答案

在gv$session中，event列告诉您会话当前正在等待什么等待事件.如果您的会话正在等待另一会话持有的某种锁定，则event会告诉您(例如，如果您排队等待锁定由另一会话持有的行，则它将为"enq:TX-行锁争用".另一个会话)和blocking_instance和blocking_session将使用锁持有者的实例和会话ID进行填充.您还可以查看seconds_in_wait(如果是wait_time=0)以确定会话在当前等待事件中花费了多少秒.这至少应该告诉您您的会话当前是否处于阻塞"状态，但并不能告诉您查询是否真的要完成–如果计划不正确，则很可能您的查询良好"等待事件(例如，等待磁盘I/O)表明会话正在执行某些操作，但查询从未真正完成.

In gv$session, the event column 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 event will 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_instance and blocking_session will 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判断查询是否挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！