查询查询运行超过5秒

Question

我的朋友问我在他的oracle数据库上发现长时间运行的查询（超过5秒）。他想在周期性的时间间隔后进行一些轮询，并想发出警报，以便知道哪些查询需要很长时间才能执行并向他发送查询和相应的会话。

我写这篇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 

但是，他说，当他手动运行查询和计算时间，则在执行它花费的时间，结果他从获得的一小部分由此特定查询生成的表。

我不知道我的查询是否是错误的，我已经做了一些搜索，但它似乎仍然没有问题。

数据库是Oracle 10g中

建议???

Answer 1

ELAPSED_TIME是所有倍SQL语句已运行的累计时间。所以对于经常执行的查询来说它会很高。

认为这是一个活泼的查询（提示风格的注释是获得在V $ SQLAREA的SQL_ID的目的）：

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

多久需要运行？这长：

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> 

这是一个相对较大的块或'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> 

而且运行后它的另一个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> 

现在，你想要的是找到持续5秒钟以上的活动会话。因此，您需要会话级别的时间安排，特别是V $ SESSION上的LAST_CALL_ET，这是会话进行某些操作（如果其状态为ACTIVE）的秒数或自上次操作以来的总时间（如果其状态是不活跃的）。

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

因此，考虑这个查询。它很慢：

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

no rows selected 

Elapsed: 00:00:07.56 
SQL> 

这是足够长的时间使用上V $ SESSION查询监控。这一个搜索已经运行超过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> 

和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> 

---

“应该给我这是当前执行的查询 的列表或具有 日前执行完毕，我可以 找到的总时间查询走上 完成”

V $ SQLAREA视图上的LAST_ACTIVE_TIME记录语句执行的最近时间。但是，没有哪个视图公开每个单独执行该语句的指标。如果你想要这种细节，你需要开始追踪。这是一个单独的问题。

Answer 2

使用追踪：

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