SQL 如何使用oracle视图识别Oracle中的高负载SQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8295021/
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 identify high-load SQL in Oracle, using oracle views?
提问by Marshall
I'm looking for a query, that will return a list of high-load sql statements. I don't want to use any Oracle tools like ADDM or AWR. I need a query statement, that will return high-load sql statements.
我正在寻找一个查询，它将返回一个高负载 sql 语句的列表。我不想使用任何 Oracle 工具，如 ADDM 或 AWR。我需要一个查询语句，它将返回高负载的 sql 语句。
采纳答案by Stephen ODonnell
You could query the AWR tables directly, that may be the most simple way.
您可以直接查询 AWR 表，这可能是最简单的方法。
Or, a simple technique I used to use on Oracle 8i with no statistics enabled, was to select the SQL with the highest buffer gets to execution ratio from v$sql. You can play with this query to only search for high execution count queries, or those doing high physical IO etc.
或者，我曾经在未启用统计信息的 Oracle 8i 上使用的一种简单技术是从 v$sql 中选择具有最高缓冲区获取率的 SQL。您可以使用此查询来仅搜索高执行计数的查询，或执行高物理 IO 的查询等。
The AWR and ASH tables will give better information, but this could be a simple first step:
AWR 和 ASH 表将提供更好的信息，但这可能是一个简单的第一步：
select a.sql_id, a.ratio, a.executions
from
(
select sql_id, buffer_gets / executions ratio, executions
from v$sql
where executions > 10
order by 2 desc
) a
where rownum <= 10
回答by Kevin Burton
To quickly find if you have long running processes taking up your resources look at v$sesson_long_ops :
要快速确定您是否有长时间运行的进程占用您的资源，请查看 v$sesson_long_ops ：
SELECT * FROM v$session_longops
see: http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2092.htm
见：http: //docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2092.htm
I would advise you to take a look at this too: http://docs.oracle.com/cd/B28359_01/server.111/b28274/instance_tune.htm
我建议你也看看这个：http: //docs.oracle.com/cd/B28359_01/server.111/b28274/instance_tune.htm
You can then take the sid to find the sql that is running:
然后就可以拿sid来查找正在运行的sql：
SELECT sql_text FROM v$session s
LEFT JOIN v$sqlarea sa ON s.sql_hash_value=sa.hash_value AND s.sql_address=sa.address
WHERE sid=&sid
If you are running unix You could also take a look at the topcommand (top10or topason different unix flavours)
you could then take the process ids of the queries consuming the most cpu then use the following to get the offending sql.
如果您正在运行 unix 您还可以查看top命令（top10或topas在不同的 unix 版本上），然后您可以获取消耗最多 cpu 的查询的进程 ID，然后使用以下命令获取有问题的 sql。
SELECT
s.username,
sa.sql_text
FROM v$process p
INNER JOIN v$session s ON p.addr=s.paddr
LEFT JOIN v$sqlarea sa ON s.sql_hash_value=sa.hash_value AND s.sql_address=sa.address
WHERE s.username IS NOT NULL AND p.spid=&SPID
