查找运行超过5秒的查询。

I was asked by my friend to find long running queries (more than 5 second) on his oracle database. He wanted to do some sort of polling after a periodic interval and wanted to send himself an alert so that he knows which query are taking so long to execute and send query and corresponding session to him.

我的朋友让我在他的oracle数据库上找到长时间运行的查询(超过5秒)。他希望在周期间隔后进行某种轮询，并希望发送自己的警报，以便他知道哪些查询需要花费这么长的时间来执行，并向他发送查询和相应的会话。

I wrote this Oracle query :

我写了这个Oracle查询:

    select    sess.sid,
    sess.username,
    sess.paddr,
    sess.machine,
    optimizer_mode,
    sess.schemaname,
    hash_value,
    address,
    sess.sql_address,
    cpu_time,
    elapsed_time,
    sql_text
from    v$sql sql, v$session sess
where 
        sess.sql_hash_value = sql.hash_value
    and     sess.sql_address = sql.address
    and     sess.username is not null
    and     elapsed_time > 1000000  * 5
order by    
    cpu_time desc

But he says that when he runs a query manually and calculates the time, the time it spends in executing it is a fraction of the result he is getting from the table generated by this particular query.

但是他说，当他手动运行一个查询并计算时间时，它在执行过程中花费的时间只是他从这个特定查询生成的表中得到的结果的一小部分。

I wonder if my query is wrong , I have done some search but it still seems the query is fine.

我不知道我的查询是否错误，我已经做了一些搜索，但看起来仍然很好。

Database is Oracle 10g

数据库Oracle 10 g

Suggestions???

建议吗? ? ?

2 个解决方案

#1

ELAPSED_TIME is the accumulated time for all the times that SQL statement has been run. So it will be high for frequently executed queries.

ELAPSED_TIME是SQL语句一直运行的所有时间的累积时间。因此，对于频繁执行的查询，它将是很高的。

Consider this a snappy query (the HINT-style comment is for the purposes of getting the SQL_ID in V$SQLAREA):

考虑一下这个snappy查询(hintstyle注释是为了在V$SQLAREA中获取SQL_ID):

select /*+ fast_running_query */ id
from big_table
where id = 1
/

How long does it take to run? This long:

跑步需要多长时间?这长:

SQL> select elapsed_time
  2         , executions
  3         , elapsed_time / executions as avg_ela_time
  4  from v$sqlarea
  5  where sql_id = '73c1zqkpp23f0'
  6  /

ELAPSED_TIME EXECUTIONS AVG_ELA_TIME
------------ ---------- ------------
      235774          1       235774

SQL>

That's a relatively large chunk o' microsecs, because of the parse time. We can see that running it a couple more times doesn't add much to the elapsed time, and the average is much lower:

这是一个相当大的块o' microsecs，因为解析时间。我们可以看到，运行它几次并不会增加太多的时间，而且平均值要低得多:

SQL> r
  1  select elapsed_time,
  2         executions,
  3         elapsed_time / executions as avg_ela_time
  4  from v$sqlarea
  5* where sql_id = '5v4nm7jtq3p2n'

ELAPSED_TIME EXECUTIONS AVG_ELA_TIME
------------ ---------- ------------
      237570          3        79190

SQL>

And after running it another 100000 times ...

在运行了10万次之后…

SQL> r
  1  select elapsed_time,
  2         executions,
  3         elapsed_time / executions as avg_ela_time
  4  from v$sqlarea
  5* where sql_id = '5v4nm7jtq3p2n'

ELAPSED_TIME EXECUTIONS AVG_ELA_TIME
------------ ---------- ------------
     1673900     100003   14.3809724

SQL>

Now, what you wantt is to find active sessions which have been doing something continuously for more than five seconds. So you need session-level timings, and in particular the LAST_CALL_ET on V$SESSION, which is the number of seconds the session has been doing something (if its status is ACTIVE) or the total elapsed time since its last action (if its status is INACTIVE).

现在，你想要找到的是连续做了超过5秒的活动。因此，您需要会话级别的计时，特别是在v$会话上的LAST_CALL_ET，这是会话一直在做某件事(如果它的状态是活动的)或自上一次操作(如果它的状态是不活动的)的总运行时间的秒数。

select sid
       , serial#
       , sql_address
       , last_call_et
from v$session
where status = 'ACTIVE'
and last_call_et > sysdate - (sysdate-(5/86400))
/

So, consider this query. It's slow:

所以,考虑该查询。慢:

SQL> select /*+ slow_running_query */ *
  2  from big_table
  3  where col2 like '%whatever%'
  4  /

no rows selected

Elapsed: 00:00:07.56
SQL>

That's long enough to monitor using a query on V$SESSION. This one searches for statements which have been running for more than 3 seconds...

这段时间足够监视使用V$会话的查询。这一次搜索的语句已经运行了超过3秒……

SQL> select sid
  2         , serial#
  3         , sql_id
  4         , last_call_et
  5  from v$session
  6  where status = 'ACTIVE'
  7  and last_call_et > sysdate - (sysdate - (3/86400))
  8  and username is not null
  9  /

       SID    SERIAL# SQL_ID        LAST_CALL_ET
---------- ---------- ------------- ------------
       137          7 096rr4hppg636            4
       170          5 ap3xdndsa05tg            7

SQL>

and lo!

,瞧!

SQL> select sql_text from v$sqlarea where sql_id = 'ap3xdndsa05tg'
  2  /

SQL_TEXT
--------------------------------------------------------------------------------
select /*+ slow_running_query */ * from big_table where col2 like '%whatever%'

SQL>

"it should give me the list of queries which are currently executing or have recently finished executing and I can find the total time the query took to complete"

“它应该给出当前正在执行的查询列表，或者最近已经完成的查询，我可以找到查询完成的总时间。”

The LAST_ACTIVE_TIME on the V$SQLAREA view records the most recent time the statement was executed. However, there is no view which exposes the metrics for each individual execution of the statement. If you want that sort of detail you will need to start tracing. And that is a separate question.

在v$ SQLAREA视图上的LAST_ACTIVE_TIME记录了该语句最近执行的时间。但是，没有任何视图可以公开声明的每个单独执行的度量。如果你想要这样的细节，你需要开始追踪。这是另一个问题。

#2

-1

Use the tracing:

使用跟踪:

# alter session set timed_statistics = true; 
# alter session set sql_trace = true; 
.....
# show parameter user_dump_dest 
$ tkprof <trc-файл> <txt-файл>

#1