如何检查Oracle数据库中长时间运行的查询 [英] How to check Oracle database for long running queries

问题描述

我的使用Oracle数据库的应用程序运行缓慢，或者似乎已完全停止.

My application, which uses an Oracle database, is going slow or appears to have stopped completely.

如何找出最昂贵的查询，以便进一步调查?

How can find out which queries are most expensive, so I can investigate further?

推荐答案

这显示了当前处于活动"状态的SQL:-

This one shows SQL that is currently "ACTIVE":-

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
/

这显示锁.有时情况会变慢，但这是因为等待锁定而被阻止了:

This shows locks. Sometimes things are going slow, but it's because it is blocked waiting for a lock:

select
  object_name, 
  object_type, 
  session_id, 
  type,         -- Type or system/user lock
  lmode,        -- lock mode in which session holds lock
  request, 
  block, 
  ctime         -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name
/

这是查找长时间操作(例如全表扫描)的好方法.如果是由于大量的短时间操作，将不会显示任何内容.

This is a good one for finding long operations (e.g. full table scans). If it is because of lots of short operations, nothing will show up.

COLUMN percent FORMAT 999.99 

SELECT sid, to_char(start_time,'hh24:mi:ss') stime, 
message,( sofar/totalwork)* 100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1
/

这篇关于如何检查Oracle数据库中长时间运行的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！