Oracle中排名前5位的耗时SQL查询 [英] Top 5 time-consuming SQL queries in Oracle

问题描述

如何在Oracle中发现性能不佳的SQL查询?

How can I find poor performing SQL queries in Oracle?

Oracle维护有关共享SQL区域的统计信息，并且每个SQL字符串(v $ sqlarea)包含一行. 但是我们如何确定其中哪一个表现不佳?

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?

推荐答案

我发现此SQL语句是一个有用的起点(对不起，我无法将其归因于原始作者；我在Internet上的某个地方找到了它) :

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):

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
/

这将找到当前存储在SQL高速缓存中的头条SQL语句(按经过时间排序).语句将随着时间的推移从缓存中消失，因此当您在中午开始工作时，尝试诊断昨晚的批处理作业可能不是一个好方法.

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.

您还可以尝试按disk_reads和执行顺序进行排序.执行很有用，因为某些性能不佳的应用程序多次发送同一条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.

然后，您可以采用语句的sql_id和child_number并将其喂入这个婴儿:-

Then, you can take the sql_id and child_number of a statement and feed them into this baby:-

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child));

这显示了SQL缓存中的实际计划和SQL的全文.

This shows the actual plan from the SQL cache and the full text of the SQL.

这篇关于Oracle中排名前5位的耗时SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！