Oracle 中最耗时的 5 个 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/316812/
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
Top 5 time-consuming SQL queries in Oracle
提问by Kamal Joshi
How can I find poor performing SQL queries in Oracle?
如何在 Oracle 中找到性能不佳的 SQL 查询？
Oracle maintains statistics on shared SQL area and contains one row per SQL string(v$sqlarea). But how can we identify which one of them are badly performing?
Oracle 维护共享 SQL 区域的统计信息，每个 SQL 字符串包含一行（v$sqlarea）。但是我们如何才能确定其中哪一个表现不佳呢？
回答by WW.
I found this SQL statement to be a useful place to start (sorry I can't attribute this to the original author; I found it somewhere on the internet):
我发现这个 SQL 语句是一个有用的起点（对不起，我不能把它归因于原作者；我在互联网上的某个地方找到了它）：
SELECT * FROM
(SELECT
sql_fulltext,
sql_id,
elapsed_time,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10
/
This finds the top SQL statements that are currently stored in the SQL cache ordered by elapsed time. Statements will disappear from the cache over time, so it might be no good trying to diagnose last night's batch job when you roll into work at midday.
这将查找当前存储在 SQL 缓存中的顶级 SQL 语句，这些语句按经过时间排序。语句会随着时间的推移从缓存中消失，因此当您在中午开始工作时尝试诊断昨晚的批处理作业可能没有好处。
You can also try ordering by disk_reads and executions. Executions is useful because some poor applications send the same SQL statement way too many times. This SQL assumes you use bind variables correctly.
您也可以尝试按 disk_reads 和 executions 排序。Executions 很有用，因为一些糟糕的应用程序多次发送相同的 SQL 语句。此 SQL 假定您正确使用绑定变量。
Then, you can take the sql_idand child_numberof a statement and feed them into this baby:-
然后，你可以采取sql_id和child_number声明，并将它们送入这个婴儿： -
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child));
This shows the actual plan from the SQL cache and the full text of the SQL.
这显示了 SQL 缓存中的实际计划和 SQL 的全文。
回答by Leigh Riffel
You could find disk intensive full table scans with something like this:
您可以通过以下方式找到磁盘密集型全表扫描：
SELECT Disk_Reads DiskReads, Executions, SQL_ID, SQL_Text SQLText,
SQL_FullText SQLFullText
FROM
(
SELECT Disk_Reads, Executions, SQL_ID, LTRIM(SQL_Text) SQL_Text,
SQL_FullText, Operation, Options,
Row_Number() OVER
(Partition By sql_text ORDER BY Disk_Reads * Executions DESC)
KeepHighSQL
FROM
(
SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads,
Max(Executions) OVER (Partition By sql_text) Executions,
t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options
FROM v$sql t, v$sql_plan p
WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS'
AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')
AND t.Executions > 1
)
ORDER BY DISK_READS * EXECUTIONS DESC
)
WHERE KeepHighSQL = 1
AND rownum <=5;
回答by Guille
You could take the average buffer gets per execution during a period of activity of the instance:
您可以在实例的活动期间获取每次执行的平均缓冲区获取量：
SELECT username,
buffer_gets,
disk_reads,
executions,
buffer_get_per_exec,
parse_calls,
sorts,
rows_processed,
hit_ratio,
module,
sql_text
-- elapsed_time, cpu_time, user_io_wait_time, ,
FROM (SELECT sql_text,
b.username,
a.disk_reads,
a.buffer_gets,
trunc(a.buffer_gets / a.executions) buffer_get_per_exec,
a.parse_calls,
a.sorts,
a.executions,
a.rows_processed,
100 - ROUND (100 * a.disk_reads / a.buffer_gets, 2) hit_ratio,
module
-- cpu_time, elapsed_time, user_io_wait_time
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id
AND b.username NOT IN ('SYS', 'SYSTEM', 'RMAN','SYSMAN')
AND a.buffer_gets > 10000
ORDER BY buffer_get_per_exec DESC)
WHERE ROWNUM <= 20
回答by Matthew Watson
回答by Steven Wolfe
The following query returns SQL statements that perform large numbers of disk reads (also includes the offending user and the number of times the query has been run):
以下查询返回执行大量磁盘读取的 SQL 语句（还包括违规用户和查询已运行的次数）：
SELECT t2.username, t1.disk_reads, t1.executions,
t1.disk_reads / DECODE(t1.executions, 0, 1, t1.executions) as exec_ratio,
t1.command_type, t1.sql_text
FROM v$sqlarea t1, dba_users t2
WHERE t1.parsing_user_id = t2.user_id
AND t1.disk_reads > 100000
ORDER BY t1.disk_reads DESC
Run the query as SYS and adjust the number of disk reads depending on what you deem to be excessive (100,000 works for me).
以 SYS 身份运行查询并根据您认为过多的内容调整磁盘读取次数（100,000 对我有用）。
I have used this query very recently to track down users who refuse to take advantage of Explain Plansbefore executing their statements.
我最近使用这个查询来追踪Explain Plans在执行语句之前拒绝利用的用户。
I found this query in an old Oracle SQL tuning book (which I unfortunately no longer have), so apologies, but no attribution.
我在一本旧的 Oracle SQL 调优书（不幸的是我不再有）中发现了这个查询，很抱歉，但没有归属。
回答by Ras Rass
the complete information one that I got from askTom-Oracle. I hope it helps you
我从 askTom-Oracle 获得的完整信息。我希望它能帮助你
select *
from v$sql
where buffer_gets > 1000000
or disk_reads > 100000
or executions > 50000
回答by cagcowboy
There are a number of possible ways to do this, but have a google for tkprof
有很多可能的方法可以做到这一点，但有一个 tkprof 的谷歌
There's no GUI... it's entirely command line and possibly a touch intimidating for Oracle beginners; but it's very powerful.
没有 GUI……它完全是命令行，对于 Oracle 初学者来说可能有点吓人；但它非常强大。
This link looks like a good start:
这个链接看起来是一个好的开始：
回答by Kamal Joshi
While searching I got the following query which does the job with one assumption(query execution time >6 seconds)
在搜索时，我得到了以下查询，它通过一个假设完成了工作（查询执行时间 >6 秒）
SELECT username, sql_text, sofar, totalwork, units
SELECT 用户名、sql_text、sofar、totalwork、单位
FROM v$sql,v$session_longops
FROM v$sql,v$session_longops
WHERE sql_address = address AND sql_hash_value = hash_value
WHERE sql_address = 地址 AND sql_hash_value = hash_value
ORDER BY address, hash_value, child_number;
ORDER BY 地址、hash_value、child_number；
I think above query will list the details for current user.
我认为上面的查询将列出当前用户的详细信息。
Comments are welcome!!
欢迎评论！！
