Oracle中排名前5的耗时的SQL查询

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)。但是,我们如何确定其中哪一个表现不佳?

8 个解决方案

#1

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语句,这些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和执行进行排序。执行很有用,因为一些不良应用程序发送相同的SQL语句的次数太多。此SQL假定您正确使用绑定变量。

Then, you can take the sql_id and child_number of 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的全文。

#2

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;

#3

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

#4

It depends which version of oracle you have, for 9i and below Statspack is what you are after, 10g and above, you want awr , both these tools will give you the top sql's and lots of other stuff.

这取决于你拥有哪个版本的oracle,对于9i及以下Statspack就是你所追求的,10g及以上,你想要的是awr,这两个工具都会给你*的sql和很多其他的东西。

#5

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

#6

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 Plans before executing their statements.

我最近使用此查询来跟踪在执行语句之前拒绝利用解释计划的用户。

I found this query in an old Oracle SQL tuning book (which I unfortunately no longer have), so apologies, but no attribution.

我在一本旧的Oracle SQL调优书中找到了这个查询(遗憾的是我不再拥有),所以道歉,但没有归属。

#7

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:

这个链接看起来是一个好的开始:

http://www.oracleutilities.com/OSUtil/tkprof.html

#8

-1

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 username,sql_text,sofar,totalwork,units

FROM v$sql,v$session_longops

WHERE sql_address = 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!!

欢迎评论!!

#1