如何检查 Oracle 数据库中是否存在长时间运行的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/622289/
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
How to check Oracle database for long running queries
提问by WW.
My application, which uses an Oracle database, is going slow or appears to have stopped completely.
我的应用程序使用 Oracle 数据库，运行缓慢或似乎已完全停止。
How can find out which queries are most expensive, so I can investigate further?
如何找出哪些查询最昂贵，以便我可以进一步调查？
回答by WW.
This one shows SQL that is currently "ACTIVE":-
这显示了当前“活动”的 SQL：-
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
/
回答by Carlos A. Ibarra
Try this, it will give you queries currently running for more than 60 seconds. Note that it prints multiple lines per running query if the SQL has multiple lines. Look at the sid,serial# to see what belongs together.
试试这个，它会给你当前运行超过 60 秒的查询。请注意，如果 SQL 有多行，它会在每个运行的查询中打印多行。查看 sid,serial# 以了解属于一起的内容。
select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s
join v$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece
回答by Gary Myers
v$session_longops
v$session_longops
If you look for sofar != totalwork you'll see ones that haven't completed, but the entries aren't removed when the operation completes so you can see a lot of history there too.
如果您查找 sofar != totalwork，您会看到尚未完成的条目，但操作完成时不会删除条目，因此您也可以在那里看到很多历史记录。
回答by mani22487
Step 1:Execute the query
column username format 'a10'
column osuser format 'a10'
column module format 'a16'
column program_name format 'a20'
column program format 'a20'
column machine format 'a20'
column action format 'a20'
column sid format '9999'
column serial# format '99999'
column spid format '99999'
set linesize 200
set pagesize 30
select
a.sid,a.serial#,a.username,a.osuser,c.start_time,
b.spid,a.status,a.machine,
a.action,a.module,a.program
from
v$session a, v$process b, v$transaction c,
v$sqlarea s
Where
a.paddr = b.addr
and a.saddr = c.ses_addr
and a.sql_address = s.address (+)
and to_date(c.start_time,'mm/dd/yy hh24:mi:ss') <= sysdate - (15/1440) -- running for 15 minutes
order by c.start_time
/
Step 2: desc v$session
Step 3:select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR from v$session where sid='xxxx' //(enter the sid value)
Step 4: select sql_text from v$sqltext where address='XXXXXXXX';
Step 5: select piece, sql_text from v$sqltext where address='XXXXXX' order by piece;
回答by Ramki
You can generate an AWR (automatic workload repository) report from the database.
您可以从数据库生成 AWR（自动工作负载存储库）报告。
Run from the SQL*Plus command line:
从 SQL*Plus 命令行运行：
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Read the document related to how to generate & understand an AWR report. It will give a complete view of database performance and resource issues. Once we are familiar with the AWR report it will be helpful to find Top SQL which is consuming resources.
阅读有关如何生成和理解 AWR 报告的文档。它将提供数据库性能和资源问题的完整视图。一旦我们熟悉了 AWR 报告，将有助于找到消耗资源的 Top SQL。
Also, in the 12C EM Express UI we can generate an AWR.
此外，在 12C EM Express UI 中，我们可以生成 AWR。
回答by santosh tiwary
You can check the long-running queries details like % completed and remaining time using the below query:
您可以使用以下查询检查长时间运行的查询详细信息，例如完成百分比和剩余时间：
SELECT SID, SERIAL#, OPNAME, CONTEXT, SOFAR,
TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
For the complete list of troubleshooting steps, you can check here:Troubleshooting long running sessions
有关故障排除步骤的完整列表，您可以在此处查看：对长时间运行的会话进行故障排除
回答by akasha
select sq.PARSING_SCHEMA_NAME, sq.LAST_LOAD_TIME, sq.ELAPSED_TIME, sq.ROWS_PROCESSED, ltrim(sq.sql_text), sq.SQL_FULLTEXT
from v$sql sq, v$session se
order by sq.ELAPSED_TIME desc, sq.LAST_LOAD_TIME desc;
